Dataset, Datatable to Json

After my previous posts about returning data to the client as a JSON object, I decided to have a go at returning a generic Datatable/Dataset. This however is not as easy as simple returning a Datatable in your code behind method or web service. There is a solution though and here it is.

If you break a Datatable down it is really only a List of Dictionary objects so that’s how we’ll approach this problem. This is compatible with .NET 2.0 and above, with the Ajax installed.

Below is the solution.

using System.Collections.Generic; using System.Data; public static class JsonMethods { private static List<Dictionary<string, object>> RowsToDictionary(DataTable table) { List<Dictionary<string, object>> objs = new List<Dictionary<string, object>>(); foreach (DataRow dr in table.Rows) { Dictionary<string, object> drow = new Dictionary<string, object>(); for (int i = 0; i < table.Columns.Count; i++) { drow.Add(table.Columns[i].ColumnName, dr[i]); } objs.Add(drow); } return objs; } public static Dictionary<string, object> ToJson(DataTable table) { Dictionary<string, object> d = new Dictionary<string, object>(); d.Add(table.TableName, RowsToDictionary(table)); return d; } public static Dictionary<string, object> ToJson(DataSet data) { Dictionary<string, object> d = new Dictionary<string, object>(); foreach (DataTable table in data.Tables) { d.Add(table.TableName, RowsToDictionary(table)); } return d; } }

The static class JsonMethods exposes two public static methods and a private method. The public method ToJson() takes either a Dataset or a Datatable, and returns a Dictionary<string,object> object. The key to this class is the RowsToDictionary() method.

This method iterates through all the rows creating a dictionary entry for each column in the row using the column name as the key and storing the data value into the object. It then adds the Dictionary object to a List of Dictionary Objects and returns this to the ToJson() method. This Dictionary list is then added to another Dictionary object using the table name as the key. We’ll see how this all works together soon.

Lets have a look at the code behind now.

[System.Web.Script.Services.ScriptMethod(ResponseFormat = ResponseFormat.Json)] [System.Web.Services.WebMethod] public static Dictionary<string, object> getTable() { string sql = "select user_name, active_indicator, create_date from users"; string connString = "database=db; server=localhost; user id=sa;"; return JsonMethods.ToJson(GetDataTable(sql, connString)); } private static DataTable GetDataTable(string sql, string connString) { using (SqlConnection myConnection = new SqlConnection(connString)) { using (SqlCommand myCommand = new SqlCommand(sql, myConnection)) { myConnection.Open(); using (SqlDataReader myReader = myCommand.ExecuteReader()) { DataTable myTable = new DataTable(); myTable.TableName = "mydt"; myTable.Load(myReader); myConnection.Close(); return myTable; } } } }

So what I have above is two static methods. One is GetTable which is the one we will access from the client. The other is a generic method for loading a results set into a Datatable. Note how I have set the TableName property. You will see why soon.

So using the jMsAjax plugin as below will return the following JSON object.

$.jmsajax({ type: "POST", url: "Default.aspx", method: "getTable", data: {}, dataType: "msjson", success: function(data) { $(outputDT(data.mydt)).appendTo("body"); } });

Results (data):

{“mydt”:{“user_name”:”000001″,”active_indicator”:”Y”,”create_date”:”/Date(1170892765197)/”}, {“user_name”:”000002″,”active_indicator”:”Y”,”create_date”:”/Date(1170892765197)/”}]}

In the resulting data, the table name is the key to referencing the array of values. In this case we use ‘mydt’ as the key. In the success function on the client request you may also notice an outputDT function. This is a little helper function which takes a JSON Datatable and returns a the results in a table. This is very useful for debugging. Here is the client side code.

function outputDT(dataTable) { var headers = []; var rows = []; headers.push("<tr>"); for (var name in dataTable[0]) headers.push("<td><b>"+name+"</b></td>"); headers.push("</tr>"); for (var row in dataTable) { rows.push("<tr>"); for (var name in dataTable[row]) { rows.push("<td>"); rows.push(dataTable[row][name]); rows.push("</td>"); } rows.push("</tr>"); } var top = "<table border='1'>"; var bottom = "</table>"; return top + headers.join("") + rows.join("") + bottom; }

So as you can see, its now very easy to return a Datatable or Dataset as a JSON object ready for use on the client.

Hope this is as useful for you as it is for me.

Cheers,

Schotime