Importing JSON File To DataSet Without Using External Libraries

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-

{
    "ClientPersonal": [{
        "ClientID": "12001",
        "Name": "Ram",
        "DOB": "20-Aug-1980",
        "Mobile": "9999999999",
        "Email": "[email protected]"
    },
    {
        "ClientID": "12002",
        "Name": "Mohan",
        "DOB": "15-Jan-1989",
        "Mobile": "9999988888",
        "Email": "[email protected]"
    },
    {
        "ClientID": "12003",
        "Name": "Sam",
        "DOB": "11-Apr-1985",
        "Mobile": "8888888888",
        "Email": "[email protected]"
    },
    {
        "ClientID": "12004",
        "Name": "Kevin",
        "DOB": "20-Jun-1992",
        "Mobile": "7777788888",
        "Email": "[email protected]"
    },
    {
        "ClientID": "12005",
        "Name": "Ajay",
        "DOB": "22-Sep-1978",
        "Mobile": "8888877777",
        "Email": "[email protected]"
    }],
    "ClientOfficial": [{
        "ClientID": "12001",
        "Name": "Ram",
        "Service": "Hardware",
        "ServicePeriod": "2 years",
        "Status": "Active"
    },
    {
        "ClientID": "12002",
        "Name": "Mohan",
        "Service": "Software",
        "ServicePeriod": "3 years",
        "Status": "Active"
    },
    {
        "ClientID": "12003",
        "Name": "Sam",
        "Service": "Software",
        "ServicePeriod": "1 year",
        "Status": "Active"
    },
    {
        "ClientID": "12004",
        "Name": "Kevin",
        "Service": "Hardware",
        "ServicePeriod": "1 year",
        "Status": "InActive"
    },
    {
        "ClientID": "12005",
        "Name": "Ajay",
        "Service": "Software",
        "ServicePeriod": "3 years",
        "Status": "InActive"
    }]
}

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.

protected DataSet JSONToDataSet(string jsonString) {
 //Replace new line characters in JSON and also escaping ',' inside JSON data
 jsonString = jsonString.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(",", "~").Replace("}~{", "},{").Replace("\"~\"", "\",\"").Replace("]~", "],");
 DataSet ds = new DataSet();

 //Splitting of JSON datas
 string[] maintables = Regex.Split(jsonString, "}],");

 //Looping through Main tables
 for (int z = 0; z < maintables.Length; z++) {
  string mainspl = maintables[z];
  DataTable dt = new DataTable();
  string[] jsonParts = Regex.Split(mainspl.Replace("[", "").Replace("]", ""), "},{");
  List < string > dtColumns = new List < string > ();
  foreach(string jp in jsonParts) {
    string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
    foreach(string rowData in propData) {
     try {
      string[] spliting = rowData.Split(':');
      int idx = 0;
      string n;
      if (spliting.Length > 2) {
       dt.TableName = spliting[0].Replace("\"", "");
       n = spliting[spliting.Length - 2];
      } else {
       idx = rowData.IndexOf(":");
       n = rowData.Substring(0, idx - 1);
      }
      if (!dtColumns.Contains(n)) {
       dtColumns.Add(n.Replace("\"", ""));
      }
     } catch (Exception ex) {
      throw new Exception(string.Format("Sorry! There were some errors in proccessing the column : {0}", rowData));
     }
    }
    break;
   }
   //Adding Header for Datatables
  foreach(string c in dtColumns) {
   dt.Columns.Add(c);
  }

  //Binding Column Values
  foreach(string jp in jsonParts) {
   string[] propData = Regex.Split(jp.Replace("{", "").Replace("}", ""), ",");
   DataRow nr = dt.NewRow();
   int lopcnt = 0;
   foreach(string rowData in propData) {
    try {
     string[] spliting = rowData.Split(':');
     int idx = 0;
     string v;
     if (spliting.Length > 2) {
      v = spliting[spliting.Length - 1].Replace("\"", "").Replace("~", ",");
     } else {
      idx = rowData.IndexOf(":");
      v = rowData.Substring(idx + 1).Replace("\"", "").Replace("~", ",");
     }
     nr[lopcnt] = v;
     lopcnt++;
    } catch (Exception ex) {
     continue;
    }
   }
   dt.Rows.Add(nr);
  }
  if (dt.TableName == "") {
   dt.TableName = "Table" + z;
  }
  ds.Tables.Add(dt);
 }
 return ds;
}

Our HTML Markup is given below-

<body>
    <form id="form1" runat="server">
        <div>
            <b>Client Personal</b>
            <br />
            <asp:GridView ID="grdpersonal" runat="server" AutoGenerateColumns="true" OnRowDataBound="grdperrowdatabound" Width="50%">
            </asp:GridView>
            <br /><br />
            <b>Client Official</b>
            <br />
            <asp:GridView ID="grdofficial" runat="server" AutoGenerateColumns="true" OnRowDataBound="grdofcrowdatabound" Width="50%">
            </asp:GridView>
        </div>
    </form>
</body>

Now, we call JSON to the dataset function. Keep the result in a dataset and bind it to Gridviews in Page_Load.

protected void Page_Load(object sender, EventArgs e) {
 if (!IsPostBack) {
  string jsonResult;

  //Reading JSON data to string
  using(StreamReader r = new StreamReader(Server.MapPath("MyJsondata.json"))) {
   jsonResult = r.ReadToEnd();
  }
  DataSet ds = JSONToDataSet(jsonResult);
  grdpersonal.DataSource = ds.Tables[0];
  grdpersonal.DataBind();
  grdofficial.DataSource = ds.Tables[1];
  grdofficial.DataBind();
 }
}

Thus, our result is given below-