This article explains how to configure jQuery DataTables and implement server-side logic with ASP.NET MVC controllers.
Table of Contents
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 does not cover all possible integration scenarios of JQuery DataTables plugin in ASP.NET MVC application. For other integration scenarios, you might also take a look at the other articles in this series:
Also, if you want to see all possible configurations of the JQuery DataTables plugin, you might take a look at the Enhancing HTML tables using the jQuery DataTables plug-in article many useful configuration options are described.
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 in 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 in 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 the 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 renders 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.
The 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:
<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>
As you might notice, this "table" do not have any rows in it. In order to apply the plugin to this "table", you will need to call something like a following code:
$('#myDataTable').dataTable({
"bServerSide": true,
"sAjaxSource": "server_processing.php"
});
In this code, the server-side processing mode is used 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 a 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 dynamically. 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
- jQuery DataTables plug-in including optional DataTables CSS style-sheets used for applying the 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. An example of usage of these files is explained below.
Model
The Model comes to a simple class containing company data. The fields that we need are company ID, name, address, and town. The source code of the 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 the data presentation is done on the client-side, the classic View page is fairly simple. It contains a simple HTML table "decorated" with the jQuery DataTables plug-in. For 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>
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:
$(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" }
]
});
});
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:
public class HomeController : Controller
{
public ActionResult Index()
{
return View();
}
}
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:
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", "Microsoft", "Redmond", "USA"},
new string[] {"2", "Google", "Mountain View", "USA"},
new string[] {"3", "Gowi", "Pancevo", "Serbia"}
}
},
JsonRequestBehavior.AllowGet);
}
}
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":"1",
"iTotalRecords":97,
"iTotalDisplayRecords":3,
"aaData":[ ["1","Microsoft","Redmond","USA"],
["2","Google","Mountain View","USA"],
["3","Gowi","Pancevo","Serbia"]
]
}
Values that the server returns to the DataTables plug-in are:
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 the 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.
public class jQueryDataTableParamModel
{
public string sEcho{ get; set; }
public string sSearch{ get; set; }
public int iDisplayLength{ get; set; }
public int iDisplayStart{ get; set; }
public int iColumns{ get; set; }
public int iSortingCols{ get; set; }
public string 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:
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);
}
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 in 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 is shown in the following listing:
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 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 is shown in 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:
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 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.
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"]);
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 in 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:
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 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 in 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:
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"];
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 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:
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 the conditions of the ordering function, creating a configurable sort functionality.
Multi-Column Sorting
DataTables plugin enables multicolumn sorting by default. If you hold SHIFT key and click on several heading column cells, table will be ordered by first column, then by second, etc. The following figure shows 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 and sSortDir_2 where each pair contains position of the column that should be ordered and sort direction. In the previous code sample, 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, an easier solution would be to use dynamically 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 and maintenance; however, 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 series about using the JQuery DataTables plugin in ASP.NET applications. Other parts in this series are:
- Part 2 - Implementation of an editable DataTable in ASP.NET MVC - how to add data management (CRUD) functionalities such as adding, deleting and editing rows
- Part 3 - Reloading content of data tables in ASP.NET MVC - how to reload DataTable content using Ajax
- Part 4 - Creating an expandable master-details table - how to implement opening details row when regular row is selected
- Part 5 - jQuery DataTables Advanced Filtering in ASP.NET MVC - how to implement advanced filtering
- Part 6 - Table Row Drag and Drop in ASP.NET MVC - how to use drag'n'drop row reordering
There are also few other articles you might be interested in:
- Enhancing HTML tables using the jQuery DataTables plug-in where I have explained various configuration options of DataTables plugin,
- JQuery Data Tables in Java Web Applications - article similar to this one showing implementation in Java.
I hope that this series of articles will help you to easily implement a better user interface.
History
- 9th February, 2011: Initial version
