At the moment, I’m working on a simple web reporting project. Given that this is a small-scale project, I’ve decided to try something new. Creating a tiny DAL responsible for retrieving and deserializing SQL Server generated JSON.
SQL Server & JSON the basics
I would like to keep this post short and straightforward. Therefore, I won’t cover the DAL in great detail at this moment. I’m planning to cover this in the near future instead.
Within this post I will cover;
- What’s required to output JSON data within SQL Server or SQL Azure.
- Retrieve the JSON data from within a C# project.
- Convert your JSON result to custom objects
Note: This covers SQL Server 2016 and the current version of SQL Azure.
1 – Return JSON data within SQL server
Returning JSON data is simple. Add the following post-fix to your SQL statement; FOR JSON PATH or FOR JSON AUTO. What’s the difference? By using PATH, you maintain full control over the format of the JSON output. Using AUTO mode, SQL server formats the output according to the structure of the SELECT statement.
You can use AUTO to simplify the steps. In case you do need formatting control, you can alter the SQL statement later.
Returning a single result.
By default, SQL server will return a JSON array. To return a single item, you will need to alter the FOR JSON clause. By adding WITHOUT_ARRAY_WRAPPER, SQL Server won’t generate the surrounding square brackets.
2- Retrieving the data within your .Net application.
Retrieving the output in JSON format is simple. No matter if you use stored procedures or generate SQL statements.
First, open a SqlConnection. Continue by creating a new SqlCommand and providing your data source details. After this, use the ExecuteReader method or asynchronous variant of the SqlCommand to return a SqlDataReader. Iterate over results and collect the output using a StringBuilder. The example below contains all the necessary code.
I’m using a helper object called DataRequest, which you can ignore.
3 – Instantiating objects based on your JSON output.
Creating new objects based on your type and JSON data isn’t a lot of work. I’m using Json.NET to assist me. For a reusable solution, you can create a helper function supporting generics.
In my sample, I’m using a class called DataResponse. This is a wrapper class containing metadata and can be removed if desired.
Note: You can use a service like http://json2csharp.com/ to generate pain C# classed.
Conclusion
That’s it! As I mentioned, this is just covering the basics to get you started. A lot more is possible and therefore included helpful links for further reading.
WITHOUT_ARRAY_WRAPPER – New important change in FOR JSONFormat Query Results as JSON with FOR JSON (SQL Server)
Interesting slides however Italian only
http://www.slideshare.net/suxstellino/ita-sql-saturday-462-parma-sql-server-2016-json-support
Recent Comments