Introduction

The jQuery DataTables plug-in is an excellent client-side component that can be used to create rich-functional tables in the web browser. This plug-in adds lot of functionalities to the plain HTML tables that are placed in web pages such as filtering, paging, sorting, changing page length, etc.

This article shows how the jQuery DataTables plug-in can be integrated into an ASP.NET MVC application. It contains step by step examples that show how the DataTables plug-in interacts with server-side components.

This article do not cover all possible integration scenarios of JQuery DataTables plugin in ASP.NET MVC application. For other integation scenarios, you might also take a look at the other articles in this serie:

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 postback a request to the server and where user actions are handled by the sever code and a new table is generated. The other group of components, such as jQuery DataTables, FlexGrid, or jqGrid are implemented as client-side code. These plugins take the plain HTML tables as the one shown on the following figure and add various enhancements.

As an example, if you apply JQuery DataTables plugin to this plain HTML table, you will get something like the table on the following figure:

The DataTables plug-in adds a "Show XXX entries" dropdown above the table enabling the user to choose whether he wants to see 10, 25, 50, or 100 records per page, and a search text box that enables the user to filter by keyword records that should be shown in the table. This plug-in also adds sorting functionality that enables the user to sort results by clicking on the column header. Below the table, there is pagination that enables the 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:

$('#myDataTable').dataTable();

Under assumption that the plain table shown on the first figure has an id "myDataTable", this code will enhance the table with DataTables plugin. Most of these functionalities can be used completely separate from the server-side code, i.e., the web server can generate a plain HTML table in standard format in any server-side technology such as ASP.NET Web Forms, ASP.NET MVC, PHP, Java etc. The client-side JavaScript components will use whatever gets generated and add client-side functionalities. In this client-side mode, DataTables takes all the table rows from the <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 the server returns all the 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 calls and memory usage on the client. However, this minimizes the number of requests sent to the server because once the table is loaded, the server is not used at all.

If you are interested in using the JQuery DataTables plugin in pure client-side mode, then you do not need to read this article. All you need to do is to generate a plain HTML table and apply plugin. You can use various configuration options in the plugin so if you are interested in this mode you might read the following article "Enhancing HTML tables using the jQuery DataTables plug-in" where I have explained various configuration options of DataTables plugin.

Theme of this article is using the JQuery DataTables plugin in the server-side processing mode.

DataTables Server-side processing mode

It is possible to implement client-server interaction by configuring DataTables to query the server via AJAX calls in order to fetch the required data. In this case, table that is generated on the client side is initially empty as the one shown in the following example:

In this code is used the server-side processing mode by setting the bServerSide parameter to true. In this mode, DataTables plugin will load table data from the remote URL using the Ajax request. The second parameter defines to what URL DataTables plugin should send Ajax request in order to load the data into the table.

Once the plug-in is applied on such of table, it will call the server side page (server_processing.php in the example above), post information about the required data, take the response from the server, and load the table data dinamically. The server response is formatted as a JSON object, parsed on the client side, and displayed in the table body. The following figure shows a trace of the calls sent to the server (captured using the Firebug add-in for Firefox).

In this case, each event (changing the number of items that should be displayed per page, entering a keyword in the search filter, sorting, pressing the pagination button, etc.) triggers the DataTables plug-in to send information about the current page, search filter, and sort column to the server page. As shown in the third request, the server page returns JSON as a result and DataTables uses that data array when displaying the current table page. In this mode, instead of taking the 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 the server_processing.php page and sends information about the user action. A dull example of the server-side configuration of the jQuery DataTables plug-in can be found here. A major problem with the server-side mode is the implementation of the server-side logic that accepts parameters from the client-side component, performs action, and returns the 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:

Creating the model classes that represent the data structure that will be shown

Creating the controller class that will react on the user events

Creating the view that will render data and create the HTML code that is sent to the browser window

A simple application that keeps information about companies and displays them in a table will be used as an example. This simple table will be enhanced with the jQuery DataTables plug-in and configured to take all the necessary data from the server-side. The following JavaScript components need to be downloaded:

jQuery library containing the standard classes used by the DataTables plug-in

The view engine used is Razor but any other view engine can be used instead, as the engine specific code is only setting the layout page on the top of the page. The page includes all the necessary JavaScript libraries and renders an empty table. Data that should be displayed is not bound on the server-side. Therefore, the table body is not needed as data is going to be pulled from the server. In client side mode, the <tbody></tbody> section would contain rows that should be displayed on the page. However, in server-side mode, data is dynamically taken via AJAX calls. Since all processing and display is done on the client-side in the browser, the usage of the server-side template engine is irrelevant. However, in a 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. The View includes the standard jQuery and DataTables libraries required to initialize a table, as well as the standard DataTables CSS file which can be replaced with any custom style-sheet. Code that initializes the DataTables plugin should be placed in the included index.js file as shown below:

The initialization code is placed in the standard jQuery document ready wrapper. It finds the table with the myDataTable ID and the magic begins. By setting the bServerSide parameter to true, DataTables is initialized to work with the server-side page. Another parameter, sAjaxSource, should point to an arbitrary URL of the page that will provide data to client-side table ("Home/AjaxHandler" in this example). The parameter bProcessing tells DataTables to show the "Processing..." message while the data is being fetched from the server, while aoColumns defines the 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 the client-server setup of DataTables.

Controller

Since there is no server-side processing, the controller class is also fairly simple and it practically does nothing. The controller class used in the example is shown below:

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

Implementation of server-side service

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

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

sEcho - the integer value that is used by DataTables for synchronization purpose. On each call sent to the server-side page, the DataTables plug-in sends the sequence number in the sEcho parameter. The same value has to be returned in response because DataTables uses this for synchronization and matching requests and responses.

iTotalRecords - the integer value that represents the 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 then user types in some keyword in a search text box, DataTables shows a "Showing 1 to 10 of 23 entries (filtered from 51 total entries)" message. In this case, the iTotalRecords value returned in response equals 51.

iTotalDisplayedRecords - The integer value that represents the number of records that match the current filter. If the user does not enter any value in the search text box, this value should be the same as the iTotalRecords value. The DataTables plug-in 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 the user types in some keyword in the search text box, DataTables shows "Showing 1 to 10 of 23 entries (filtered from 51 total entries)" message. In this case, the iTotalDisplayedRecords value returned in the response equals 23.

aaData - the two-dimensional array that represents the cell values that will be shown in the table. When DataTables receives data, it will populate the table cells with values from the aaData array. The number of columns in the two dimensional array must match the number of columns in the HTML table (even the hidden ones) and the 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 the Home/AjaxHandler URL with various parameters. These parameters can be placed in the method signature so MVC can map them directly, or accessed via the Request object as in standard ASP.NET, but in this example, they are encapsulated in the JQueryDataTableParamModel class given below.

///<summary>/// Class that encapsulates most common parameters sent by DataTables plugin
///</summary>publicclass jQueryDataTableParamModel
{
///<summary>/// Request sequence number sent by DataTable,
/// same value must be returned in response
///</summary>publicstring sEcho{ get; set; }
///<summary>/// Text used for filtering
///</summary>publicstring sSearch{ get; set; }
///<summary>/// Number of records that should be shown in table
///</summary>publicint iDisplayLength{ get; set; }
///<summary>/// First record that should be shown(used for paging)
///</summary>publicint iDisplayStart{ get; set; }
///<summary>/// Number of columns in table
///</summary>publicint iColumns{ get; set; }
///<summary>/// Number of columns that are used in sorting
///</summary>publicint iSortingCols{ get; set; }
///<summary>/// Comma separated list of column names
///</summary>publicstring sColumns{ get; set; }
}

The DataTables plug-in may send some additional parameters, but for most purposes, the mentioned parameters should be enough.

Loading data into the table

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

The list of all companies is fetched from the repository; they are formatted as a two-dimensional matrix containing the cells that should be shown in the table, and sent as a JSON object. The parameters iTotalRecords and iTotalDisplayRecords are equal to the number of companies in the list as this is the number of records that should be shown and the 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 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.

Filtering records

DataTables plugin adds a text box in the table, so the user can filter the results displayed in the table by entering a keyword. Text box used for filtering is shown on the following figure:

In server-side processing mode, each time the user enters some text in the text box, DataTables sends a new AJAX request to the server-side expecting only those entries that match the filter. DataTables plugin sends the value entered in the filter text box in the sSearch HTTP request parameter. In order to handle the user request for filtering, AjaxHandler must be slightly modified, as it is shown in the following listing:

In the given example, we use a LINQ query to filter the list of companies by the param.sSearch value. DataTables plugin sends the keyword entered in the text box in the sSearch parameter. The filtered companies are returned as JSON results. The number of all records and the records that should be displayed are returned as well.

Multi-column filtering

DataTables can use multiple column based filters instead of a single filter that is applied on the whole table. Detailed instructions for setting a multi-column filter can be found on the DataTables site (multi-filtering example). When multi-column filtering is used, in the table footer are added separate text boxes for filtering each individual columns, as it is shown on the following figure:

In multi-column filtering configuration, DataTables sends individual column filters to the server side in request parameters sSearch_0, sSearch_1, etc. The number of request variables is equal to the iColumns variable. Also, instead of the param.sSearch value, you may use particular values for columns as shown in the example:

DataTables initialization settings could specify whether a column is searchable or not (the ID column is not searchable in the 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 the configuration used in this article, DataTables sends the individual column filters to server as request parameters (bSearchable_0, bSearchable_1, etc). The number of request variables is equal to the iColumns variable.

The example configuration used in this article has the isIDSearchable variable set to false, while other variables are set to true. Values that are sent to the server depend on the aoColumns setting in the database initialization function. The problem with server-side filtering might be a big number of AJAX requests sent to the server. The DataTables plug-in sends a new AJAX request to the server each time the user changes a search keyword (e.g., types or deletes any character). This might be a problem since the 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 the request will be sent only after some delay (there is an example of the fnSetFilteringDelay function on the DataTables site).

Pagination

Another functionality that is added by the DataTables plug-in is the ability to perform paging on the displayed records. DataTables can add either Previous-Next buttons or standard paging numbers. also it enables you to change the number of the records that will be displayed per page using the drop-down. Drop-down and pagination links are shown on the following figure:

In server-side mode, each time the user clicks on a paging link, the DataTables plug-in sends information about the current page and the page size to a server-side URL that should process the request. The AjaxHandler method that processes paging should be modified to use information sent in the request as shown in the example:

This example is similar to the previous one, but here we use the param.iDisplayStart and param.IDisplayLength parameters. These are integer values representing the starting index of the record that should be shown and the number of results that should be returned.

Sorting

The last functionality that will be explained in this article is sorting results by column. The DataTables plug-in adds event handlers in HTML columns so the user that can order results by any column. DataTables supports multi-column sorting too, enabling user to order results by several columns, pressing the SHIFT key while clicking on the columns. DataTables adds event handlers to the column heading cells with direction arrows as it is shown on the following figure:

Each time user clicks on the column, DataTables plugin sends information about the column and sort order direction (ascending or descending). To implement sorting, AjaxHandler should be modified to use information about the column that should be used for ordering, as shown in the example:

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

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

Multi-column sorting

DataTables plugin enables multicolumn sorting by defualt. If you hold SHIFT key and click on several heading column cells, table will be ordered by first column then by second etc. On the following figure is shown how rows in the table are sorted by the first three columns at the same time.

This is directly implemented in the client-side mode; however, in the server-side processing mode you will need to implement logic that will order records by several columns it in the controlller.

When several columns are selected for sorting, for each column that should be sorted DataTables sends in the Ajax request pairs iSortCol_0, sSortDir_0, iSortCol_1, sSortDir_1, iSortCol_2, sSortDir_2, where each pair contains position of the column that should be ordered and sort direction. in the previous code samle i have used only iSortCol_0 and sSortDir_0, because I have assumed that only single column sorting is used.

Multi-column sorting code is similar to the code shown in the previous example but you will need to put several ordering functions for each column, and apply OrderBy().ThenBy().ThenBy() chain of functions. Due to the specific nature of this code and complexity I have not implemented it here. Note that if this is a requirement more easier solution would be to use dinamically generated SQL Query where you will concatenate these columns and sorting directions in the "ORDER BY" clause. Linq is great and clean code for presentation adn maintenence; howerer, in some situations where you need too much customization, you need to go to lower level functionalities.

Summary

This article represents a step-by-step guideline for integrating the jQuery DataTables plug-in into server-side code. It shows how the standard DataTables plug-in that, by default, works with client-side data can be configured to take all the necessary data from the server via AJAX calls. The server-side code used in this example is a pure LINQ query set performed on an in-memory collection of objects. However, in a 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 and 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 the link above. 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, JavaScript's) and modify them if needed.

This article is just a first part in the serie about using the JQuery DataTables plugin in ASP.NET applications. Other parts in this serie are:

Share

About the Author

Started as a young scientist - winning the highest national awards in mathematics, physics, electrotechnics, and electronics.
Graduated from Faculty of Electrical Engineering, Department of Computer Techniques and Informatics, University of Belgrade, Serbia, as a first in the class, as a Master of Software Sciences.
Currently working in Gowi as a Software engineer, architect, and project manager since 2004 - mostly using Microsoft technologies (ASP.NET, C#). Member of JQuery community - created few popular plugins (four popular JQuery DataTables add-ins and loadJSON template engine).
Interests: Software engineering process(estimation and standardization), mobile and business intelligence platforms.

Comments and Discussions

Thanks very much for this tutorial. I have found it very helpful in finishing my project. However, I am stumped by one point, which is defining DT_RowClass for each row.
As you know, the javascript object looks like:

Hi
I was using the server side sorting from your article.
It works fine when the type of columns I am sorting are strings of characters. But when it comes sorting a column with numbers, it sorts them into a random fashion. There is no descending or ascending order, just random.
This is the function

thanks, I''m glad that you like it. Regarding the check-boxes there is nothing special you can do - just generate input type=checkbox in the cells they will be displayed. DataTables plugin do not touch any content of the cell it just adds sorting, filtering etc.

I am able to add checkbox to each row of the datatable, but the real concern is i am not able to generate events like SELECT ALL (same as the GridView in the Dot Net). Once I click the checkbox the respective row should get selected and I must be able to get which row the user has clicked. Same should work for deselect too.

I have to access it for each row, but what syntax is used for .each? something like this? Plz let me know what is that 'xxxx' may be in my code? .each(function(){
if (this.checked) {
Ids.push(this.value);
} else {
for (var i=0; i

Now, How can I get theese parameters into my ViewResult AjaxHandler(...)
when I Try this:
var forms = Request.QueryString.Get("form");
it does not work, whats more, in debugger I cant see any key that is called 'form' or 'course'. This way works when is used in ActionResult Index(){...}