Introduction

JQuery DataTables plugin is an excellent client-side component that can be used to create rich-functional tables in the web browser. This plugin adds lot of functionalities to the plain HTML tables that are placed in web pages such as filtering, paging, sorting, changing page length etc. By default it works with client side rows that are rendered in table (in <tbody> </tbody> section in the HTML code of the table) but it might be configured to use data from the server via Ajax calls. However, to integrate DataTables with server-side code developer must know protocols and parameters that are sent by DataTables and how to use them on server side.

This article shows how JQuery DataTables plugin can be integrated into ASP.NET MVC application. It contains step by step examples that show how DataTables plugin interacts with server-side components. 

Background  

Currently there are several components that can help developers to create effective and functional-rich tables on the web pages. Some of them are server-side components such as standard ASP.NET GridView, or other similar components that generate HTML code and attach events which post-back request to server and where user actions are handled by the sever code and new table is generated. The other group of components, such as JQuery DataTables, FlexGrid or jqGrid, are implemented as a client-side code.
JQuery DataTables is a powerful JavaScript plugin that automatically adds several functionalities such as dropdown for selecting how much items should be shown per page, text box for filtering results, sorting functionality attached to table headers and pagination on the bottom of the table. No server-side code is required for this as all functionalities are implemented as set of JavaScript handlers that use data found in HTML. Following figure shows how a plain HTML table looks after applying DataTables plugin:  

JqueryDataTables.png

DataTables plugin adds “Show XXX entries” dropdown above the table enabling user to choose whether he wants to see 10, 25, 50 or 100 records per page and search text box that enables user to filter by keyword records that should be shown in the table. This plugin also adds sorting functionality that enables user to sort results by clicking on the column header. Below the table there is pagination that enables user to navigate through the pages and text that automatically displays which records are currently displayed. All these functionalities are added by default and all you need is a single line of code:    

$(document).ready(function() {
	$('#myTable').dataTable();
} );   
Most of these functionalities can be used completely separate from server-side code i.e. web server can generate plain HTML table in the standard format in any server-side technology such as ASP.NET Web Forms, ASP.NET MVC, PHP, Java etc. Client-side JavaScript components will use whatever gets generated and add client-side functionalities. In this client-side mode DataTables takes all table rows from <tbody></tbody> section and performs filtering, paging and sorting directly on these elements as on in-memory objects. This is the fastest way to use DataTables but it requires that server returns all data in a single call, loads all these rows as in-memory JavaScript objects, and render them dynamically in DOM. This might cause performance issues with server call and memory usage on the client. However, this minimizes number of requests sent to the server because once table is loaded server is not used at all.

On the other hand, it is possible to implement client-server interaction by configuring DataTables to query server via Ajax calls in order to fetch required data. In this case, plugin will call server side page, post information about required data, take the response from server and refresh the table. On the DataTables site there is an example of server-side configuration where JQuery DataTables plugin sends requests to the PHP page and gets data that should be shown in current view. Server response is formatted as JSON object, parsed on a client side and displayed in the table body. Following figure shows trace of the calls sent to server (captured using FireBug add-in for FireFox).   

JqueryAjaxRequests.png

In this case, each event (changing number of items that should be displayed per page, entering keyword in search filter, sorting, pressing pagination button etc) triggers DataTables plugin to send information about current page, search filter and sort column to the server page. As shown in the third request, server page returns JSON as a result and DataTables uses that data array when displaying current table page. In this mode, instead of taking complete page at once, several smaller requests are sent whenever new information is required and minimal amount of data is returned from the server. DataTables, in this example, calls server_processing.php page and sends information about user action. Full example of server-side configuration of JQuery DataTable plugin can be found here. Major problem with the server-side mode is implementation of server-side logic that accepts parameters from client-side component, performs action and returns data as expected.  
This article explains how to configure JQuery DataTables and implement server-side logic with ASP.NET MVC controllers.

Using the code  

The first thing you need to do is to create a standard ASP.NET Model-View-Controller structure. There are three steps required for this setup:
  1. Creating the model classes that represent data structure that will be shown
  2. Creating the controller class that will react on the user events 
  3. Creating the view that will render data and create HTML code that is sent to the browser window 
Simple application that keeps information about companies and displays them in a table will be used as an example. Then, this simple table will be enhanced with the JQuery DataTables plug-in and configured to take all necessary data from the server-side.
Following JavaScript components need to be downloaded:
  1. jQuery library containing standard classes used by DataTables plugin
  2. jQuery DataTables plugin including optional DataTables CSS style-sheets used for applying default styles on the page 
These files should be stored in the local file system and included in the HTML page that is rendered on the client. Example of usage of these files is explained below.

Model      

Model comes to a simple class containing company data. Fields that we need are company id, name, address and a town. Source code of company model class is shown below:
public class Company
{
    public int ID { get; set; }
    public string Name { get; set; }
    public string Address { get; set; }
    public string Town { get; set; }
}             

View  

Since data presentation is done on the client-side classic View page is fairly simple. It contains one simple HTML table “decorated” with JQuery DataTables plugin. Example:

@{
    Layout = null;
}

<!DOCTYPE html>
<html>    
    <head>   
        <title>jQuery DataTables/ASP.NET MVC Integration</title>
        <link href="~/Content/dataTables/demo_table.css" rel="stylesheet" type="text/css" />        
        <script src="~/Scripts/jQuery-1.4.4.min.js" type="text/javascript"></script>
        <script src="~/Scripts/jQuery.dataTables.min.js" type="text/javascript"></script>
        <script src="~/Scripts/index.js" type="text/javascript"></script>
    </head>
    <body>
        <div id="container">
            <div id="demo">
                <h2>Index</h2>
                <table id="myDataTable" class="display">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Company name</th>
                            <th>Address</th>
                            <th>Town</th>
                        </tr>
                    </thead>
                    <tbody> 
                    </tbody>
                </table>
            </div>
        </div>
    </body>
</html>
As the view engine is used Razor but any other view engine can be used instead, as only engine specific code is setting no layout page on the top of the page. Page includes all necessary JavaScript libraries and renders an empty table. Data that should be displayed is not bound on the server-side. Therefore, table body is not needed as data is going to be pulled from the server. In a client side mode, <tbody></tbody> section would contain rows that should be displayed on the page. However, in a server-side mode, data is dynamically taken via Ajax calls.
Since all processing and display is done on the client-side in browser, usage of the server-side template engine is irrelevant. However, in real situation, if we should bind some dynamic data on the server-side, we could use any MVC template engine such as ASPX, Razor, Spark or NHaml.
View includes standard JQuery and DataTables libraries required to initialize a table, as well as standard DataTables CSS file which can be replaced with any custom style-sheet.
Code that initializes DataTables plugin should be placed in the included index.js file as shown below:   
$(document).ready(function () {

    $('#myDataTable').dataTable({
        "bServerSide": true,
        "sAjaxSource": "Home/AjaxHandler",
        "bProcessing": true,
        "aoColumns": [
                        { "sName": "ID",
                            "bSearchable": false,
                            "bSortable": false,
                            "fnRender": function (oObj) {
                                return '<a href=\"Details/' + oObj.aData[0] + '\">View</a>';
                            }
                        },
                        { "sName": "COMPANY_NAME" },
                        { "sName": "ADDRESS" },
                        { "sName": "TOWN" }
                    ]
    });
}); 

Initialization code is placed in the standard JQuery document ready wrapper. It finds table with myDataTable id and magic begins. By setting bServerSide parameter to true DataTables is initialized to work with server-side page. Another parameter, sAjaxSource, should point to arbitrary URL of the page that will provide data to client-side table (“Home/AjaxHandler” in this example). Parameter bProcessing tells DataTables to show the “Processing...” message while the data is beingfetched from the server, while aoColumns defines properties of the table columns (e.g. whether they can be used for sorting or filtering, whether some custom function should be applied on each cell when it is rendered etc – more information about DataTables properties can be found on the DataTables site) and it is not directly related to client-server setup of DataTables. 

Controller  

Since there is no server-side processing, controller class is also fairly simple and it practically does nothing. Controller class used in example is shown below:.
public class HomeController : Controller
{
    public ActionResult Index()
       {
            return View();
       }
}  

As shown in the snippet, controller just waits for someone to call “Home/Index” URL and forwards request to Index view. All data processing is done in Home/AjaxHandler controller action.

Implementation of Ajax service

Once the table has been initialized it is necessary to implement server-side logic that will provide data to DataTables. Server-side service will be called (by JQuery DataTables) each time data should be displayed. Since DataTables configuration declared “Home/AjaxHandler” as URL that should be used for providing data to DataTable, we need to implement AjaxHandler action in Home controller that will react on the Home/AjaxHandler calls. Example:

public class HomeController : Controller
{
        public ActionResult AjaxHandler(jQueryDataTableParamModel param)
        {
            return Json(new{
                                sEcho = param.sEcho,
                                iTotalRecords = 97,
                                iTotalDisplayRecords = 3,
                                aaData = new List<string[]>() {
                                                new string[] {"1", "a1", "a2", "a3"},
                                                new string[] {"2", "b1", "b2", "b3"},
                                                new string[] {"3", "c1", "c2", "c3"}
                                                }
                            },
                        JsonRequestBehavior.AllowGet);
        }
}        

Action method returns dummy 3x4 array that simulates information expected by the DataTable plugin i.e.JSON data containing the number of total records, number of records that should be displayed and two dimensional matrix representing the table cells. Example:

{   "sEcho":"1",
    "iTotalRecords":97,
    "iTotalDisplayRecords":3,
    "aaData":[    ["1","a1","a2","a3"],
                  ["2","b1","b2","b3"],
                  ["3","c1","c2","c3"]
        ]
}
Values that server returns to the DataTables plugin are:
  1. sEcho - integer value that is used by DataTables for synchronization purpose. On each call sent to the server-side page, DataTables plugin sends the sequence number in sEcho parameter. Same value has to be returned in response because DataTables uses this for synchronization and matching requests and responses.
  2. iTotalRecords - integer value that represents total unfiltered number of records that exist on the server-side and that might be displayed if no filter is applied. This value is used only for display purposes; when user types in some keyword in a search text box DataTables shows “Showing 1 to 10 of 23 entries (filtered from 51 total entries)” message. In this case iTotalRecords value returned in response equals 51.
  3. iTotalDisplayedRecords - integer value that represents the number of records that match  current filter. If user does not enter any value in a search text box this value should be same as the iTotalRecords value. DataTables plugin uses this value to determine how many pages will be required to generate pagination - if this value is less or equal than the current page size pagination buttons will be disabled. When user types in some keyword in a search text box DataTables shows “Showing 1 to 10 of 23 entries (filtered from 51 total entries)” message. In this case iTotalDisplayedRecords value returned in response equals 23.
  4. aaData – two-dimensional array that represents cell values that will be shown in table. When DataTables receives data it will populate table cells with values from the aaData array. Number of columns in two dimensional array must match the number of columns in the HTML table (even the hidden ones) and number of rows should be equal to the number of items that can be shown on the current page (e.g. 10, 25, 50 or 100 this value is selected in the "Show XXX entries" dropdown). 
Once DataTables is initialized, it calls Home/AjaxHandler URL with various parameters. These parameters can be placed in the method signature, so MVC can map them directly, or accessed via Request object as in standard ASP.NET, but in this case they are encapsulated in the JQueryDataTableParamModel class given below.

/// <summary>
/// Class that encapsulates most common parameters sent by DataTables plugin
/// </summary>
public class jQueryDataTableParamModel
{
        /// <summary>
        /// Request sequence number sent by DataTable, same value must be returned in response
        /// </summary>       
        public string sEcho{ get; set; }

        /// <summary>
        /// Text used for filtering
        /// </summary>
        public string sSearch{ get; set; }

        /// <summary>
        /// Number of records that should be shown in table
        /// </summary>
        public int iDisplayLength{ get; set; }

        /// <summary>
        /// First record that should be shown(used for paging)
        /// </summary>
        public int iDisplayStart{ get; set; }

        /// <summary>
        /// Number of columns in table
        /// </summary>
        public int iColumns{ get; set; }

        /// <summary>
        /// Number of columns that are used in sorting
        /// </summary>
        public int iSortingCols{ get; set; }

        /// <summary>
        /// Comma separated list of column names
        /// </summary>
        public string sColumns{ get; set; }
}     

 DataTables plugin may send some additional parameters, but for most purposes mentioned parameters should be enough.

Initial data request

First example of server-side processing implementation shown in this article is response to the initial call. Immediately after initialization, DataTables sends first call to sAjaxSource URL and shows JSON data returned by that page. Implementation of method that returns data needed for initial table population is shown below:

public ActionResult AjaxHandler(jQueryDataTableParamModel param)
{
    var allCompanies = DataRepository.GetCompanies();
            
    var result = from c in allCompanies
            select new[] { c.Name, c.Address, c.Town };

    return Json(new { sEcho = param.sEcho,
                         iTotalRecords = allCompanies.Count(),
                         iTotalDisplayRecords = allCompanies.Count(),
                         aaData = result
            },
                    JsonRequestBehavior.AllowGet);
} 

 List of all companies is fetched from repository, they are formatted as two-dimensional matrix containing cells that should be shown in the table, and sent as JSON object. Parameters iTotalRecords and iTotalDisplayRecords are equal to number of companies in the list as this is a number of records that should be shown and number of total records in a data set.
The only parameter used from the request object is sEcho , and it is just returned back to the DataTables. Although this server action is good enough to display initial data, it does not handle other data table operations such as filtering, ordering, and paging.

Implementation of Server-side filtering 

DataTables adds one text box so the user can filter results displayed in the table by entering keyword. In server-side mode, each time user enters some text in the text box, DataTables sends new Ajax request to server expecting only those entries that match the filter. In order to handle user request for filtering, AjaxHandler must be slightly modified. Example:

public ActionResult AjaxHandler(jQueryDataTableParamModel param)
{
       var allCompanies = DataRepository.GetCompanies();
       IEnumerable<Company> filteredCompanies;
       if (!string.IsNullOrEmpty(param.sSearch))
       {
          filteredCompanies = DataRepository.GetCompanies()
                   .Where(c => c.Name.Contains(param.sSearch)
                               ||
                    c.Address.Contains(param.sSearch)
                               ||
                               c.Town.Contains(param.sSearch));
       }
       else
       {
           filteredCompanies = allCompanies;
       }

       var displayedCompanies = filteredCompanies;  
       var result = from c in displayedCompanies 
            select new[] { Convert.ToString(c.ID), c.Name, c.Address, c.Town };
       return Json(new{  sEcho = param.sEcho,
                         iTotalRecords = allCompanies.Count(),
                         iTotalDisplayRecords = filteredCompanies.Count(),
                         aaData = result},
                        JsonRequestBehavior.AllowGet);

In the given example, we use Linq query to filter list of companies by param.sSearch value. Filtered companies are returned as JSON results. Number of all records and records that should be displayed are returned as well.

DataTables can use multiple column based filters instead of single filter that is applied on the whole table. Detailed instructions for setting multi-column filter can be found on DataTables site (multi filtering example). In multi-column filtering configuration, DataTables sends individual column filters to server side in request parameters sSearch_0sSearch_1, etc. Number of request variables is equal to the iColumns variable. Also, instead of the param.sSearch value, you may use particular values for column as shown in the example:

    //Used if particular columns are separately filtered 
    var nameFilter = Convert.ToString(Request["sSearch_1"]);
    var addressFilter = Convert.ToString(Request["sSearch_2"]);
    var townFilter = Convert.ToString(Request["sSearch_3"]);

DataTables initialization settings could specify whether some column is searchable or not (ID column is not searchable in previous example). DataTables also sends additional parameters to the server-side page so server side component can determine which fields are searchable at all. In configuration used in this article, DataTables sends the individual column filters to server as request parameters (bSearchable_0, bSearchable_1, etc). Number of request variables is equal to the iColumns variable.

    //Optionally check whether the columns are searchable at all 
    var isIDSearchable = Convert.ToBoolean(Request["bSearchable_0"]);
    var isNameSearchable = Convert.ToBoolean(Request["bSearchable_1"]);
    var isAddressSearchable = Convert.ToBoolean(Request["bSearchable_2"]);
    var isTownSearchable = Convert.ToBoolean(Request["bSearchable_3"]); 

Example configuration used in this article has isIDSearchable variable set to false, while other variables are set to true. Values that are sent to server depend on aoColumns setting in database initialization function.
Problem with server-side filtering might be big number of Ajax requests sent to server. DataTables plugin sends new Ajax request to server each time user changes a search keyword (e.g. types or deletes any character). This might be a problem since server needs to process more requests although only some of them will really be used. Therefore, it would be good to implement some delay function where request will be sent only after some delay (there is example of fnSetFilteringDelay function on DataTables site).

Implementation of Server-side paging   

Another functionality that is added by DataTables plugin is ability to perform paging on the displayed records. DataTables can add either Previous-Next buttons or standard paging numbers. In server-side mode, each time user clicks on paging link, DataTables plugin sends information about current page and page size to server-side URL that should process the request. AjaxHandler method that processes paging should be modified to use information sent in request as shown in the example:

public ActionResult AjaxHandler(jQueryDataTableParamModel param)
{
    var allCompanies = DataRepository.GetCompanies();
    IEnumerable<Company> filteredCompanies = allCompanies;
            
    var displayedCompanies = filteredCompanies
                        .Skip(param.iDisplayStart)
                        .Take(param.iDisplayLength); 
 
    var result = from c in displayedCompanies 
                select new[] { Convert.ToString(c.ID), c.Name,
                          c.Address, c.Town };
    return Json(new{  sEcho = param.sEcho,
                              iTotalRecords = allCompanies.Count(),
                              iTotalDisplayRecords = filteredCompanies.Count(),
                              aaData = result},
                        JsonRequestBehavior.AllowGet);
}   
This example is similar to the previous one, but here we use param.iDisplayStart and param.IDisplayLength parameters. These are integer values representing starting index of the record that should be shown and number of results that should be returned.

Implementation of Server-side sorting

Last functionality that will be explained in this article is sorting results by column. DataTables plugin adds event handlers in HTML columns so user that can order results by any column. DataTables supports multi-column sorting too, enabling user to order results by several column, pressing the SHIFT key while clicking on the columns. To implement ordering AjaxHandler should be modified to use information about column that should be used for ordering as shown in the example:

public ActionResult AjaxHandler(jQueryDataTableParamModel param)
{
    var allCompanies = DataRepository.GetCompanies();
    IEnumerable<Company> filteredCompanies = allCompanies;

    var sortColumnIndex = Convert.ToInt32(Request["iSortCol_0"]);
    Func<Company, string> orderingFunction = (c =>  sortColumnIndex == 1 ? c.Name :
                                                        sortColumnIndex == 2 ? c.Address :
                                                        c.Town);

    var sortDirection = Request["sSortDir_0"]; // asc or desc
    if (sortDirection == "asc")
        filteredCompanies = filteredCompanies.OrderBy(orderingFunction);
    else
                filteredCompanies = filteredCompanies.OrderByDescending(orderingFunction);

    var displayedCompanies = filteredCompanies;
    var result = from c in displayedCompanies
                         select new[] { Convert.ToString(c.ID), c.Name, c.Address, c.Town };
    return Json(new
        {
            sEcho = param.sEcho,
            iTotalRecords = allCompanies.Count(),
            iTotalDisplayRecords = filteredCompanies.Count(),
            aaData = result
        },
        JsonRequestBehavior.AllowGet);

There is an assumption that server-side knows which fields are sortable. However, if this information is not known or it can be dynamically configured, DataTables sends all necessary information in each request. Columns that are sortable are sent in array of request parameters called bSortable_0, bSortable_1, bSortable_2, etc. Number of parameters is equal to the number of columns that can be used for sorting which is also sent in iSortingCols parameter. In this case, name, address, and town might be sortable on client side, so the following code determines whether they are actually sortable or not:

    var isNameSortable = Convert.ToBoolean(Request["bSortable_1"]);
    var isAddressSortable = Convert.ToBoolean(Request["bSortable_2"]);
    var isTownSortable = Convert.ToBoolean(Request["bSortable_3"]); 

These variables can be added in conditions of ordering function creating configurable sort functionality.  

Summary  

This article represents step-by-step guideline for integrating JQuery DataTables plugin into server-side code. It shows how the standard DataTables plugin that, by default, works with client-side data, can be configured to take all necessary data from the server via Ajax calls.
Server-side code used in this example is pure Linq query set performed on in-memory collection of objects. However in the real application, we should use some data access components such as Linq2Sql, Entity framework, stored procedures, WCF services or any other code that takes data, performs sorting, paging and filtering. As these data access samples are out of the scope of this article they are not used in the example. 

A complete example with controller action where are merged all functionalities described in the article can be downloaded from here Download JQueryDataTables.zip - 89.58 KB.  This is  a project created in Visual Web developer 2010 and you will need to have installed ASP.NET MVC with Razor. If you are not using Razor it is not a big problem - I recommend that you copy some files into your project (Controller, model, javascrits) and modify them if needed.

推荐.NET配套的通用数据层ORM框架:CYQ.Data 通用数据层框架
新浪微博粉丝精灵,刷粉丝、刷评论、刷转发、企业商家微博营销必备工具"