Converting Datatable And Dataset To JSON String And Vice Versa

In this blog, we will learn how to convert a datatable and dataset into a JSON string and vice versa.

Here, in this blog, I have described four methods to convert a datatable or a dataset into a JSON string and vice versa.
 
For this, you have to use the following namespaces.
  1. using System;  
  2. using System.Xml;  
  3. using System.Data;  
  4. using System.Linq;  
  5. using System.Collections;  
  6. using System.Collections.Generic;  
  7. using System.Text.RegularExpressions;  
  8. using System.Web.Script.Serialization;  
  9. using Newtonsoft.Json;  
  10. using Newtonsoft.Json.Linq;  
First Method - DataTable to JSON String
 
Here, I have used the JavaScriptSerializer class to covert the DataTable into a JSON string.
  1. public static object dataTableToJSON(DataTable table)  
  2. {  
  3.     var list = new List<Dictionary<stringobject>>();  
  4.     foreach (DataRow row in table.Rows)  
  5.     {  
  6.         var dict = new Dictionary<stringobject>();  
  7.         foreach (DataColumn col in table.Columns)  
  8.         {  
  9.             dict[col.ColumnName] = (Convert.ToString(row[col]));  
  10.         }  
  11.         list.Add(dict);  
  12.     }  
  13.     JavaScriptSerializer serializer = new JavaScriptSerializer();  
  14.     return serializer.Serialize(list);  

Second Method - Dataset to JSON string
  1. public static object dataSetToJSON(DataSet ds)  
  2. {  
  3.     ArrayList root = new ArrayList();  
  4.     List<Dictionary<stringobject>> table;  
  5.     Dictionary<stringobject> data;  
  6.   
  7.     foreach (DataTable dt in ds.Tables)  
  8.     {  
  9.         table = new List<Dictionary<stringobject>>();  
  10.         foreach (DataRow dr in dt.Rows)  
  11.         {  
  12.             data = new Dictionary<stringobject>();  
  13.             foreach (DataColumn col in dt.Columns)  
  14.             {  
  15.                 data.Add(col.ColumnName, dr[col]);  
  16.             }  
  17.             table.Add(data);  
  18.         }  
  19.         root.Add(table);  
  20.     }  
  21.     JavaScriptSerializer serializer = new JavaScriptSerializer();  
  22.     return serializer.Serialize(root);  

Third Method - JSON String to DataTable
 
Here, I have used Newtonsoft.
  1. public static DataTable jsonToDataTable(string jsonString)  
  2. {  
  3.     var jsonLinq = JObject.Parse(jsonString);  
  4.   
  5.     // Find the first array using Linq  
  6.     var srcArray = jsonLinq.Descendants().Where(d => d is JArray).First();  
  7.     var trgArray = new JArray();  
  8.     foreach (JObject row in srcArray.Children<JObject>())  
  9.     {  
  10.         var cleanRow = new JObject();  
  11.         foreach (JProperty column in row.Properties())  
  12.         {  
  13.             // Only include JValue types  
  14.             if (column.Value is JValue)  
  15.             {  
  16.                 cleanRow.Add(column.Name, column.Value);  
  17.             }  
  18.         }  
  19.         trgArray.Add(cleanRow);  
  20.     }  
  21.   
  22.     return JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());  

Or you can use this also to convert the JSON String into DataTable.
  1. public static DataTable JsonStringToDataTable(string jsonString)  
  2. {  
  3.     DataTable dt = new DataTable();  
  4.     string[] jsonStringArray = Regex.Split(jsonString.Replace("[""").Replace("]"""), "},{");  
  5.     List<string> ColumnsName = new List<string>();  
  6.     foreach (string jSA in jsonStringArray)  
  7.     {  
  8.         string[] jsonStringData = Regex.Split(jSA.Replace("{""").Replace("}"""), ",");  
  9.         foreach (string ColumnsNameData in jsonStringData)  
  10.         {  
  11.             try  
  12.             {  
  13.                 int idx = ColumnsNameData.IndexOf(":");  
  14.                 string ColumnsNameString = ColumnsNameData.Substring(0, idx - 1).Replace("\"""");  
  15.                 if (!ColumnsName.Contains(ColumnsNameString))  
  16.                 {  
  17.                     ColumnsName.Add(ColumnsNameString);  
  18.                 }  
  19.             }  
  20.             catch (Exception ex)  
  21.             {  
  22.                 Console.WriteLine(ex.Message);  
  23.                 throw new Exception(string.Format("Error Parsing Column Name : {0}", ColumnsNameData));  
  24.             }  
  25.         }  
  26.         break;  
  27.     }  
  28.     foreach (string AddColumnName in ColumnsName)  
  29.     {  
  30.         dt.Columns.Add(AddColumnName);  
  31.     }  
  32.     foreach (string jSA in jsonStringArray)  
  33.     {  
  34.         string[] RowData = Regex.Split(jSA.Replace("{""").Replace("}"""), ",");  
  35.         DataRow nr = dt.NewRow();  
  36.         foreach (string rowData in RowData)  
  37.         {  
  38.             try  
  39.             {  
  40.                 int idx = rowData.IndexOf(":");  
  41.                 string RowColumns = rowData.Substring(0, idx - 1).Replace("\"""");  
  42.                 string RowDataString = rowData.Substring(idx + 1).Replace("\"""");  
  43.                 nr[RowColumns] = RowDataString;  
  44.             }  
  45.             catch (Exception ex)  
  46.             {  
  47.                 Console.WriteLine(ex.Message);  
  48.                 continue;  
  49.             }  
  50.         }  
  51.         dt.Rows.Add(nr);  
  52.     }  
  53.     return dt;  

Fourth Method - JSON String to Dataset
  1. public static DataSet jsonToDataSet(string jsonString)  
  2. {  
  3.     try  
  4.     {  
  5.         XmlDocument xd = new XmlDocument();  
  6.         jsonString = "{ \"rootNode\": {" + jsonString.Trim().TrimStart('{').TrimEnd('}') + "} }";  
  7.         xd = (XmlDocument)JsonConvert.DeserializeXmlNode(jsonString);  
  8.         DataSet ds = new DataSet();  
  9.         ds.ReadXml(new XmlNodeReader(xd));  
  10.         return ds;  
  11.     }  
  12.     catch (Exception ex)  
  13.     {  
  14.         throw new ArgumentException(ex.Message);  
  15.     }  

 I hope this is useful to all the readers. Happy Coding!