Introduction

This article explains how to convert a DataTable to JSON in ASP.NET C#. In other words, how to serialize a DataTable to a JSON array in C#. In other words, how to return a JSON String from a DataTable in ASP.NET C#. There are 3 ways to do this, and they are using StringBuilder, JavaScriptSerializer, or using JSON.Net DLL (from Newtonsoft).

What is JSON?

JavaScript Object Notation (JSON) is a lightweight data interchange format. It is easy for humans to read and write and 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, and so on.

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.

The following is the procedure for converting a DataTable to a JSON object 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 of the preceding methods will serialize a .NET DataTable to a JSON String in C#.

First, we will get records from the database (Microsoft SQL Server) into a DataTable, or we can also add dynamic rows to our DataTable. So our code looks as in the following:

public DataTable GetData()
{
    DataTable dt = new DataTable();

    // Adding columns to the 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));

    // Adding rows to the DataTable
    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");

    return dt;
}

Then convert the DataTable into a JSON object using any of the previous methods that return the JSON object. Let’s go through each method step-by-step.

Method 1. Convert DataTable to JSON using StringBuilder

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

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

So using StringBuilder, we can create a similar JSON Structured String.

Since we are using StringBuilder, we need to import the System. Text namespace in our page as in the following:

Using System.Text; 

The following code will generate a JSON string. Here we are making a for loop over our DataTable rows and columns. Fetch the data (values) and append it to our JSONString StringBuilder. This is how our code looks.

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. Convert the DataTable to JSON using JavaScriptSerializer

Since we are using JavaScriptSerializer, we first need to import the System.Web.Script.Serialization namespace into our page as in the following 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 the serialize method to get the JSON format data. So our code looks as in the following:

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. Convert DataTable to JSON using JSON.Net DLL (Newtonsoft)

Now in this method, we will convert our C# Datatable to JSON using the Newtonsoft DLL.

For this, first, we need to download JSON.Net DLL. We can download it from Nuget.org and then import the Newtonsoft.JSON namespace into our page as in the following code. JSON.NET is a popular high-performance JSON framework. NET.

using Newtonsoft.JSON;  
  
public string DataTableToJSONWithJSONNet(DataTable table) {  
   string JSONString=string.Empty;  
   JSONString = JSONConvert.SerializeObject(table);  
   return JSONString;  
}  

Output

datatable to JSON