4 ways to Convert JSON to DataTable in C# – asp.net

Problem: Sometime we do require to convert JSON to DataTable in C# Windows Application or Web application or website to display data using DataTable in GridView or DataGrid or other Server Control. I strongly recommend to read my other post as a reference before reading this post are Convert DataTable to JSON in C#, Convert DataTable to List C#, Extension Method in C#, Extension Method with parameter in C#, Convert List to XML in C# etc.

Solutions:

Here as a solution I will discuss three different ways from which we could Convert JSON to DataTable in C#.

1. Using Simple method

In this method we just split the string by using specific characters and tried to fetch data from JSON string. I have added full code snippet for your reference. Please refer and try to understand.

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

namespace Convert_JSON_to_DataTable_Csharp_TechnoThirsty
{
    class Program
    {
        static void Main(string[] args)
        {
            string strJSON = @"[
  {
    EmployeeID: ""1"",
    EmployeeName: ""Dhruv""
  },
  {
    EmployeeID: ""2"",
    EmployeeName: ""Bhavin""
  },
  {
    EmployeeID: ""4"",
    EmployeeName: ""Arvind""
  },
  {
    EmployeeID: ""5"",
    EmployeeName: ""Aditya""
  },
  {
    EmployeeID: ""6"",
    EmployeeName: ""Vidhi""
  },
  {
    EmployeeID: ""7"",
    EmployeeName: ""Neha""
  }
]";

            DataTable dtUsingMethod = GetJSONToDataTableUsingMethod(strJSON);
        }
          
        public static DataTable GetJSONToDataTableUsingMethod(string JSONData)
        {
            DataTable dtUsingMethodReturn = new DataTable();
            string[] jsonStringArray = Regex.Split(JSONData.Replace("[", "").Replace("]", ""), "},{");
            List<string> ColumnsName = new List<string>();
            foreach (string strJSONarr in jsonStringArray)
            {
                string[] jsonStringData = Regex.Split(strJSONarr.Replace("{", "").Replace("}", ""), ",");
                foreach (string ColumnsNameData in jsonStringData)
                {
                    try
                    {
                        int idx = ColumnsNameData.IndexOf(":");
                        string ColumnsNameString = ColumnsNameData.Substring(0, idx).Replace("\"", "").Trim();
                        if (!ColumnsName.Contains(ColumnsNameString))
                        {
                            ColumnsName.Add(ColumnsNameString);
                        }
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));
                    }
                }
                break;
            }
            foreach (string AddColumnName in ColumnsName)
            {
                dtUsingMethodReturn.Columns.Add(AddColumnName);
            }
            foreach (string strJSONarr in jsonStringArray)
            {
                string[] RowData = Regex.Split(strJSONarr.Replace("{", "").Replace("}", ""), ",");
                DataRow nr = dtUsingMethodReturn.NewRow();
                foreach (string rowData in RowData)
                {
                    try
                    {
                        int idx = rowData.IndexOf(":");
                        string RowColumns = rowData.Substring(0, idx).Replace("\"", "").Trim();
                        string RowDataString = rowData.Substring(idx + 1).Replace("\"", "").Trim();
                        nr[RowColumns] = RowDataString;
                    }
                    catch (Exception ex)
                    {
                        continue;
                    }
                }
                dtUsingMethodReturn.Rows.Add(nr);
            }
            return dtUsingMethodReturn;
        } 
    }
}

2. Using NewtonSoft.dll

In this method first of all we have to add Newtonsoft.dll. For this we have to add NeGet Package as shown below image:

 

After adding NeGet Package we have to add “using Newtonsoft.Json” and find below code for your reference.

 

using System;
using System.Data;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using Newtonsoft.Json;

namespace Convert_JSON_to_DataTable_Csharp_TechnoThirsty
{
    class Program
    {
        static void Main(string[] args)
        {
            string strJSON = @"[
  {
    EmployeeID: ""1"",
    EmployeeName: ""Dhruv""
  },
  {
    EmployeeID: ""2"",
    EmployeeName: ""Bhavin""
  },
  {
    EmployeeID: ""4"",
    EmployeeName: ""Arvind""
  },
  {
    EmployeeID: ""5"",
    EmployeeName: ""Aditya""
  },
  {
    EmployeeID: ""6"",
    EmployeeName: ""Vidhi""
  },
  {
    EmployeeID: ""7"",
    EmployeeName: ""Neha""
  }
]";

            DataTable dtUsingMethod = GetJSONToDataTableUsingNewtonSoftDll(strJSON);
        }

        public static DataTable GetJSONToDataTableUsingNewtonSoftDll(string JSONData)
        {
            DataTable dt = (DataTable)JsonConvert.DeserializeObject(JSONData, (typeof(DataTable)));
            return dt;
        } 
    }
}

 

3. Using NewtonSoft.dll by Linq

As shown in method-2 I have demonstrate how we could add NewtonSoft.dll. In this method we have used NewtonSoft’s  “using Newtonsoft.Json.Linq;” and we tried to convert Json into DataTable. Please find below code snippet for your reference.

using System;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace Convert_JSON_to_DataTable_Csharp_TechnoThirsty
{
    class Program
    {
        static void test(string[] args)
        {
            string strJSON = @"{Employees:[
  {
    EmployeeID: ""1"",
    EmployeeName: ""Dhruv""
  },
  {
    EmployeeID: ""2"",
    EmployeeName: ""Bhavin""
  },
  {
    EmployeeID: ""4"",
    EmployeeName: ""Arvind""
  },
  {
    EmployeeID: ""5"",
    EmployeeName: ""Aditya""
  },
  {
    EmployeeID: ""6"",
    EmployeeName: ""Vidhi""
  },
  {
    EmployeeID: ""7"",
    EmployeeName: ""Neha""
  }
]}";

            DataTable dtNewtonSoftLinq = ConvertJsonToDatatableLinq(strJSON);
        }

        public static DataTable ConvertJsonToDatatableLinq(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());
        }
    }
}

 

4. Using Extension Method and Class

Finally in method-4 I have used Extension method and class to convert JSON string into DataTable. As you can see in below code snippet I have created Extension method named with “ToDataTable” and declared class named with “Employee”.

using System;
using System.Data;
using System.Linq;
using System.Text.RegularExpressions;
using System.Collections.Generic;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.ComponentModel;

namespace Convert_JSON_to_DataTable_Csharp_TechnoThirsty
{
    class Program
    {
        static void test(string[] args)
        {
            string strJSON = @"[
  {
    EmployeeID: ""1"",
    EmployeeName: ""Dhruv""
  },
  {
    EmployeeID: ""2"",
    EmployeeName: ""Bhavin""
  },
  {
    EmployeeID: ""4"",
    EmployeeName: ""Arvind""
  },
  {
    EmployeeID: ""5"",
    EmployeeName: ""Aditya""
  },
  {
    EmployeeID: ""6"",
    EmployeeName: ""Vidhi""
  },
  {
    EmployeeID: ""7"",
    EmployeeName: ""Neha""
  }
]";
            List<Employee> Employees = JsonConvert.DeserializeObject<List<Employee>>(strJSON);
            DataTable dt = Employees.ToDataTable<Employee>();
        }

        public static DataTable ConvertJsonToDatatableLinq(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());
        }
    }

    class Employee
    {
        public int EmployeeID { get; set; }
        public string EmployeeName { get; set; }

    }
    public static class MyExtensionClass
    {
        public static DataTable ToDataTable<T>(this List<T> data)
        {
            PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
            DataTable table = new DataTable();

            for (int i = 0; i < props.Count; i++)
            {
                PropertyDescriptor prop = props[i];
                table.Columns.Add(prop.Name, prop.PropertyType);
            }

            object[] values = new object[props.Count];

            foreach (T item in data)
            {
                for (int i = 0; i < values.Length; i++)
                {
                    values[i] = props[i].GetValue(item);
                }
                table.Rows.Add(values);
            }
            return table;
        }
    }
}

 

As an output of all programs we will get below image output as a DataTable:

 

Folks, I request you to test methods in your environment and use in live environment in terms of performance as well. I would be happy to hear from you guys. Let me know topics on which you want to know. Happy Coding!!!