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

/ / 14 Comments

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:

You must also check these articles:

Thank you for reading, pls keep visiting this blog and share this in your network. Also, I would love to hear your opinions down in the comments.

PS: If you found this content valuable and want to do a favor, then Buy me a coffee

Subscribe to our newsletter

Get the latest and greatest from Codepedia delivered straight to your inbox.


Post Comment

Your email address will not be published. Required fields are marked *

14 Comments

  1. Anjan Kant 10/06/2015 05:45:58
    Very Well explained, it helped me. thanks
  2. Pravesh Semwal 11/18/2015 09:59:00
    Thanks, for this
  3. Shamick 02/01/2016 14:45:16
    I am kind of new to .net and object oriented in general. I added all this code but nothing shows on the page. I suspect this is because I did not call this method correctly. How do you do that from 'front page' or within those classes/objects?
  4. Satinder singh 02/01/2016 15:30:16
    Hi Shamick, Thanks for the reading, here the above article explains how to convert datatable into JSON with 3 different ways. For that have created 3 methods each return json string. Here's a syntax for calling each method Method 1
    string getJSONResult=DataTableToJsonWithStringBuilder(myDataTable);
    Method 2
    string getJSONResult=DataTableToJsonWithJavaScriptSerializer(myDataTable);
    Method 3
    string getJSONResult=DataTableToJsonWithJsonNet(myDataTable);
    You can use any one from it
  5. Shamick 02/01/2016 20:01:39
    Thanks. I finally got it. It's the little things that get me ... like putting that inside a class (or is that object?) public void showstring(DataTable table) Thank you.
  6. Junar from Philippines 02/02/2016 07:15:03
    Thank you very much for this tutorial. More powers
  7. Raul Rueda 09/20/2016 17:24:25
    I develop using the Method 1 and now that I follow your tutorial with serialize its so much faster. Thanks, sorry for my English =).
  8. Satinder singh 10/05/2016 07:32:18
    Hi Raul Rueda, Am glad this post helps you
  9. Satinder singh 01/16/2017 10:28:05
    Thanks Zoran, For sharing this library.
  10. Meghana 05/11/2017 05:14:16
    while converting from datatable to json string i am getting system out of memory exception, please provide solution