Simple C# Function to query Azure SQL DB and return JSON reply

run.csx

#r "Newtonsoft.Json"

using System.Text;
using System.Net;
using System.Linq;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Collections;
using Newtonsoft.Json;
using Microsoft.Extensions.Primitives;
using Microsoft.AspNetCore.Mvc;

// Helpers
public static IEnumerable<Dictionary<string, object>> Serialize(SqlDataReader reader)
{
    var results = new List<Dictionary<string, object>>();
    var cols = new List<string>();
    for (var i = 0; i < reader.FieldCount; i++)
    {
        var colName = reader.GetName(i);
        var camelCaseName = Char.ToLowerInvariant(colName[0]) + colName.Substring(1);
        cols.Add(camelCaseName);
    }

    while (reader.Read())
        results.Add(SerializeRow(cols, reader));

    return results;
}

// Build dictionary
private static Dictionary<string, object> SerializeRow(IEnumerable<string> cols, SqlDataReader reader)
{
    var result = new Dictionary<string, object>();
    foreach (var col in cols)
        result.Add(col, reader[col]);
    return result;
}


// Function Handler
public static async Task<HttpResponseMessage> Run(HttpRequest req, ILogger log)
{
    log.LogInformation("C# HTTP trigger function processed a request.");

    string json ="";

    try {
        var str = Environment.GetEnvironmentVariable("<ConnectionStringName in appSettings>");
        using(SqlConnection conn = new SqlConnection(str)) {
            using(SqlCommand cmd = new SqlCommand()) {
                SqlDataReader dataReader;
                cmd.CommandText = "<SQL QUERY HERE>";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;
                conn.Open();
                dataReader = cmd.ExecuteReader();
                var r = Serialize(dataReader);
                json = JsonConvert.SerializeObject(r, Formatting.Indented);
            }
        }
    }
    catch(SqlException sqlex) {
        log.LogInformation(sqlex.Message);
        log.LogInformation(sqlex.ToString());
        return new HttpResponseMessage(HttpStatusCode.BadRequest) {
            Content = new StringContent(JsonConvert.SerializeObject($"The following SqlException happened: {sqlex.Message}"), Encoding.UTF8, "application/json")
        };
    }
    catch(Exception ex) {
        log.LogInformation(ex.Message);
        log.LogInformation(ex.ToString());
        return new HttpResponseMessage(HttpStatusCode.BadRequest) {
            Content = new StringContent(JsonConvert.SerializeObject($"The following SqlException happened: {ex.Message}"), Encoding.UTF8, "application/json")
        };
    }
    
    return new HttpResponseMessage(HttpStatusCode.OK) {
        Content = new StringContent(json, Encoding.UTF8, "application/json")
    };
}