SQL 2008: returning data rows as JSON?

Question

I think this question is like clay pidgeon shooting.. "pull... bang!" .. shot down.. but nevertheless, it's worth asking I believe.

Lots of JS frameworks etc use JSON these days, and for good reason I know. The classic question is "where to transform the data to JSON".

I understand that at some point in the pipeline, you have to convert the data to JSON, be it in the data access layer (I am looking at JSON.NET) or I believe in .NET 4.x there are methods to output/serialize as JSON.

So the question is: Is it really a bad idea to contemplate a SQL function to output as JSON?

Qualifier: I understand trying to output 1000's of rows like that isn't a good idea - in fact not really a good idea for web apps either way unless you really have to. For my requirement, I need possibly 100 rows at a time...

Answer 1

Better to load it using your standard data access technique and then convert to JSON. You can then use it in standard objects in .NET as well as your client side javascript.

Answer 2

If using .net mvc you serialize your results in your controllers and output a JsonResult, there's a method Controller.Json() that does this for you. If using webforms an http handler and the JavascriptSerializer class would be the way to go.

Answer 3

This is why [WebMethod] (WebMethodAttribute) exists.

Answer 4

Best to load the data to to the piece of program and then return it as JSON.

.NET 4 has a support for returning json, and i did it as a part of one ASP.NET MVC site and it was fairly simple and straightforward.

I recommend to move the transformation out of the sql server

Answer 5

I agree with the other respondents that this is better done in your application code. However... this is theoretically possible using SQL Server's ability to include CLR assemblies in the database using create assembly syntax. The choice is really yours. You could create an assembly to do the translation in .net, define that assembly to SQL Server and then use contained method(s) to serialize to JSON as return values from your stored procedures...

Answer 6

The answer really is: it depends.

If your application is a small one that doesn't receive much use, then by all means do it in the database. The thing to bear in mind though is, what happens when your application is being used by 10x as many users in 12 months time?

If it makes it quick, simple and easy to implement JSON encoding in your stored procedures, rather than in your web code and allows you to get your app out and in use, then that's clearly the way to go. That said, it really doesn't take that much work to do it "properly" with solutions that have been suggested in other answers.

The long and short of it is, take the solution that best fits your current needs, whilst thinking about the impact it'll have if you need to change it in the future.

Answer 7

Hey thanks for all the responses.. it still amazes me how many people out there have the time to help.

All very good points, and certainly confirmed my feeling of letting the app/layer do the conversion work - as the glue between the actual data and frontend. I guess I haven't kept up too much with MVC or SQL-2008, and so was unsure if there were some nuggets worth tracking down.

As it worked out (following some links posted here, and further fishing) I have opted to do the following for the time being (stuck back using .NET 3.5 and no MVC right now..):

1. Getting the SQL data as a datatable/datareader
2. Using a simple datatable > collection (dictionary) conversion for a serializable list

3. Because right now I am using an ASHX page to act as the broker to the javascript (i.e. via a JQuery AJAX call), within my ASHX page I have:

   context.Response.ContentType = "application/json"; System.Web.Script.Serialization.JavaScriptSerializer json = new System.Web.Script.Serialization.JavaScriptSerializer();

4. I can then issue: json.serialize(<>)

Might seem a bit backward, but it works fine.. and the main caveat is that it is not ever returning huge amounts of data at a time.

Once again, thanks for all the repsonses!