This article aims to show how to implement tables where the content of the table will be refreshed with AJAX calls each time the user changes the filter.
Table of Contents
- Introduction
- Background
- Using the Code
- Model
- View
- Controller
- Conclusion
- History
Introduction
In this article, I will explain how you can implement advanced filtering in fully AJAXified tables using the jQuery DataTables plug-in and ASP.NET MVC. An example of a table with column filters is shown in the following figure:
As you can see, among the other table features (pagination, sorting), this table has separate filters placed in each column. The goal of this article is to show how you can implement tables where the content of the table will be refreshed with AJAX calls each time the user changes the filter.
The jQuery DataTables^ plug-in can work in two major modes:
- Client side processing mode - in this mode, you just need to put a plain HTML table in the page and the plug-in will add all the necessary actions as JavaScript functionalities. In this mode, the plug-in will process rows in the table directly, so you don't need to implement anything - the plug-in will handle filtering, pagination, and sorting using the rows it finds in the table.
- Server side processing mode - where the table is completely AJAXified. In this mode, you should put an empty table in the HTML of the page and specify from what URL the jQuery DataTables^ plug-in should take rows that should be displayed. In this mode, the jQuery DataTables^ plug-in will handle all user interaction and send information about the current page, filter condition, and sorting to your server-side page via an AJAX call. All you need to do is to create some controller that will receive parameters and return results that will be loaded into the table body.
If you want to add pagination, sorting, and filtering in the client-side mode, you will need just one line of JavaScript code:
$("table#myTable").dataTable().columnFilter();
This line finds a table with id myTable, applies the dataTable plugin that adds pagination and sorting features, and then applies the columnFilter plugin that puts filters in the individual columns. This is everything you need to do in the client side mode. Optionally, you can customize plugins by passing different parameters - see more examples on the DataTables Column Filter^ site.
However, if you work with larger data sets, you might want to implement some AJAXified table where filtering conditions will be sent to the server. You will need to add more code for this, therefore I will explain that scenario in this article.
This article is part of a series about the integration of the jQuery DataTables^ plug-in with ASP.NET MVC web applications. You might also want to take a look at other articles in this group:
- jQuery DataTables and ASP.NET MVC Integration
- ASP.NET MVC Editable Table
- Refreshing the content of DataTable using Ajax in ASP.NET MVC
- Creating an expandable master-details table
In these articles, you might find lots of useful information about the usage of the jQuery DataTables^ plug-in in ASP.NET MVC. If you have not read the first article, I would recommend that you read it too because it contains some important details about the integration of jQuery DataTables with ASP.NET MVC applications.
Background
jQuery DataTables is an excellent jQuery plug-in that enables you to display and manage information in the table. To use the jQuery Datatables plug-in in your MVC applications, you will need to setup the following steps.
First, you will need to place in your page an empty table that represents the table structure. An example of a table is shown in the following code:
<table class="display" id="example">
<thead>
<tr>
<th>ID</th><th>Company</th><th>Town</th><th>Date</th>
</tr>
</thead>
<tbody>
</tbody>
<tfoot>
<tr>
<th>ID</th><th>Company</th><th>Town</th><th>Date</th>
</tr>
</tfoot>
</table>
In this table are defined fixed parts of the table such as table header, table footer, but the body is empty. With the DataTables plug-in, data that will be displayed in the body of the table will be loaded dynamically via AJAX calls.
Then, you will need a controller that has an action for providing data to the DataTables plug-in. This controller is shown in the following listing:
public class HomeController
{
public JsonResult DataProviderAction(string sEcho, int iDisplayStart, int iDisplayLength)
{
}
}
The actual implementation of the controller method is not shown here. If you are not familiar with the integration of the DataTables plug-in into the MVC application, you can find more in the first article in this series - jQuery DataTables and ASP.NET MVC Integration. There you can find everything that is needed for integration with the plug-in and the implementation of this controller action. Also, you have this method in the attached code sample.
Finally, you will need to bind the empty HTML table with the controller in order to start displaying data. This is done using the following jQuery call:
<script type="text/javascript" charset="utf-8">
$(document).ready( function () {
$('#example').dataTable({
"bServerSide": true,
"sAjaxSource": "/Home/DataProviderAction"
});
});
</script>
This initialization call defines that data will be fetched from the server side via an AJAX call, and that for Ajax source, action on the URL "/Home/DataProviderAction" will be used. As a result, you will get a fully functional table with pagination, sorting, and filtering as the one shown in the following figure:
This table is completely AJAXified - each time the user changes something on the client-side (e.g., change page or sort order, type anything in the search text box), an AJAX request will be sent to the controller and new set of data will be shown in the table.
The goal of this article is to show you how you can replace the default, global single keyword search with advanced multi-column filters. I will show you how you can use various types of filters such as drop down lists, date ranges, and a range of numbers.
Using the Code
Code is organized in a standard Model-View-Controller architecture.
Model
The Model comes to a simple class containing company data. The fields that we need are company ID, name, date, 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 DateTime DateCreated { 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 Filtering</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/init.js" type="text/javascript"></script>
</head>
<body>
<table id="myDataTable" class="display">
<thead>
<tr>
<th>ID</th>
<th>Company name</th>
<th>Town</th>
<th>Date</th>
</tr>
</thead>
<tbody>
</tbody>
<thead>
<tr>
<th>ID</th>
<th>Company name</th>
<th>Town</th>
<th>Date</th>
</tr>
</thead>
</table>
</body>
</html>
Also, as a part of the view, we would need to initialize the table with the jQuery DataTables plug-in and setup individual column filtering. This part will be implemented in the init.js file shown in the following listing:
$(document).ready(function () {
$.datepicker.regional[""].dateFormat = 'dd/mm/yy';
$.datepicker.setDefaults($.datepicker.regional['']);
$('#myDataTable').dataTable({
"bServerSide": true,
"sAjaxSource": "/Home/DataProviderAction"
}).columnFilter({
"aoColumns": [
{ "type": "number-range" },
{ "type": "text" },
{ "type": "select" },
{ "type": "date-range" }
]
});
});
The standard jQuery DataTables plugin is applied to the table, and configured in server-side processing mode. This initialization script will add standard DataTables features (pagination, filtering by keyword, and sorting).
In order to setup advanced column filtering, in this example, we use the DataTables Column Filtering add-on. This is an additional plug-in for DataTables that adds filters in each column and enables the user to filter data by individual columns.
Additionally, you can define in the plugin configuration what kind of filters you want to use in each column. In the example above, the first column will be filtered by number range, the second filter will be plain text, in the third column will be placed a select list, and the last column will be filtered by date range using two date pickers. The first two lines in the script are used to set the date format in the date pickers.
As a result of this script, individual column filters will be injected in the footer of the table as shown on the following figure. This is just an optional configuration for filtering - as an alternative, you can place filters in the header or even in the external form.
Column filters are also AJAXified. Each time the user types something in the column filters, values will be sent to the controller action. In the figure, you can see part of the AJAX request that is sent to the controller action. In sSearch_0, sSearch_1, sSearch_2 and sSearch_3 are sent values from the filters. Note that single filters (text box and select) are sent as single values, and in the number range and date filters are lower and higher range combined in the same value and separated with a tilde (~) character. If there is no value in the filter, an empty string will be sent as a boundary.
Controller
Controller is the most important part in the integration. It should handle AJAX requests sent from the plug-in, take parameters that contain the filter condition, and return companies that match the criterion. The full controller code is shown in the following listing:
public JsonResult DataProviderAction(string sEcho, int iDisplayStart, int iDisplayLength)
{
var idFilter = Convert.ToString(Request["sSearch_0"]);
var nameFilter = Convert.ToString(Request["sSearch_1"]);
var townFilter = Convert.ToString(Request["sSearch_2"]);
var dateFilter = Convert.ToString(Request["sSearch_3"]);
var fromID = 0;
var toID = 0;
if (idFilter.Contains('~'))
{
fromID = idFilter.Split('~')[0] == "" ? 0 : Convert.ToInt32(idFilter.Split('~')[0]);
toID = idFilter.Split('~')[1] == "" ? 0 : Convert.ToInt32(idFilter.Split('~')[1]);
}
DateTime fromDate = DateTime.MinValue;
DateTime toDate = DateTime.MaxValue;
if(dateFilter.Contains('~')){
fromDate = dateFilter.Split('~')[0] == "" ?
DateTime.MinValue : Convert.ToDateTime(dateFilter.Split('~')[0]);
toDate = dateFilter.Split('~')[1] == "" ?
DateTime.MaxValue : Convert.ToDateTime(dateFilter.Split('~')[1]);
}
var filteredCompanies = DataRepository.GetCompanies()
.Where(c => (fromID == 0 || fromID < c.ID)
&&
(toID == 0 || c.ID < toID)
&&
(nameFilter == "" ||
c.Name.ToLower().Contains(nameFilter.ToLower()))
&&
(townFilter == "" || c.Town == townFilter)
&&
(fromDate == DateTime.MinValue ||
fromDate < c.DateCreated)
&&
(toDate == DateTime.MaxValue || c.DateCreated < toDate)
);
var displayedCompanies = filteredCompanies.Skip(iDisplayStart).Take(iDisplayLength);
var result = from c in displayedCompanies
select new[] {
Convert.ToString(c.ID),
c.Name,
c.Town,
c.DateCreated.ToShortDateString()
};
return Json(new
{
sEcho = sEcho,
iTotalRecords = DataRepository.GetCompanies().Count(),
iTotalDisplayRecords = filteredCompanies.Count(),
aaData = result
},
JsonRequestBehavior.AllowGet);
}
The first action here is taking values of the filter boxes from the Request object. The jQuery DataTables plug-in sends these parameters in the sSearch_0, sSearch_1, ..., sSearch_N parameters, where N is the number of columns. Range filters (number range and date range filters) that use two values in the filters send ranges separated by ~ therefore they are split by this character, so the first part is set as the lower boundary and the second part as the higher boundary. Additionally, if only lower or higher boundary is entered on the page, default values are set (0 for number range boundary and MinDate/MaxDate for date range boundary). If values in the range filter contain default values, a filter will not be applied.
When the filter criteria is loaded, companies are filtered. There are many ways to implement this, e.g., via a SQL query or a Stored Procedure in the database, with LINQ query, etc. In this case, I have used the Where LINQ function where I have built a lambda expression that filters companies by filter parameters. Note that you can change the logic of this query depending on your requirements. In this query, I have used AND conditions but you can use OR instead. Also I have used the less operator in range filters, but you can use less or equal if you need it. From the DataTables point of view, this server-side logic is irrelevant - as long as you provide some results for the filter criterion, it will work fine.
Once companies are filtered, they are formatted as a JSON array and returned back to the plug-in as a response of the view. You can see more details about returning a JSON response to a plug-in in the article jQuery DataTables and ASP.NET MVC Integration. Also, you might notice that in this code, we have used information about the first record that should be displayed in the table (iDisplayStart) and the number of records that should be displayed on the current page in the table (iDisplayLength). This LINQ query has a simple pagination command (the Skip(iDisplayStart).Take(iDisplayLength) part) that displays only a page that the user currently is looking at. In real code, you should handle sorting by columns too, but this is not shown in this example. However, you can find detailed description about the integration in the jQuery DataTables and ASP.NET MVC Integration article.
Conclusion
In this article, I have explained how to implement a fully functional AJAXified table with advanced filters. As you can see, you need minimal effort on the view side - just an empty table and simple JavaScript that can be easily changed.
All you need to do is create a controller action method that will handle AJAX calls sent by the plug-in and filter them by criteria that the plug-in has sent.
In this article, I have shown some basic features of the DataTables and Column filter plug-in, however there are a lot of other settings that can be customized. Some of them are:
- Ability to position filters in the header row or even out of table instead of footer row.
- Ability to define the so called filtering delay. In order to prevent too much AJAX calls, you can define that a new AJAX request is sent when the user types in at least N characters before a table is refreshed.
- Ability to use Regular Expressions in the filters.
An example of the filtering configuration where filters are placed in a separate form is shown on the following figure:
Each time you change the filter condition in the form, the table will be refreshed. You can see other features of the column filter plug-in on the plug-in site. Also, DataTables and Column Filter plug-ins do not need to work in AJAXified server-side processing mode. If you do not want to create an action that handles requests, you can just output all rows in the TBODY of the table and let them work in pure client-side mode. If you want to use them in this mode, you might need to take a look at the Enhancing HTML tables using the jQuery DataTables plug-in article where I have explained what you can do with these plug-ins in pure client-side mode. Beware that client-side mode should not be used with huge amounts of data. If you have a lot of records that should be processed, you should consider using server-side processing mode.
History
- 13th March, 2012: Initial version
