C# - Converting DataTable to JSON Format


In previous articles I explained about .Net Framework - An Introduction, Datetime to String format - C#. In this article I will explain about JSON format and converting datatable to json format in a simple way.


JSON (JavaScript Object Notation) is a lightweight data-interchange format. It is easy for machines to parse and generate. JSON is a text format that is completely language independent. Fore more information click here

There are the following ways to convert datatable to json.

Method 1:
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));
        dtEmployee.Columns.Add("Date", typeof(DateTime));

        //
        // Here we add five DataRows.
        //
        dtEmployee.Rows.Add(25, "Rk", "Gurgaon", DateTime.Now);
        dtEmployee.Rows.Add(50, "Sachin", "Noida", DateTime.Now);
        dtEmployee.Rows.Add(10, "Nitin", "Noida", DateTime.Now);
        dtEmployee.Rows.Add(21, "Aditya", "Meerut", DateTime.Now);
        dtEmployee.Rows.Add(100, "Mohan", "Banglore", DateTime.Now);

        //gvEmployees.DataSource = dtEmployee;


        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 + "},");
            }
        }
        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 formated output
    }

Output

How to convert datatable to JSON convert to json,  create a json, a data table,c# and web services


Method 2 
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));
        dtEmployee.Columns.Add("Date", typeof(DateTime));

        //
        // Here we add five DataRows.
        //
        dtEmployee.Rows.Add(25, "Rk", "Gurgaon", DateTime.Now);
        dtEmployee.Rows.Add(50, "Sachin", "Noida", DateTime.Now);
        dtEmployee.Rows.Add(10, "Nitin", "Noida", DateTime.Now);
        dtEmployee.Rows.Add(21, "Aditya", "Meerut", DateTime.Now);
        dtEmployee.Rows.Add(100, "Mohan", "Banglore", DateTime.Now);

        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);
    }    

Output

 

Download the source code click here ..
Please leave your comments, suggestions and queries about this post in the comment sections in order for me to improve my writing skills and to showcase more useful posts. Thanks for reading! :)

Post a Comment

  1. This is the best way to serialize in JSON format. Thanks a lot.

    ReplyDelete
  2. Replies
    1. u're welcome.. :) and keep visiting for more articles.

      Delete
  3. Replies
    1. u're welcome.. :) and keep visiting for more articles.

      Delete

 
Top