CODEWITHSUNDEEP
c#jsonasp.net-web-apisql-server-2016
Hazem
Hazem

Reputation: 352

How to get JSON data from sql server 2016 and send it as it is (using IHttpActionResult) to client by web API

After Sql server 2016 we can select data direct as JSON by this statement:

SELECT Top (10) * from  Products FOR JSON AUTO

So we no longer need to assign them to objects and convert them to JSON into code. I think we can reduce the complexity of the process and get better performance. I use web API 2 and I want to receive and direct send it to cleint . Is this any new function or method works with SqlCommand to do this? Cloud you help me please?

Upvotes: 3

Views: 12998

Answers (1)

Markus
Markus

Reputation: 22436

This sample shows how to read the JSON from SQL Server using a SqlCommand: 

var queryWithForJson = "SELECT ... FOR JSON";
var conn = new SqlConnection("<connection string>");
var cmd = new SqlCommand(queryWithForJson, conn);
conn.Open();
var jsonResult = new StringBuilder();
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
    jsonResult.Append("[]");
}
else
{
    while (reader.Read())
    {
        jsonResult.Append(reader.GetValue(0).ToString());
    }
}

In your ApiController, you can return the string using the ResponseMessage-method:

public IHttpActionResult Get()
{
    var jsonResult = new StringBuilder();
    /// get JSON
    var response = new HttpResponseMessage(System.Net.HttpStatusCode.OK);
    response.Content = new StringContent(jsonResult.ToString());
    return ResponseMessage(response);
}

However, though technically feasible, IMHO there are some disadvantages that you should take into account when going this route:

  1. You loose the ability to negotiate the content type that you return from your Web API. If you later on have to serve a client that requests XML, you cannot do this easily.
  2. Another disadvantage, maybe minor disadvantage, is that you reduce the ability to scale the JSON conversion. Usually you have one database server whereas you can have several web frontends. Obviously you need the database server to get the data, but you can put the load of the conversion in a place that you can scale better.

I assume that it is more efficient to let SQL Server deliver the data in binary format to the frontends that perform the conversion. I doubt that it will be much faster to put this load on the database server - of course this depends on the infrastructure.

Upvotes: 4

Related Questions