In this blog, we will learn, how to import a JSON file to a dataset without using any external libraries.

I have searched for this feature and only thing, I can found is a code, which can be used to convert a Simple JSON file to the datatable.

My requirement is little different. I have got a nested JSON file, which cannot be managed with a single datatable, but with a dataset with multiple datatables.

Thus, I made some changes in the code to convert JSON to the datatable and achieved the result.

Our JSON file looks like-
  1. {
  2. "ClientPersonal": [{
  3. "ClientID": "12001",
  4. "Name": "Ram",
  5. "DOB": "20-Aug-1980",
  6. "Mobile": "9999999999",
  7. "Email": "[email protected]"
  8. },
  9. {
  10. "ClientID": "12002",
  11. "Name": "Mohan",
  12. "DOB": "15-Jan-1989",
  13. "Mobile": "9999988888",
  14. "Email": "[email protected]"
  15. },
  16. {
  17. "ClientID": "12003",
  18. "Name": "Sam",
  19. "DOB": "11-Apr-1985",
  20. "Mobile": "8888888888",
  21. "Email": "[email protected]"
  22. },
  23. {
  24. "ClientID": "12004",
  25. "Name": "Kevin",
  26. "DOB": "20-Jun-1992",
  27. "Mobile": "7777788888",
  28. "Email": "[email protected]"
  29. },
  30. {
  31. "ClientID": "12005",
  32. "Name": "Ajay",
  33. "DOB": "22-Sep-1978",
  34. "Mobile": "8888877777",
  35. "Email": "[email protected]"
  36. }],
  37. "ClientOfficial": [{
  38. "ClientID": "12001",
  39. "Name": "Ram",
  40. "Service": "Hardware",
  41. "ServicePeriod": "2 years",
  42. "Status": "Active"
  43. },
  44. {
  45. "ClientID": "12002",
  46. "Name": "Mohan",
  47. "Service": "Software",
  48. "ServicePeriod": "3 years",
  49. "Status": "Active"
  50. },
  51. {
  52. "ClientID": "12003",
  53. "Name": "Sam",
  54. "Service": "Software",
  55. "ServicePeriod": "1 year",
  56. "Status": "Active"
  57. },
  58. {
  59. "ClientID": "12004",
  60. "Name": "Kevin",
  61. "Service": "Hardware",
  62. "ServicePeriod": "1 year",
  63. "Status": "InActive"
  64. },
  65. {
  66. "ClientID": "12005",
  67. "Name": "Ajay",
  68. "Service": "Software",
  69. "ServicePeriod": "3 years",
  70. "Status": "InActive"
  71. }]
  72. }
It has two separate details - ClientPersonal & ClientOfficial.

I want to show both the details in separate Grid views.

Thus, the function is given below to convert JSON to dataset.
  1. protected DataSet JSONToDataSet(string jsonString) {
  2. //Replace new line characters in JSON and also escaping ',' inside JSON data
  3. jsonString = jsonString.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(",", "~").Replace("}~{", "},{").Replace("\"~\"", "\",\"").Replace("]~", "],");
  4. DataSet ds = new DataSet();
  6. //Splitting of JSON datas
  7. string[] maintables = Regex.Split(jsonString, "}],");
  9. //Looping through Main tables
  10. for (int z = 0; z < maintables.Length; z++) {
  11. string mainspl = maintables[z];
  12. DataTable dt = new DataTable();
  13. string[] jsonParts = Regex.Split(mainspl.Replace("[", "").Replace("]", ""), "},{");
  14. List < string > dtColumns = new List < string > ();
  15. foreach(string jp in jsonParts) {
  16. string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
  17. foreach(string rowData in propData) {
  18. try {
  19. string[] spliting = rowData.Split(':');
  20. int idx = 0;
  21. string n;
  22. if (spliting.Length > 2) {
  23. dt.TableName = spliting[0].Replace("\"", "");
  24. n = spliting[spliting.Length - 2];
  25. } else {
  26. idx = rowData.IndexOf(":");
  27. n = rowData.Substring(0, idx - 1);
  28. }
  29. if (!dtColumns.Contains(n)) {
  30. dtColumns.Add(n.Replace("\"", ""));
  31. }
  32. } catch (Exception ex) {
  33. throw new Exception(string.Format("Sorry! There were some errors in proccessing the column : {0}", rowData));
  34. }
  35. }
  36. break;
  37. }
  38. //Adding Header for Datatables
  39. foreach(string c in dtColumns) {
  40. dt.Columns.Add(c);
  41. }
  43. //Binding Column Values
  44. foreach(string jp in jsonParts) {
  45. string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
  46. DataRow nr = dt.NewRow();
  47. int lopcnt = 0;
  48. foreach(string rowData in propData) {
  49. try {
  50. string[] spliting = rowData.Split(':');
  51. int idx = 0;
  52. string v;
  53. if (spliting.Length > 2) {
  54. v = spliting[spliting.Length - 1].Replace("\"", "").Replace("~", ",");
  55. } else {
  56. idx = rowData.IndexOf(":");
  57. v = rowData.Substring(idx + 1).Replace("\"", "").Replace("~", ",");
  58. }
  59. nr[lopcnt] = v;
  60. lopcnt++;
  61. } catch (Exception ex) {
  62. continue;
  63. }
  64. }
  65. dt.Rows.Add(nr);
  66. }
  67. if (dt.TableName == "") {
  68. dt.TableName = "Table" + z;
  69. }
  70. ds.Tables.Add(dt);
  71. }
  72. return ds;
  73. }
Our HTML Markup is given below-
  1. <body>
  2. <form id="form1" runat="server">
  3. <div>
  4. <b>Client Personal</b>
  5. <br />
  6. <asp:GridView ID="grdpersonal" runat="server" AutoGenerateColumns="true" OnRowDataBound="grdperrowdatabound" Width="50%">
  7. </asp:GridView>
  8. <br /><br />
  9. <b>Client Official</b>
  10. <br />
  11. <asp:GridView ID="grdofficial" runat="server" AutoGenerateColumns="true" OnRowDataBound="grdofcrowdatabound" Width="50%">
  12. </asp:GridView>
  13. </div>
  14. </form>
  15. </body>
Now, we call JSON to the dataset function. Keep the result in a dataset and bind it to Gridviews in Page_Load.
  1. protected void Page_Load(object sender, EventArgs e) {
  2. if (!IsPostBack) {
  3. string jsonResult;
  5. //Reading JSON data to string
  6. using(StreamReader r = new StreamReader(Server.MapPath("MyJsondata.json"))) {
  7. jsonResult = r.ReadToEnd();
  8. }
  9. DataSet ds = JSONToDataSet(jsonResult);
  10. grdpersonal.DataSource = ds.Tables[0];
  11. grdpersonal.DataBind();
  12. grdofficial.DataSource = ds.Tables[1];
  13. grdofficial.DataBind();
  14. }
  15. }

Thus, our result is given below-

1