Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications

|

Generating JSON Document from SQL Query

Post Author: Joe D365 |

If you are looking for ways to generate JSON files from SQL server data or if you need to pass JSON data to downstream systems, then you are reading the proper blog! This blog describes how to utilize the FOR JSON feature of SQL server to generate JSON data, and eventually JSON file with well-formatted JSON data in it.

Generating JSON data is straightforward. The syntax is as follows:

Select
contactid,
firstname,
lastname
From contact
FOR  JSON  PATH

This is as simple as appending your SQL select query with the FOR JSON clause. Multiple options are available for the structure of the output, including FOR JSON PATH and FOR JSON AUTO. More information can be found here: Microsoft Documentation.

To generate files out of the retrieved JSON data, a little bit of coding is necessary, as SQL returns chunks of rows of JSON data and each row is not a complete JSON. To combine the JSON data retrieved by the FOR JSON clause to generate complete JSON, use a small piece of C# code as follows:

var queryWithForJson = "SELECT ... FOR JSON";
var conn = new SqlConnection("");
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());
    }
}

One thing to keep in mind is that if the result of the SQL query is too large, the JSON builder might produce a memory exception. To avoid this, open a file and start appending incoming rows into it as the code loops through each retrieved row. Example code snippet:

File.AppendAllText(path, jsonResult);

Hopefully this helps you generate a JSON document from a SQL query.

As always, happy D365'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

PowerObjects Recommends