Querying Azure SQL Database using Azure Functions 2.0 to return JSON data


The guide below shows how you can easily query Azure SQL Database using Azure Functions.

I have to admit, I have to do multiple google search and combine it for a working solution.

Challenges:

  1. How to get SQL connectionString from Azure Function settings. https://docs.microsoft.com/en-us/azure/azure-functions/functions-scenario-database-table-cleanup
  2. How to convert the sql results to JSON.  https://stackoverflow.com/questions/5083709/convert-from-sqldatareader-to-json

Steps:

    • 1. Create a new Function with HTTP trigger
    • 2. Add a new file called serialize.csx with following contents below. This will convert the SQL rows to a JSON like data.

using System.Text;
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections;
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;
}
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;
}

 

3. In the run.csx, paste the following code. This will query the Azure SQL database and returns the data.

#r “Newtonsoft.Json”

#load “serialize.csx”
using System.Net;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Primitives;
using Newtonsoft.Json;
using System.Text;
using System.Data;
using System.Linq;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;
public static async Task<HttpResponseMessage> Run(HttpRequest req, ILogger log)
{
log.LogInformation(“C# HTTP trigger function processed a request.”);
string name = req.Query[“name”];
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());
returnnew 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());
returnnew HttpResponseMessage(HttpStatusCode.BadRequest)
{
Content = new StringContent(JsonConvert.SerializeObject($”The following SqlException happened: {ex.Message}”), Encoding.UTF8, “application/json”)
};
}
returnnew HttpResponseMessage(HttpStatusCode.OK)
{
Content = new StringContent(json, Encoding.UTF8, “application/json”)
};
}
This shows how you can easily query the Azure SQL Database and return the data JSON in few minutes.
Perhaps next steps is to deploy this Azure Function into API management.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s