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:
Hide Copy Code
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:
Hide Shrink 
Copy Code
Copy Code@{
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:
Hide Copy Code
$(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:
Hide Shrink Copy Code
Copy Codepublic 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.
No comments:
Post a Comment