convert datatable to json c#

Convert Json Response to Datatable C#

Json format is a lightweight data-interchange format for storing and exchanging data. It is easy for machines to parse and generate. I recently needed to serialize a datatable to JSON. So in this c# tutorial we will learn about how to convert datatable to json c# asp.net web application.

The following example demonstrates two different ways to convert datatable to json in asp.net c#. First you can write customized method and secondly you can use serialization () in asp.net web application. Find the source code below:-

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Convert datatable to json c#</title>
</head>
<body>
    <form id="form1" runat="server">
        <h1>Convert datatable to json list</h1>
        <div>
            <asp:Literal ID="Literal1" runat="server"></asp:Literal>
        </div>
    </form>
</body>
</html>


using System;
using System.Data;
using System.Text;
using System.Collections.Generic;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string jsonVal = ConvertTableToJSON();
        Literal1.Text = GetJson();
    }

    // Function to convert datatable to JSON in ASP.net using C#
    public static string ConvertTableToJSON()
    {
        DataTable dtEmployee = new DataTable();
        dtEmployee.Columns.Add("EmpId", typeof(int));
        dtEmployee.Columns.Add("Name", typeof(string));
        dtEmployee.Columns.Add("Address", typeof(string));

        //
        // Here we add five DataRows.
        //
        dtEmployee.Rows.Add(25, "Ravi", "Gurgaon");
        dtEmployee.Rows.Add(50, "Sachin", "Noida");
        dtEmployee.Rows.Add(10, "Nitin", "Noida");
        dtEmployee.Rows.Add(21, "Aditya", "Meerut");

        string[] jsonArray = new string[dtEmployee.Columns.Count];
        string headString = string.Empty;

        for (int i = 0; i < dtEmployee.Columns.Count; i++)
        {
            jsonArray[i] = dtEmployee.Columns[i].Caption; // Array for all columns
            headString += "'" + jsonArray[i] + "' : '" + jsonArray[i] + i.ToString() + "%" + "',";
        }

        headString = headString.Substring(0, headString.Length - 1);

        StringBuilder sb = new StringBuilder();
        sb.Append("[");

        if (dtEmployee.Rows.Count > 0)
        {
            for (int i = 0; i < dtEmployee.Rows.Count; i++)
            {
                string tempString = headString;
                sb.Append("{");

                // To get each value from the datatable
                for (int j = 0; j < dtEmployee.Columns.Count; j++)
                {
                    tempString = tempString.Replace(dtEmployee.Columns[j] + j.ToString() + "%", dtEmployee.Rows[i][j].ToString());
                }

                sb.Append(tempString + "},"); // append tempstring value in sb
            }
        }
        else
        {
            string tempString = headString;
            sb.Append("{");
            for (int j = 0; j < dtEmployee.Columns.Count; j++)
            {
                tempString = tempString.Replace(dtEmployee.Columns[j] + j.ToString() + "%", "-");
            }

            sb.Append(tempString + "},");
        }

        sb = new StringBuilder(sb.ToString().Substring(0, sb.ToString().Length - 1));
        sb.Append("]");
        return sb.ToString(); // json format string
    }

    public string GetJson()
    {
        System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();
        List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
        Dictionary<string, object> row = null;

        DataTable dtEmployee = new DataTable();
        dtEmployee.Columns.Add("EmpId", typeof(int));
        dtEmployee.Columns.Add("Name", typeof(string));
        dtEmployee.Columns.Add("Address", typeof(string));

        //
        // Here we add five DataRows.
        //
        dtEmployee.Rows.Add(25, "Ravi", "Gurgaon");
        dtEmployee.Rows.Add(50, "Sachin", "Noida");
        dtEmployee.Rows.Add(10, "Nitin", "Noida");
        dtEmployee.Rows.Add(21, "Aditya", "Meerut");

        foreach (DataRow dr in dtEmployee.Rows)
        {
            row = new Dictionary<string, object>();
            foreach (DataColumn col in dtEmployee.Columns)
            {
                row.Add(col.ColumnName, dr[col]);
            }
            rows.Add(row);
        }
        return serializer.Serialize(rows);
    }

}

Download Source Code

Also there is an easier method than the other answers here, which require first deserializing into a c# class, and then turning it into a datatable. It is possible to go directly to a datatable, with JSON.NET and code like this:

DataSet data = JsonConvert.DeserializeObject<DataSet>(json);

Did you find this post useful? I hope you liked this article. Please share with me your valuable suggestions and feedback.

LEAVE A REPLY

Please enter your comment!
Please enter your name here