JSON Challenges for DEVS in SQL Server

     FOR JSON lets you return the data in JSON format. As you might know, SQL Server can return and query JSON documents, but It doesn't have a special data type for JSON documents. You must store data as string in SQL Server. You can make SQL Server work like a NoSQL Database. Your web application can retrieve data as JSON document and you can use dynamic objects to make things flexible.

     Let's see an example first, In the following example, I retrieve data as JSON document and send it to directly to my front-end as string. JavaScript parses it and generates a grid from it. It's very flexible because there is no schema. Front-End will display whatever SQL Server returns. You can change query and without changing any code in the middle, your grid will display the data.

using (var sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["WideWorld"].ConnectionString))
            {
                var sqlcomm = new SqlCommand("

select top 10 OrderId, CustomerId, ContactPersonId, OrderDate 

from Sales.Orders FOR JSON PATH", sqlconn);             
                var dset = new DataSet();
                var adap = new SqlDataAdapter(sqlcomm);
                adap.Fill(dset);
                return dset.Tables[0].Rows[0][0].ToString();
            }

     You might be very excited If this is the first time you are seeing SQL Server returns data as JSON document. SQL Server is a relational database and I am sure trying to make it work like a NoSQL database has been a challenge. It feels like a NoSQL database when you look at the returned data but, in the back-end things work like a relational database. Look what happens when I change my query. Rather than 10 rows, I want 100 rows.

     As I said before, all I changed is the number of rows and my solution is already crashed. I taught there was a bug when I found this first time. Jovan Popovic gave me a great explanation what is really happening in the back-end. He said FOR JSON and FOR XML split output into 2KB chunks and return chunks to the client whenever the server-side buffer is full. This is done to avoid large JSON/XML results that are buffered on SQL Server engine if you are joining large tables. There is no way to change this 2KB buffer size. 

     What do we do now? You might be already saying that you don't want to deal with this and use something else. Specially, if you are using Entity Framework. Don't give up that easy! There are two ways to fix this problem, first one is concatenating chunks of 2KB strings by using SqlClient class. Let's try that first.

using (var sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["WideWorld"].ConnectionString))
            {
                var sqlcomm = new SqlCommand(

"select top 100 OrderId, CustomerId, ContactPersonId, OrderDate 

from Sales.Orders FOR JSON PATH", sqlconn);
                sqlconn.Open();
                var sb = new StringBuilder();
                var reader = sqlcomm.ExecuteReader();
                if (!reader.HasRows)
                {
                    sb.Append("[]");
                }
                else
                {
                    while (reader.Read())
                    {
                        sb.Append(reader.GetValue(0).ToString());
                    }
                }
                return sb.ToString();
            }

     When I ran this code and look at the value of sb as JSON. I can see a valid JSON document. It has been some time since I work with the old friend StringBuilder. 😀

     If you use Entity Framework and you don't want to use SqlClient class in your project, or you want a simple fix for this, you can introduce a local variable to your query and force SQL Server to return varchar(max) rather than 2KB chucks. As you can see in the following example, I declared a local variable named @result then I put the result of my query in it and return it.

using (var sqlconn = new SqlConnection(ConfigurationManager.ConnectionStrings["WideWorld"].ConnectionString))
            {
                var sqlcomm = new SqlCommand("

DECLARE @result NVARCHAR(MAX) = (select top 100 OrderId, CustomerId, ContactPersonId, 

OrderDate from Sales.Orders FOR JSON PATH) SELECT @result", sqlconn);             

                var dset = new DataSet();
                var adap = new SqlDataAdapter(sqlcomm);
                adap.Fill(dset);
                return dset.Tables[0].Rows[0][0].ToString();
            }

     This returns a valid JSON document too.