Generating Dynamic HTML Tables with jQuery From an In-Memory DataTable

(sourced from: http://www.codecapers.com/post/generating-dynamic-html-tables-with.aspx)

By nature, web applications are stateless so you have to work a little harder to make them produce an user experience equivalent to that of a traditional windows application.
In this article, I will go over the process for how to load an HTML table to a web page using Ajax and jQuery. The process will require you to execute an asynchronous query in SQL Server, load it into a DataTable and return it to the browser as JSON (JavaScript Object Notation). Finally the JSON string can be consumed by jQuery and rendered as an HTML table on the browser.
Lets start out by looking at this traditional snippet of ADO.NET code:

System.Data.DataTable tbl = new System.Data.DataTable();

try {
    using (SqlConnection conn = new SqlConnection(connectString)) {
        SqlDataAdapter da = new SqlDataAdapter(sql, conn);                
        da.Fill(tbl);                                         
     }
}
catch {
    //do something
}

The code simply loads a query result into a DataTable. This is something that most .NET developers have probably done a million times before. Therefore I will just move forward onto the next step which is serializing the DataTable as JSON.
After a bit of experimenting, I determined that a collection of Dictionary objects would be a perfect candidate for serializing the DataTable as a JavaScript object. I originally tried just converting the DataTable by calling Json(tbl). However, the conversion was not sufficient for my needs. By using a collection of Dictionary objects, each row in the DataTable would map to a Dictionary object. Here is the code that I ended up with:

using System.Web.Script.Serialization;
...
public static string GetJson(DataTable table) {
    JavaScriptSerializer jss = new JavaScriptSerializer();
    Liststring

, object>> rows = new Liststring, object>>();
Dictionary<string, object> row;

foreach (DataRow dr in table.Rows) {
row = new Dictionary<string, object>();
foreach( DataColumn col in table.Columns ) {
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
return jss.Serialize(rows);
} To clarify the code, lets consider the following table:

ID	NAME
1	Foo
2	Bar

This table would produce a list with two objects. The row with the ID of 1 would be represented as a dictionary object that has a Key equal to 1 and a Value equal to "Foo". The row with the ID of 2 would be represented as a dictionary object that has a Key equal to 2 and a Value equal to "Bar". When converted to a JSON string the entire table would look something like this:
[ { "ID" = 1, "NAME" = "Foo" }, { "ID" = 2, "NAME" = "Bar" } ]
For those of you who can interpret JSON, this is simply an array with two objects.
So now I have two parts of the puzzle completed. First, I executed the query and populated a DataTable. Secondly, I serialized the DataTable into JSON. Now I need to add the code to my webpage to make the asynchronous JavaScript call and format the results as an HTML table. Luckily for us, jQuery makes AJAX calls simple by using the post method:

   1:  $.post("/Utils/GetData", 

   2:      { orderBy: ord, keyword: key },

   3:      function(data) {

   4:         buildTable(data);

   5:      }

   6:  );

The first parameter in the post method is the URL of the method we are invoking on the web server. The second part consists of the parameters you are passing to the method. In my case, the method took 2 strings, one named orderBy and another named keyword. The variables ord and key were initialized earlier in the code by reading the values of two textboxes on the page. On line 3, we have the callback function that is invoked when the results are returned from the web server. The variable named data, is the JSON representation of the DataTable we created using the GetJson method. The only thing left to do now, is render the JSON results as a table.
To render the dynamic table I started by adding a placeholder to my web page which will hold the results:
  
Since there are no rows initially in my table it does not appear on the page. However, if you wanted to hide it you could easily do so by calling $("#grid").hide() or setting the initial style to display:none. And finally, here is the javascript that converts the serialized DataTable into HTML:

function buildTable(tableData) {    
    var table = $("#grid");
    table.html("");  //clear out the table if it was previously populated
    eval("var data = " + tableData);  //load the data variable as an object array

    table.append('

');
    var thead = $('thead tr', table);                                        
    
    //create the table headers
    for (var propertyName in $(data)[0]) {                
        thead.append('' + propertyName + '

');
}

//add the table rows
$(data).each(function(key, val) {
table.append(' ');
var tr = $('tr:last', table);
for (var propertyName in val) {
tr.append('
'
+ val[propertyName] + '');
}
});
} In my application, I used this code to create a web page that allows a user to constantly modify the parameters for a query and dynamically update the results of a table without refreshing the page. jQuery and Ajax really make the web application feel "state-ful" Not to mention, it drastically improves the user experience within the application.