C# Web API to consume Azure SQL Server Data

Question

I would really appreciate it if you can point me to the right place or right idea. I have not done C# in ages and I am not sure what I am doing anymore.

Basically I have an Azure based MSSQL database that I need to read data from and return that data in JSON format for my iPhone app. Azure APIs do not allow you to read directly from the DB but you can build a .Net web API and use that - so that is where I went to.

I have gotten the first part to work. In that I can make a http request and Azure responds fine. I have also built the DB and tables and I have tested all that and it works. I have also tested the query and it runs well formatted JSON.

However, I need to pass the data in the data reader as an http response and I cannot figure that part out. Attached are the two vanilla files..one that makes the dummy http request and the other is the db connector file.

So in short, in the Function1.cs file I need to go from

:req.CreateResponse(HttpStatusCode.OK, "Hello" + name); to :req.CreateResponse(HttpStatusCode.OK, );

First File: Function1.cs

namespace getOutageAlerts

{ public static class Function1 { [FunctionName("Function1")] public static async Task Run([HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)]HttpRequestMessage req, TraceWriter log) { log.Info("C# HTTP trigger function processed a request.");

        // parse query parameter
        string name = req.GetQueryNameValuePairs()
            .FirstOrDefault(q => string.Compare(q.Key, "name", true) == 0)
            .Value;

        if (name == null)
        {
            // Get request body
            dynamic data = await req.Content.ReadAsAsync<object>();
            name = data?.name;
        }

        return name == null
            ? req.CreateResponse(HttpStatusCode.BadRequest, "Please pass a name on the query string or in the request body")
            **:req.CreateResponse(HttpStatusCode.OK, "Hello" + name);**

    }
}

}

Second File: DBConnector.cs

using System;
using System.Data.SqlClient;


public class DBConnector
{
    public DBConnector()
    {
        SqlConnection getalertsConnection = new SqlConnection("Server=tcp:xxxxxxxxx.database.windows.net,1433;Initial Catalog=mckMobileAppsDB;Persist Security Info=False;User ID=xxxxxxx;Password=xxxxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;");
        SqlCommand cmd = new SqlCommand();
        SqlDataReader reader;

        cmd.CommandText = "SELECT Date_Created As Date, Incident_Number AS Incident, Flash_Summary AS Summary, Service, Business_Impact AS Impact, Incident_Status AS Status from OutagesMobileAppNotifications FOR JSON PATH, Root('Alerts')";
        cmd.CommandType = CommandType.Text;
        cmd.Connection = getalertsConnection;

        getalertsConnection.Open();

        reader = cmd.ExecuteReader();
        // Data is accessible through the DataReader object here.


        getalertsConnection.Close();
    }
}

Sailing Judo · Accepted Answer · 2018-09-14 19:40:00Z

0

You're question is phrased a little awkwardly, plus the bad formatting doesn't help. However, it looks as though you want to return JSON from an Azure Serverless Function. Based on this assumption I'll try to answer.

To return JSON you will want to add the Newtonsoft.Json package. This is simple, at the top of your function add the following:

#r "Newtonsoft.Json"
using System.Net;
using System.Text;
using Newtonsoft.Json;
using System.Linq;

(Edit: Added using statement for System.Linq so the Select is available for the data reader.)

In the DBConnector you wrote I would convert the reader to an array of objects and then serialize that result to JSON. (I have truncated your list of fields to jsut 2 for brevity... you'll obviously need to add the whole list.)

var incidents = dataReader.Select(m => new { m.Date, m.Incident }).ToList();
var jsonResult = JsonConvert.SerializeObject(incidents, Formatting.Indented);

Then return the JSON.

return new HttpResponseMessage(HttpStatusCode.OK) {
    Content = new StringContent(jsonResult, Encoding.UTF8, "application/json")
};

(I am trying to do this from memory... and I haven't done Azure Functions in about a year... so the exact code may differ.)

Additionally... since you haven't worked with C# in a while and maybe never with Azure Functions, it might be more simple to keep everything in the same function. By this I mean you don't have to have the second class/file... you can just put all the SQL work in your main method. I would normally consider this a bad practice, but in the short term it might ease your efforts.

edited Sep 14, 2018 at 19:40

answered Sep 14, 2018 at 17:18

Sailing Judo

11.3k20 gold badges71 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ibrahim Akar Over a year ago

Thanks a lot - it seems like I have one issue left.. the datareader does not have a select method.. var Incidents = dataReader.Select(m => new { m.Date, m.Incident, m.Summary, m.Service, m.Impact, m.Status }).ToList(); I tried GetValues and that didn't work either..

Sailing Judo Over a year ago

You will need to add "using System.Linq;" at the top. (I'll edit my answer to show this.)

Sailing Judo Over a year ago

For clarity... if you add the .Select() to the DBConnector class you will need to add the using statement for Linq there.

ibrahim Akar Over a year ago

I moved everything to the main class as you suggested. And I am using System.Ling

Collectives™ on Stack Overflow

C# Web API to consume Azure SQL Server Data

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related