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) {
-
- jsonString = jsonString.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(",", "~").Replace("}~{", "},{").Replace("\"~\"", "\",\"").Replace("]~", "],");
- DataSet ds = new DataSet();
-
-
- string[] maintables = Regex.Split(jsonString, "}],");
-
-
- 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;
- }
-
- foreach(string c in dtColumns) {
- dt.Columns.Add(c);
- }
-
-
- 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;
-
-
- 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-