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!!!