DataTable To JSON Array

Share |

In this post I will discuss about converting any data table in the JSON array. JSON can be very effectively used for Ajax operations since data handling becomes more easier in case of JSON. Here I will not discuss how to use JSON in Ajax. I will simply provide code for converting Data Table into JSON Array.
Remember to add System.Web.Script.Serialization and System.Globalization; namespaces
Let us assume a demo table as below (DOWNLOAD)
<br /> <br /> public static DataTable DT<br /> {<br /> get<br /> {<br /> //Write your DataBase Logic Here.<br /> DataTable dt1 = (DataTable)HttpContext.Current.Session["DT"];<br /> if (dt1 == null)<br /> {<br /> dt1 = new DataTable();<br /> dt1.TableName = "GENERAL";<br /> dt1.Columns.Add(new DataColumn("EmpId", typeof(string)));<br /> dt1.Columns.Add(new DataColumn("Name", typeof(string)));<br /> dt1.Columns.Add(new DataColumn("Designation", typeof(string)));<br /> dt1.Columns.Add(new DataColumn("Department", typeof(string)));<br /> dt1.Columns.Add(new DataColumn("Contry", typeof(string)));<br /> dt1.Rows.Add(new object[] { "0", "A", "Manager", "Mech", "India" });<br /> dt1.Rows.Add(new object[] { "1", "B", "Team Leader", "Mech", "India" });<br /> dt1.Rows.Add(new object[] { "2", "C", "Team Leader", "IT", "India" });<br /> dt1.Rows.Add(new object[] { "3", "D", "Team Leader", "Mech", "India" });<br /> dt1.Rows.Add(new object[] { "4", "E", "Sw Developer", "IT", "India" });<br /> dt1.Rows.Add(new object[] { "5", "F", "Sw Developer", "IT", "India" });<br /> dt1.Rows.Add(new object[] { "6", "G", "Sw Developer", "IT", "India" });<br /> dt1.Rows.Add(new object[] { "7", "H", "Sw Developer", "IT", "India" });<br /> dt1.Rows.Add(new object[] { "8", "I", "Sr. Sw Developer", "IT", "India" });<br /> dt1.Rows.Add(new object[] { "9", "J", "Sr. Sw Developer", "IT", "India" });<br /> dt1.Rows.Add(new object[] { "10", "K", "Sr. Sw Developer", "IT", "India" });<br /> dt1.Rows.Add(new object[] { "11", "L", "Sr. Sw Developer", "IT", "India" });<br /> HttpContext.Current.Session["DT"] = dt1;<br /> }<br /> <br /> return dt1;<br /> }<br /> set<br /> {<br /> //Write your DataBase Logic Here.<br /> HttpContext.Current.Session["DT"] = value;<br /> }<br /> <br /> }<br /> <br />
For this table JSON output will be as below
<br /> <br /> { "Rows" :[{"EmpId":"0","Name":"A","Designation":"Manager","Department":"Mech","Contry":"India"},{"EmpId":"1","Name":"B","Designation":"Team Leader","Department":"Mech","Contry":"India"},{"EmpId":"2","Name":"C","Designation":"Team Leader","Department":"IT","Contry":"India"},{"EmpId":"3","Name":"D","Designation":"Team Leader","Department":"Mech","Contry":"India"},{"EmpId":"4","Name":"E","Designation":"Sw Developer","Department":"IT","Contry":"India"},{"EmpId":"5","Name":"F","Designation":"Sw Developer","Department":"IT","Contry":"India"},{"EmpId":"6","Name":"G","Designation":"Sw Developer","Department":"IT","Contry":"India"},{"EmpId":"7","Name":"H","Designation":"Sw Developer","Department":"IT","Contry":"India"},{"EmpId":"8","Name":"I","Designation":"Sr. Sw Developer","Department":"IT","Contry":"India"},{"EmpId":"9","Name":"J","Designation":"Sr. Sw Developer","Department":"IT","Contry":"India"},{"EmpId":"10","Name":"K","Designation":"Sr. Sw Developer","Department":"IT","Contry":"India"},{"EmpId":"11","Name":"L","Designation":"Sr. Sw Developer","Department":"IT","Contry":"India"}]} <br /> <br />
Add following functions to build JSON string from an object
<br /> <br /> // <summary><br /> /// Quotes the string.<br /> /// </summary><br /> /// <param name="s">The s.</param><br /> <br /> /// <param name="sb">The sb.</param><br /> void QuoteString(string s, StringBuilder sb)<br /> {<br /> QuoteString(s, '"', sb);<br /> }<br /> /// <summary><br /> /// Quotes the string.<br /> /// </summary><br /> /// <param name="s">The s.</param><br /> /// <param name="quoteChar">The quote char.</param><br /> /// <param name="sb">The sb.</param><br /> void QuoteString(string s, char quoteChar, StringBuilder sb)<br /> {<br /> if (s == null || (s.Length == 1 && s[0] == '\0'))<br /> {<br /> sb.Append(new String(quoteChar, 2));<br /> return;<br /> }<br /> <br /> char c;<br /> int len = s.Length;<br /> <br /> sb.EnsureCapacity(sb.Length + s.Length + 2);<br /> <br /> sb.Append(quoteChar);<br /> <br /> for (int i = 0; i < len; i++) { c = s[i]; switch (c) { case '\\': sb.Append("\\\\"); break; case '\b': sb.Append("\\b"); break; case '\t': sb.Append("\\t"); break; case '\r': sb.Append("\\r"); break; case '\n': sb.Append("\\n"); break; case '\f': sb.Append("\\f"); break; default: if (c < ' ') { sb.Append("\\u"); sb.Append(((int)c).ToString("x4", CultureInfo.InvariantCulture)); } else if (c == quoteChar) { sb.Append("\\"); sb.Append(c); } else { sb.Append(c); } break; } } sb.Append(quoteChar); }
Above functions will be responsible to convert an object into JSON variable, now we will write one function which will break the table and call above functions, which in turn update the string builder.
<br /> public void DTSerialize(object o, StringBuilder sb)<br /> {<br /> DataTable dt = o as DataTable;<br /> <br /> if (dt == null)<br /> throw new NotSupportedException();<br /> <br /> DataColumnCollection cols = dt.Columns;<br /> DataRowCollection rows = dt.Rows;<br /> <br /> bool b = true;<br /> <br /> sb.Append('[');<br /> <br /> foreach (DataRow row in rows)<br /> {<br /> if (b) { b = false; }<br /> else { sb.Append(","); }<br /> <br /> sb.Append('{');<br /> <br /> bool bc = true;<br /> <br /> foreach (DataColumn col in dt.Columns)<br /> {<br /> if (bc) { bc = false; }<br /> else { sb.Append(","); }<br /> <br /> QuoteString(col.ColumnName, sb);<br /> sb.Append(':');<br /> JavaScriptSerializer JS = new JavaScriptSerializer();<br /> JS.Serialize(row[col], sb);<br /> }<br /> <br /> sb.Append('}');<br /> }<br /> <br /> sb.Append(']');<br /> }<br />
In page load we will simply call following code, this will convert table into JSON string and write it on Page.
<br /> protected void Page_Load(object sender, EventArgs e)<br /> {<br /> <br /> StringBuilder sb = new StringBuilder();<br /> DTSerialize(DT, sb);<br /> sb.Insert(0, " { \"Rows\" :", 1);<br /> sb.Insert(sb.Length, "}", 1);<br /> string jsStr = "var Table = " + sb.ToString();<br /> Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "", jsStr, true);<br /> Response.Write(sb.ToString());<br /> <br /> <br /> }<br />
In above code I have registered JAVA script block as well, this just for demo purpose; if we put following code we will get an alert with first employee ID.
<br /> <script language="javascript"> alert(Table.Rows[0].EmpId); </script><br />
Download Demo Code Here --> DataTableToJSONArray.zip