Post
Ask Question

Bind Auto Columns HTML Table Using jQuery/JSON

In my earlier articles, I have already explained how to bind an HTML table in jQuery with AJAX call using different methods. This article will explain how to bind the HTML table using JSON data but with the best method in which you do not require to append the table by specifying the column names. Just call the header function and pass the response in a list as a parameter that converts the list items into the table columns and appends it as a table header to the table using the For loop.
Here is our table structure. In this, we have given different ids to both, the table and the table header. We do this just because our table header is appended in different functions and body is in a different function. Let's see this in details.
  1. <table id="jsonTable" border="1" style="border-collapse: collapse;" cellpadding="5">  
  2.    <thead id="av">  
  3.     </thead>  
  4. </table>  
After the table structure, add the bootstrap and jQuery files. Then, let us call the tablebind function in the document so as to bind the table at the time of page load.
  1. <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script>    
  2. <link rel="stylesheet" href="http://netdna.bootstrapcdn.com/bootstrap/3.0.3/css/bootstrap.min.css" />  
  3.   
  4.  $(document).ready(function () {  
  5.           tablebind();  
  6. });    
Now, using an AJAX call, we can call the web method to bind the table. In my last article, I have already explained all the AJAX calling parameters so you can refer to them. In this procedure,  what we do is to call a web method named bind, as given below, and convert the response from the JSON string to JavaScript object using ParseJSON. Then, by passing the data as a parameter, call the Columnheader function which returns the column header and using a Nested For Loop, we append each row in a row$ variable up to the number of records in the data and to the number of columns in a list the For Loop is iterated. After the loop work is done, we need to append the tr$ variable to the table using the id selector.    
  1. function tablebind() {  
  2.                     $.ajax({  
  3.                         type: "POST",  
  4.                         contentType: "application/json; charset=utf-8",  
  5.                         url: "ShipTo.aspx/binds",  
  6.                         data: "{}",  
  7.                         dataType: "json",  
  8.                         success: function (response) {  
  9.                             var obj = $.parseJSON(response.d);  
  10.                             if (obj.length > 0) {  
  11.   
  12.                                 var data = obj[0].Table1;  
  13.                                 var table = $("<table />");  
  14.                                 table[0].border = "1";  
  15.   
  16.                                 var row$;  
  17.   
  18.                                 var columns = addAllColumnHeaders(data);  
  19.                                 for (var i = 0; i < data.length; i++) {  
  20.                                     row$ = $('<tr/>');  
  21.                              
  22.                                     for (var colIndex = 0; colIndex < columns.length; colIndex++) {  
  23.                                         var cellValue = data[i][columns[colIndex]];  
  24.   
  25.                                         if (cellValue == null) { cellValue = ""; }  
  26.   
  27.                                         row$.append($('<td/>').html(cellValue));  
  28.                                     }  
  29.                                     $("#jsonTable").append(row$);  
  30.                                 }  
  31.                                  
  32.                             }  
  33.   
  34.                         },  
  35.                         error: function (response) {  
  36.                             //                      
  37.                         }  
  38.                     });  
  39.   
  40.                 }  
Our result is already appended to the table above. Now, this function is used to make the table header. This is similar to the above method where we iterated the For Loop to make each <tr>. Now, in this, we need to push each column to the columnset. This method returns the list of the columns to the previous function and appends each <th> to the headertr$ to form the table header.
  1. function addAllColumnHeaders(myList) {  
  2.             var columnSet = [];  
  3.             var headerTr$ = $('<tr/>');
  4.             for (var i = 0; i < myList.length; i++) {  
  5.                 var rowHash = myList[i];  
  6.                 for (var key in rowHash) {  
  7.                     if ($.inArray(key, columnSet) == -1) {  

  9.                             columnSet.push(key);  
  10.                             headerTr$.append($('<th/>').html(key));  
  11.  
  12.                     }  
  13.                 }  
  14.             }  
  15.             $("#av").append(headerTr$);  
  16.   
  17.             return columnSet;  
  18.         }  
There may be a condition that you don't want any column in the HTML table but the query is returning the column. In that case, you can pass the if condition with the column name in the addAllColumnsHeader function. See an example below.
  1. function addAllColumnHeaders(myList) {  
  2.                    var columnSet = [];  
  3.                    var headerTr$ = $('<tr/>');
  4.                    for (var i = 0; i < myList.length; i++) {  
  5.                        var rowHash = myList[i];  
  6.                        for (var key in rowHash) {  
  7.                            if ($.inArray(key, columnSet) == -1) {  
  8.                                if (key != "ShipToId" && key != "GCRETEDON" key != "userid") {  
  9.                                    columnSet.push(key);  
  10.                                    headerTr$.append($('<th/>').html(key));  
  11.                                }  
  12.                            }  
  13.                        }  
  14.                    }  
  15.                    $("#av").append(headerTr$);  
  16.   
  17.                    return columnSet;  
  18.                }  
Similarly, you might want to add an extra column to the HTML table, like edit and delete. For this, you have to change both the functions. Let's look at the changes in addAllColumnsHeader function first.
  1. function addAllColumnHeaders(myList) {  
  2.                     var columnSet = [];  
  3.                     if (myList.length > 0) {  
  4.                         var headerTr$ = $('<tr/>');  
  5.                         headerTr$.append($('<th/>').html('Action'));  
  6.                     }  
  7.                     for (var i = 0; i < myList.length; i++) {  
  8.                         var rowHash = myList[i];  
  9.                         for (var key in rowHash) {  
  10.                             if ($.inArray(key, columnSet) == -1) {  
  11.                                 if (key != "ShipToId" && key != "GCRETEDON"  && key != "userid") {  
  12.                                     columnSet.push(key);  
  13.                                     headerTr$.append($('<th/>').html(key));  
  14.                                 }  
  15.                             }  
  16.                         }  
  17.                     }  
  18.                     $("#av").append(headerTr$);  
  19.   
  20.                     return columnSet;  
  21.                 }  
Now, let's see the bind function's code.
  1. function tablebind() {  
  2.                    $.ajax({  
  3.                        type: "POST",  
  4.                        contentType: "application/json; charset=utf-8",  
  5.                        url: "ShipTo.aspx/binds",  
  6.                        data: "",  
  7.                        dataType: "json",  
  8.                        success: function (response) {  
  9.                            var obj = $.parseJSON(response.d);  
  10.                            if (obj.length > 0) {  
  11.   
  12.                                var data = obj[0].Table1;  
  13.                                var table = $("<table />");  
  14.                                table[0].border = "1";  
  15.   
  16.                                var row$;  
  17.   
  18.                                var columns = addAllColumnHeaders(data);  
  19.                                for (var i = 0; i < data.length; i++) {  
  20.                                    row$ = $('<tr/>');  
  21.                                    row$.append($('<td/>').html('<img src="../images/edit_icon.jpg" id="' + data[i]['ShipToId'] + '" onclick="edt(this)"><img src="../images/Delete1.png" id="' + data[i]['ShipToId'] + '" onclick="dlt(this)"><img src="../images/view.png" id="' + data[i]['ShipToId'] + '" onclick="pop(this)">'));  
  22.                                    for (var colIndex = 0; colIndex < columns.length; colIndex++) {  
  23.                                        var cellValue = data[i][columns[colIndex]];  
  24.   
  25.                                        if (cellValue == null) { cellValue = ""; }  
  26.   
  27.                                        row$.append($('<td/>').html(cellValue));  
  28.                                    }  
  29.                                    $("#jsonTable").append(row$);  
  30.                                }  
  31.                                sort();  
  32.                            }  
  33.   
  34.                        },  
  35.                        error: function (response) {  
  36.                            //                      
  37.                        }  
  38.                    });  
  39.   
  40.                }  
In the above function, we added the row with two icons - edit and delete - which call their respective functions. Here, I am not explaining the edit and delete functions for those are explained in my earlier article. Now, our last step would be the web method that we call from our AJAX method.  
  1. [System.Web.Services.WebMethod(EnableSession = true), ScriptMethod(ResponseFormat = ResponseFormat.Json)]    
  2.     public static string binds()    
  3.     {     
  4.         List<Dictionary<string, Object>> tables = new List<Dictionary<string, object>>();    
  5.         //to create tables with table name and their rows.    
  6.         List<Dictionary<string, Object>> rows = null;    
  7.         //to hold single table with rows    
  8.         Dictionary<string, Object> tab = new Dictionary<string, object>();    
  9.         // to hold single row of each table.    
  10.         Dictionary<string, Object> row = null;    
  11.             
  12.         System.Web.Script.Serialization.JavaScriptSerializer serializer = new System.Web.Script.Serialization.JavaScriptSerializer();    
  13.         SqlConnection con = null;    
  14.         con = (SqlConnection)HttpContext.Current.Session["conn"];    
  15.         string g3 = "select ShipToId,category_code [Category Code],category_name [Category Name],GCRETEDON,userid,status from category";    
  16.     
  17.         SqlCommand cmdgla = new SqlCommand(g3, con);    
  18.         cmdgla.Connection = con;    
  19.     
  20.         SqlDataAdapter adpgla = new SqlDataAdapter(cmdgla);    
  21.         DataTable ndt = new DataTable();    
  22.         adpgla.Fill(ndt);    
  23.         DataTable dtadd = new DataTable();    
  24.         
  25.         DataRow dr1;    
  26.         for (int k = 0; k < ndt.Rows.Count; k++)    
  27.         {    
  28.     
  29.             dr1 = dtadd.NewRow();    
  30.             dtadd.Rows.Add(dr1);    
  31.     
  32.         }    
  33.         DataSet ds = new DataSet();    
  34.         ds.Tables.Add(ndt);    
  35.         foreach (DataTable dt in ds.Tables)    
  36.         {    
  37.             rows = new List<Dictionary<string, object>>();    
  38.     
  39.     
  40.             foreach (DataRow dr in ndt.Rows)    
  41.             {    
  42.                 row = new Dictionary<string, object>();    
  43.                 foreach (DataColumn dc in ndt.Columns)    
  44.                 {    
  45.                     row.Add(dc.ColumnName.Trim(), dr[dc]);    
  46.                 }    
  47.                 rows.Add(row);    
  48.             }    
  49.             tab.Add(ndt.TableName.Trim(), rows);    
  50.     
  51.         }    
  52.         tables.Add(tab);    
  53.         return serializer.Serialize(tables);    
  54.     }    
Here is the final output with only edit action.
 
Bind Auto Columns HTML Table 
 
You can see how easy it is for you to bind an HTML table without specifying the table header row and the data rows. This can be a very helpful article for you to start working with JSON data using AJAX call. Also, you can follow the process mentioned above when you are having a bulk data and need to bind the HTML table. According to your requirement, you can edit these functions simply.


Similar Articles