Sql2Json Helper

This SQL2JSONHELPER package help to convert a Data Table into a JSON document, it doesn’t need equivalent definition of Data Transfer Object to serialize. We could use the advantages of Dynamic/JArray/JObject classes which will simplify our tasks with loosely coupling.

  • For example my SQL Query outputs a data table with the column names Employee ID, First Name, Last Name, DOB, and Salary, we don’t need to define these column names as property/member of a class to serialize as JSON document
  • Please look this approach recommended to use a DTO object only if you consider there is not much business computation/validation performed between the UI and Database backend in C# code
  • This would be recommended where ever you export the data from SQL to JSON document to easily bind with the web interfaces
  • Eventually I have to a write another helper to convert a JSON document into a Data table that helps to input as SQL stored procedure. I have read from Microsoft site that SQL 2016 going to have inbuilt JSON parsing, definitely it would be the feature that every developer anticipating now

 

Create a sample ASP.NET MVC Project, Find and Include the Sql2JsonHelper package to the project using NuGet package manager.

sql2json01

 

You should have the required database configuration for Enterprise library on your web.config file to connect and query your SQL Database.

sql2json02

You may not need to use SqlHelper method in case you have different style of querying from SQL. The JsonHelper used to convert any Data Table, even any generic data table you construct your own. Please see the sample code how it converts a data table into a JSON document,

The Controller code to convert the Data Table into JArray Type, and send the model to view.

public ActionResult Index() {
 SqlHelper SqlHelper = new SqlHelper();
 SqlHelper.Command = SqlHelper.DataBase.GetSqlStringCommand("SELECT EmployeeId, FirstName, LastName, DOB, Salary FROM Employee Where Salary > 4500");
 DataSet ds = SqlHelper.ExecuteDataSet();
 JArray Employees = JsonHelper.ConvertDataTableToJArray(ds.Tables[0]);
 return View(Employees); 
}

The Razor code to read the JSON document and render as grid.

Note: This is Sample implementation, you might prefer to use API Controller and render the data using jQuery Asynchronous data binding using DataTables or Kendo controls. JArray is a collection of JObject, you can iterate in a for loop to display all records.


@foreach (Newtonsoft.Json.Linq.JObject emp in Model)
{
}



@emp["EmployeeId"]


@string.Format("{1}, {0}", emp["LastName"].ToString(), emp["FirstName"].ToString())


@emp["DOB"]


@emp["Salary"]

Here is sample output, you can download the sample project from the below GITHUB link.

Leave a comment