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#.
- Keep reading on C# Serialize Object to JSON Newtonsoft
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.
Hi,
Could you please help me in converting the below Json into DataTable:
[
{
“SaleId”: “110”,
“Customers”: [
{
“Customer”: {
“Id”: “1”,
“Name”: “Hunaid”
},
“Address”: {
“City”: “Hyderabad”,
“State”: “Telangana”,
“Country”: “India”,
“Pincode”: “500064”
},
“Contact”: {
“email”: “ITSHUNAID@HOTMAIL.COM”,
“Phone”: “0123456789”
}
},
{
“Customer”: {
“Id”: “2”,
“Name”: “Ali Akbar”
},
“Address”: {
“City”: “Hyderabad”,
“State”: “Telangana”,
“Country”: “India”,
“Pincode”: “500064”
},
“Contact”: {
“email”: “ITSHUNAID@HOTMAIL.COM”,
“Phone”: “0123456789”
}
},
{
“Customer”: {
“Id”: “3”,
“Name”: “Ali Amer”
},
“Address”: {
“City”: “Hyderabad”,
“State”: “Telangana”,
“Country”: “India”,
“Pincode”: “500064”
},
“Contact”: {
“email”: “ITSHUNAID@HOTMAIL.COM”,
“Phone”: “0123456789”
}
}
]
}
]
Looking forward to hear from you.
Best Regards,
Hunaid Hussain.
This is not a valid JSON string, check it again.