How to Convert JSON to Datatable in C#

In the following example, I will discuss a problem statement of how to convert JSON to datatable in C#. As a solution, I will discuss three different ways from which we could Parse JSON to DataTable in C#.

What is JSON Schema?

JSON stands for JavaScript Object Notation. JSON is a lightweight format for storing and transporting data. It is a text format that is completely language independent. It is very easy to read and write.

"[ { \"CustomerAddress\": \"Banglore\", \"CustomerId\": 1, \"CustomerName\": \"Dell\" }, { \"CustomerAddress\": \"New Delhi\", \"CustomerId\": 2, \"CustomerName\": \"Sony\" } } ]"

Convert JSON to datatable C# using Newtonsoft.dll

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Linq;
public static DataTable ConvertJsonToDatatable(string jsonString)
{
    var jsonLinq = JObject.Parse(jsonString);
    // Find the first array using Linq
    var linqArray = jsonLinq.Descendants().Where(x => x is JArray).First();
    var jsonArray = new JArray();
    foreach (JObject row in linqArray.Children<JObject>())
    {
        var createRow = new JObject();
        foreach (JProperty column in row.Properties())
        {
            // Only include JValue types
            if (column.Value is JValue)
            {
                createRow.Add(column.Name, column.Value);
            }
        }
        jsonArray.Add(createRow);
    }
    return JsonConvert.DeserializeObject<DataTable>(jsonArray.ToString());
}

Using the Extension Method and Class

using System;
using System.Collections.Generic;
using System.Data;
using System.Text.RegularExpressions;
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            string jsonResult = "[{\"CustomerAddress\":\"Banglore\",\"CustomerId\":1,\"CustomerName\":\"Dell\"},{\"CustomerAddress\":\"New Delhi\",\"CustomerId\":2,\"CustomerName\":\"Sony\"}}]";
            DataTable dt = ConvertJsonToDatatable(jsonResult);
            Gridview1.DataSource = dt;
            Gridview1.DataBind();
        }
    }
    // how to convert json to datatable in asp.net c#
    protected DataTable ConvertJsonToDatatable(string jsonString)
    {
        DataTable dt = new DataTable();
        //strip out bad characters
        string[] jsonParts = Regex.Split(jsonString.Replace("[", "").Replace("]", ""), "},{");
        //hold column names
        List<string> dtColumns = new List<string>();
        //get columns
        foreach (string jp in jsonParts)
        {
            //only loop thru once to get column names
            string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
            foreach (string rowData in propData)
            {
                try
                {
                    int idx = rowData.IndexOf(":");
                    string n = rowData.Substring(0, idx - 1);
                    string v = rowData.Substring(idx + 1);
                    if (!dtColumns.Contains(n))
                    {
                        dtColumns.Add(n.Replace("\"", ""));
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("Error Parsing Column Name : {0}", rowData));
                }
            }
            break; // TODO: might not be correct. Was : Exit For
        }
        //build dt
        foreach (string c in dtColumns)
        {
            dt.Columns.Add(c);
        }
        //get table data
        foreach (string jp in jsonParts)
        {
            string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
            DataRow nr = dt.NewRow();
            foreach (string rowData in propData)
            {
                try
                {
                    int idx = rowData.IndexOf(":");
                    string n = rowData.Substring(0, idx - 1).Replace("\"", "");
                    string v = rowData.Substring(idx + 1).Replace("\"", "");
                    nr[n] = v;
                }
                catch (Exception ex)
                {
                    continue;
                }
            }
            dt.Rows.Add(nr);
        }
        return dt;
    }
}

Conclusion

I hope you liked this article based on how to parse JSON to datatable in C#. I would like to have feedback from my blog readers. Your valuable feedback, question, or comments about this article are always welcome.

Buy me a coffee