Convert Datatable to JSON in Asp.net C# [3 ways]

Convert Datatable to JSON C#: This article explains how to convert DataTable to JSON string in Asp.net C#. i.e. serialize DataTable to JSON array in C#. For example, let's say you have SQL database and now want to return SQL data to JSON in C# application i.e how to return JSON String from DataTable in Asp.net C#. There are 3 ways to achieve this task and that are: By using StringBuilder, By using JavaScriptSerializer, By using Json.Net DLL ( newtonsoft )

What is JSON?

JSON stands for (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for humans to read and write. It is easy for machines to parse and generate.

JSON is a text format that is completely language independent but uses conventions that are familiar to programmers of the C-family of languages, including C, C++, C#, Java, JavaScript, Perl, Python, etc.

These properties make JSON an ideal data-interchange language. Because of that JSON has become a popular format for data interchange in Ajax based applications.

 In short JSON, is a syntax for storing and exchanging data. JSON is an easier-to-use alternative to XML.

3 Ways to convert Datatable to JSON  in C#

1. Convert DataTable to JSON using StringBuilder.
2. Convert DataTable to JSON using JavaScriptSerializer.
3. Convert DataTable to JSON using Json.Net DLL.

Using any one above method we can serialize .net DataTable to JSON String in C#, we can also return DataSet as JSON.

Output: 

---

First of all, we have to fetch the records from the database (MS Sqlserver) into the C# DataTable, or we can add dynamic rows to our DataTable. So our code looks like as written below.

//*
public DataTable getData()
{
	DataTable dt = new DataTable();
	dt.Columns.Add("UserId", typeof(Int32));
	dt.Columns.Add("UserName", typeof(string));
	dt.Columns.Add("Education", typeof(string));
	dt.Columns.Add("Location", typeof(string));
	dt.Rows.Add(1, "Satinder Singh", "Bsc Com Sci", "Mumbai");
	dt.Rows.Add(2, "Amit Sarna", "Mstr Com Sci", "Mumbai");
	dt.Rows.Add(3, "Andrea Ely", "Bsc Bio-Chemistry", "Queensland");
	dt.Rows.Add(4, "Leslie Mac", "MSC", "Town-ville");
	dt.Rows.Add(5, "Vaibhav Adhyapak", "MBA", "New Delhi");
	dt.Rows.Add(6, "Johny Dave", "MCA", "Texas");
	return dt;
}
//*

Then convert the DataTable into a JSON object by using any above methods which return the JSON object. Let's go through each method step by step.

# Method 1: Using C# StringBuilder.

Look: This is how JSON sample data looks {"firstName":"Satinder", "lastName":"Singh"} .

JSON objects are written inside curly braces and can contain multiple name/values pairs.

So using StringBuilder we can create similar JSON Structured String.

As we are using StringBuilder so, we need to import System.Text namespace in our page as shown below.

//*
using System.Text;
//*

Below code will generate JSON string, here we are making a for loop, over our DataTable rows and columns.

Fetch each data (value), and append to our jsonString StringBuilder. This is how our code looks like

//*
public string DataTableToJsonWithStringBuilder(DataTable table)
{
   var jsonString = new StringBuilder();
   if (table.Rows.Count > 0)
   {
	   jsonString.Append("[");
	   for (int i = 0; i < table.Rows.Count; i++)
	   {
		   jsonString.Append("{");
		   for (int j = 0; j < table.Columns.Count; j++)
		   {
			   if (j < table.Columns.Count - 1)
			   {
				   jsonString.Append(""" + table.Columns[j].ColumnName.ToString()
									 + "":" + """
									 + table.Rows[i][j].ToString() + "",");
			   }
			   else if (j == table.Columns.Count - 1)
			   {
				   jsonString.Append(""" + table.Columns[j].ColumnName.ToString()
									 + "":" + """
									 + table.Rows[i][j].ToString() + """);
			   }
		   }
		   if (i == table.Rows.Count - 1)
		   {
			   jsonString.Append("}");
		   }
		   else
		   {
			   jsonString.Append("},");
		   }
	   }
	   jsonString.Append("]");
   }
	return jsonString.ToString();
}
//*

# Method 2: Using JavaScriptSerializer.

As we are using JavaScriptSerializer so first, we need to import serialization in our page, i.e.,   System.Web.Script.Serialization namespace into our page as shown in below code.

//*
using System.Web.Script.Serialization;
//*

 The JavaScriptSerializer class is used internally by the asynchronous communication layer to serialize and deserialize the data.

To serialize an object, use the Serialize method. To deserialize a JSON string, use the Deserialize or DeserializeObject methods.

Here we use serialize method to get JSON format data. So our code looks like as written below.

 public string DataTableToJsonWithJavaScriptSerializer(DataTable table)
{
	JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
	List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
	Dictionary<string, object> childRow;
	foreach (DataRow row in table.Rows)
	{
		childRow = new Dictionary<string, object>();
		foreach (DataColumn col in table.Columns)
		{
			childRow.Add(col.ColumnName, row[col]);
		}
		parentRow.Add(childRow);
	}
	return jsSerializer.Serialize(parentRow);
}

# Method 3: Using nuget Json.Net DLL (Newtonsoft).

Look :

Now in this method, we are going to convert our C# datatable to JSON using newtonsoft DLL.

For this first, we need to download Json.Net DLL. We can download it from Nuget.org and then import Newtonsoft.Json namespace in our page as shown in below code.

Json.NET is a popular high-performance JSON framework for .NET. So our final code looks like as written below

//*
using Newtonsoft.Json;
//*

 Using Json.net Newtonsoft DLL its very easy to get JSON data. Below written a small piece of code will convert the dataTable to JSON.

//*
public string DataTableToJsonWithJsonNet(DataTable table) 
{
   string jsonString=string.Empty;
   jsonString = JsonConvert.SerializeObject(table);
   return jsonString;
}
//*

 Yes, we are done with JSON. Using json.net library we can also convert Dataset to JSON, which returns all tables exists in given DataSet.

Conclusion:  Once we converted our DataTable to JSON, then using jQuery we can append JSON data anywhere on the Webpage.  I have used JSON object many times in my Charts related project where on server-side C# creating a WebMethod to convert my Data into JSON result.

Other Reference:

• StringBuildder Class
• JSON
• NewtonSoft Json
• DataTable Class

You must also check these articles:

• Simple Asp.net jQuery ajax JSON Example, WebMethod.
• How to send JSON data to Generic Handler ashx file in Asp.net.
• Chart.js + Asp.net: Create Pie chart with JSON response in Jquery.