3

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?

Hazem
  • 342
  • 1
  • 5
  • 19

1 Answers1

4

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.

Markus
  • 20,838
  • 4
  • 31
  • 55
  • When I try to use Execute reader some data is getting truncated. Any idea why this is happening? – code_blue Aug 18 '17 at 15:40
  • @code_blue in which way is the data truncated? Are some lines missing? Are texts in the columns truncated? – Markus Aug 19 '17 at 17:49
  • The JSON ends abruptly and is not complete. Also I cannot see the entire JSON in SSMS as it seems the JSON size is quite large. – code_blue Aug 20 '17 at 03:10
  • @code_blue could you try this: as follow: https://stackoverflow.com/questions/1151987/can-i-set-an-unlimited-length-for-maxjsonlength-in-web-config – Hazem Aug 21 '17 at 19:16
  • +1 for using stringbuilder, i was using string and getting response in 1 minute, string builder is limited to 0.16 sec, same as SSMS response time – Stelios Feb 27 '21 at 11:23