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

16 Responses to Dataset, Datatable to Json

  1. [...] Click here to view the simple solution. Posted: Jul 28 2008, 10:05 PM by schotime | with no comments [...]

  2. Tomas says:

    This is brilliant. Thank you. Exactly what I was googling for.

  3. Tomas says:

    Schotime, I copied your code letter for letter. I am returning a DataTable with 2 columns (both strings). I’m getting the following error message in an alert box. Maybe you can point me in the right direction.

    Status: Internal Server Error
    Message: Cannot convert object of type ‘System.String’ to type ‘System.Collections.Generic.IDictionary`2[System.String,System.Object]‘

  4. Schotime says:

    @Tomas

    Could you please paste the exact code you are using, and I will try and figure something out for you.

    Cheers.
    Schotime

  5. Nelson says:

    Great work!

    I have a question can we do de inverse

    Dictionary to datatable, exemaple

    public static DataTable ToJson(Dictionary)

    Cheers
    Nelson

  6. Achutha Krishnan says:

    Hai Tomas,

    That was really good one. Now I’ve the json string with me. I need to bind this string to the GridView. I mean, dynamically create a table. My output is as follow:
    {“”:[{"ID":"1","Items":"Book"},{"ID":"2","Items":"Ball"},{"ID":"1","Items":"Mobile"},{"ID":"3","Items":"Paper"}]}

    How will I loop through and dynamically create the table? Can you let me know, please?

  7. Mike says:

    Great, thanks! I’m using this now (simplified) and it’s working very well.

    Whatever happened to the serializers that Microsoft wasmaking in the Ajax Futures project?

  8. this is very excellent and knowledgeable. i helpful for people.

  9. Function says:

    Schotime, I copied your code and it alert “jQuery is not defined”,could you please give me you mail address, and I write to you?Thanks!

  10. Excellent post. Thanks!

  11. AneeshAbe says:

    Hi Scot,
    Your article is really helpful with easy to understand.But when I tried to implement in my webservice I got this error:

    The type System.Collections.Generic.Dictionary`2[[System.String, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089],[System.Object, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]] is not supported because it implements IDictionary.

    - Any clues

  12. Don says:

    Hi,
    Thanks for your post.
    I am having trouble reading the object created.

    outputDT(DataTable)
    returning error looking for object. Why?

    I can see in debugging the all table is retrning as needed.

  13. mg says:

    What about the case where you have a stored proc returning multiple result sets, so you have to deal with multiple DataTables?

  14. sathiya says:

    Schotime,
    what a great stuff..Thank you so much

  15. Prasad says:

    I am getting the error “No conversion from text to msjson”

    How can i resolve this?

  16. [...] .Net 3.5. I figured this must have been done before, so I went looking online and found a number of different options. Some of them depend on an additional library, which I would have a hard time pushing [...]

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>