0 Comments

Microsoft SQL Server has the ability to format the query output to xml, which is great. I used version “Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)” to output XML to have a nested data structure, but there is unfortunately no first citizen support for JSON which is big on the web these days. Perhaps it is left out on purpose to make sure that people are unable to direct output their data as JSON and are forced to build a layer in between so that it can also authenticate and add security to a request. Web API is great for this. But back to the subject: I wanted to output my nested data as JSON really quickly with the minimum amount of code.

In a nutshell, I got the data as xml and converted that to JSON and send it to the output of a generic handler (*.ashx)

Here is my source code for my easy SQL2JSON solution:

private XDocument GetProducts(string q)
{
    SqlCommand comProd = new SqlCommand(@"SELECT ProductCode, ProductEan13, CategoryName FROM PRODUCTS 
        inner join CATEGORIES on products.CategoryID = CATEGORIES.CategoryID
        WHERE ((FactoryCode LIKE @name) OR (ProductCode LIKE @name)) AND (ProductDeleted = 0) for xml raw ('Product'), ROOT ('Products');");
    comProd.Parameters.AddWithValue("name", q + "%");
    string xml = SsDb.GetScalar(comProd).ToString();
    return XDocument.Parse(xml);
}

This gave me an `XDocument` object which I still have to convert to JSON

Since I was using an generic handler I retrieved the search parameter from the query string and used it as input parameter for the `GetProducts` method.

public void ProcessRequest(HttpContext context)
{
    context.Response.ContentType = "application/json";
    context.Response.Cache.SetCacheability(HttpCacheability.Public);
    context.Response.Cache.SetExpires(DateTime.Now.AddDays(1));
    context.Response.Cache.SetMaxAge(new TimeSpan(24, 0, 0));

    string q = context.Request.QueryString.Get("q");
    context.Response.Write(JsonConvert.SerializeXNode(GetProducts(q)));
}

So the most important line is the last one:

context.Response.Write(JsonConvert.SerializeXNode(GetProducts(q)));

Do not forget to use the one-nuget-package-that-rules-them-all (Install-Package Newtonsoft.Json )

using Newtonsoft.Json;

 

Now you can access your JSON data source with the C# generic handler from your JavaScript

yourHandler.ashx?q=yourproductcode

I hope this has been of any help. good luck!

Pin on pinterest Plus on Googleplus Post on LinkedIn