Table of Contents
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.
$("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
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)
{
//Implementation of action body
}
}
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 on 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 () {
// Setup JQuery UI date picker format to dd/mm/yy
$.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, 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('~'))
{
//Split number range filters with ~
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('~')){
//Split date range filters with ~
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)
);
//Extract only current page
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 theree 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.
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.
0 comments:
Post a Comment