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<string, object>>();
  4. foreach (DataRow row in table.Rows)
  5. {
  6. var dict = new Dictionary<string, object>();
  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);
  15. }
Second Method - Dataset to JSON string
  1. public static object dataSetToJSON(DataSet ds)
  2. {
  3. ArrayList root = new ArrayList();
  4. List<Dictionary<string, object>> table;
  5. Dictionary<string, object> data;
  7. foreach (DataTable dt in ds.Tables)
  8. {
  9. table = new List<Dictionary<string, object>>();
  10. foreach (DataRow dr in dt.Rows)
  11. {
  12. data = new Dictionary<string, object>();
  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);
  23. }
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);
  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. }
  22. return JsonConvert.DeserializeObject<DataTable>(trgArray.ToString());
  23. }
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;
  54. }
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. }
  16. }
I hope this is useful to all the readers. Happy Coding!