Introduction
In this session, we will learn how to implement server-side processing with custom range filtering in jQuery DataTables. I am going to show you a DataTable's server-side paging, sorting, and filtering in ASP.NET MVC application. Server-side means using C# code behind the file in the Controller section. We can erase the global search box of jQuery DataTable and use our custom filter area with input fields like textbox and dropdown for implementing custom multicolumn server-side filtering in jQuery DataTables. By using that way, we can implement our own filter areas anywhere on our page, to work with jQuery DataTables.
Description
DataTable is a most powerful and easy-to-use jQuery plugin for displaying tabular data with features like pagination, searching, state saving, and multi-column sorting with data type detection and lots more, with ZERO or minimal configuration.
The prerequisites of this article include knowledge of the following technologies.
- ASP.NET MVC
- HTML
- JavaScript
- AJAX
- CSS
- Bootstrap
- C# Programming
- C# LINQ
- jQuery
Note. Before going through the session, I suggest you first visit my previous articles with a back-end section.
Steps to be followed
Step 1. Add a new action into the Controller to get the View where we will implement the jQuery DataTable with server-side paging and sorting.
Code
public ActionResult Filter()
{
return View();
}
Step 2. Add a View for the action (here "filter") and design.
Code
@{
ViewBag.Title = "Satyaprakash - Filter Records";
}
<h2 style="color: blue">Satyaprakash-Server Side Processing With Custom Range Filtering</h2>
<style>
table {
font-family: arial, sans-serif;
border-collapse: collapse;
width: 100%;
}
td, th {
border: 1px solid #dddddd;
text-align: left;
padding: 8px;
}
tr:nth-child(even) {
background-color: #dddddd;
}
.custom-loader-color {
color: #fff !important;
font-size: 40px !important;
}
.custom-loader-background {
background-color: #f60 !important;
}
.custom-middle-align {
vertical-align: middle !important;
}
</style>
<div style="width:90%; margin:0 auto">
<div style="background-color:#f5f5f5; padding:20px">
<h2 style="color: blue">Filter Records</h2>
<table>
<tbody>
<tr>
<td style="color: blue">City</td>
<td><input type="text" class="form-control" id="txtCity" /></td>
<td style="color: blue">State</td>
<td>
<select id="ddState" class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">
<option value="">All States</option>
<option value="Karnataka">Karnataka</option>
<option value="Andhra Pradesh">Andhra Pradesh</option>
<option value="Georgia">Georgia</option>
<option value="Uttar Pradesh">Uttar Pradesh</option>
<option value="Odisha">Odisha</option>
</select>
</td>
<td>
<input type="button" class="btn btn-success btn-md" value="Filter" id="btnFilter" />
</td>
</tr>
</tbody>
</table>
</div>
@* jQuery DataTables *@
<div style="width:90%; margin:0 auto;">
<table id="myTable" class="table table-responsive table-striped">
<thead>
<tr>
<th style="background-color: Yellow;color: blue">First Name</th>
<th style="background-color: Yellow;color: blue">Last Name</th>
<th style="background-color: Yellow;color: blue">Age</th>
<th style="background-color: Yellow;color: blue">Address</th>
<th style="background-color: Yellow;color: blue">City</th>
<th style="background-color: Yellow;color: blue">State</th>
</tr>
</thead>
</table>
</div>
</div>
@* Load bootstrap datatable css *@
<link href="//cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.1.1/css/bootstrap.css" rel="stylesheet" />
<link href="//cdn.datatables.net/1.10.19/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
@* Load bootstrap datatable js and initialize DataTable *@
@section Scripts {
<script src="//code.jquery.com/jquery-3.3.1.js"></script>
<script src="//cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script src="//cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js"></script>
<script>
$(document).ready(function () {
// jQuery DataTables initialization
$('#myTable').DataTable({
"language": {
"processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"
},
"processing": true,
"serverSide": true,
"orderMulti": false,
"dom": '<"top"i>rt<"bottom"lp><"clear">',
"ajax": {
"url": "/home/FilterData",
"type": "POST",
"datatype": "json"
},
"columns": [
{ "data": "FirstName", "name": "FirstName", "autoWidth": true },
{ "data": "LastName", "name": "LastName", "autoWidth": true },
{ "data": "Age", "name": "Age", "autoWidth": true },
{ "data": "Address", "name": "Address", "autoWidth": true },
{ "data": "City", "name": "City", "autoWidth": true },
{ "data": "State", "name": "State", "autoWidth": true }
]
});
// DataTables filtering on button click
var oTable = $('#myTable').DataTable();
$('#btnFilter').click(function () {
oTable.columns(4).search($('#txtCity').val().trim());
oTable.columns(5).search($('#ddState').val().trim());
oTable.draw();
});
});
</script>
}
Code description
Here, I have added a textbox for City and a dropdown for State to filter the records. I used some static records in the dropdown for basic understanding and in my next session, I will fill the dropdown from the database using Entity Framework.
<div style="background-color:#f5f5f5; padding:20px;">
<h2 style="color: blue;">Filter Records</h2>
<table>
<tbody>
<tr>
<td style="color: blue;">City</td>
<td><input type="text" class="form-control" id="txtCity" /></td>
<td style="color: blue;">State</td>
<td>
<select id="ddState" class="btn btn-primary dropdown-toggle" type="button" data-toggle="dropdown">
<option value="">All States</option>
<option value="Karnataka">Karnataka</option>
<option value="Andhra Pradesh">Andhra Pradesh</option>
<option value="Georgia">Georgia</option>
<option value="Uttar Pradesh">Uttar Pradesh</option>
<option value="Odisha">Odisha</option>
</select>
</td>
<td>
<input type="button" class="btn btn-success btn-md" value="Filter" id="btnFilter" />
</td>
</tr>
</tbody>
</table>
</div>
I have updated the code to implement custom multicolumn server-side filtering in jQuery DataTables by removing the default global search box. This initialization variable allows you to specify where in the DOM you want DataTables to introduce the various controls it composes to the page.
The following piece of code will enable the data loading from server-side. The path "/home/FilterData" is the function that will be returning data from server side. The columns here are the exact names of the properties that we have created in the table and uploaded using the Entity Data Model file. Here, we can get the index number of the table's columns.
$('#myTable').DataTable({
"language": {
"processing": "<div class='overlay custom-loader-background'><i class='fa fa-cog fa-spin custom-loader-color'></i></div>"
},
"processing": true,
"serverSide": true,
"orderMulti": false,
"dom": '<"top"i>rt<"bottom"lp><"clear">',
"ajax": {
"url": "/home/FilterData",
"type": "POST",
"datatype": "json"
},
"columns": [
{ "data": "FirstName", "name": "FirstName", "autoWidth": true }, //index 0
{ "data": "LastName", "name": "LastName", "autoWidth": true }, //index 1
{ "data": "Age", "name": "Age", "autoWidth": true }, //index 2
{ "data": "Address", "name": "Address", "autoWidth": true }, //index 3
{ "data": "City", "name": "City", "autoWidth": true }, //index 4
{ "data": "State", "name": "State", "autoWidth": true } //index 5
]
});
The following piece of code is used to apply custom search on jQuery DataTables. I have applied search for the city name using DataTable column index 4 and search for state name using DataTable column index 5.
oTable = $('#myTable').DataTable();
$('#btnFilter').click(function () {
oTable.columns(4).search($('#txtCity').val().trim());
oTable.columns(5).search($('#ddState').val().trim());
oTable.draw();
});
});
Step 3. Add another action (here "FilterData") for fetching the data from the database and implementing the logic for server-side paging and sorting.
Code
[HttpPost]
public ActionResult FilterData()
{
// Initialization.
JsonResult result = new JsonResult();
try
{
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault()
+ "][name]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
var city = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();
var state = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();
int pageSize = length != null ? Convert.ToInt32(length) : 0;
int skip = start != null ? Convert.ToInt16(start) : 0;
int recordsTotal = 0;
using (SatyaDBEntities dc = new SatyaDBEntities())
{
var v = (from a in dc.employees select a);
if (!string.IsNullOrEmpty(city))
{
v = v.Where(a => a.City.Contains(city));
}
if (!string.IsNullOrEmpty(state))
{
v = v.Where(a => a.State == state);
}
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
v = v.OrderBy(sortColumn + " " + sortColumnDir);
}
recordsTotal = v.Count();
var data = v.Skip(skip).Take(pageSize).ToList();
return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data },
JsonRequestBehavior.AllowGet);
}
}
catch (Exception ex)
{
// Handle exception (log or display error)
Console.WriteLine(ex); // Log exception details to console
}
// Return empty or default result if exception occurs
return result;
}
Code description
I have described the code using the comment line in every line of code. It will be easy for a quick understanding of the code flow. In this piece of code, which is based on searching, sorting, and pagination information sent from the DataTable plugin, the following has been done: The data is being loaded first. It is being churned out based on the search criteria. Data is then sorted by a provided column in a provided order. Lastly, it is paginated and returned.
I have declared two variables which contain the informaion of two columns for filtering records with their index values as I have described in the View section.
var city = Request.Form.GetValues("columns[4][search][value]").FirstOrDefault();
var state = Request.Form.GetValues("columns[5][search][value]").FirstOrDefault();
The following is the piece of code for filtering records using city and state columns.
if (!string.IsNullOrEmpty(city))
{
v = v.Where(a => a.City.Contains(city));
}
if (!string.IsNullOrEmpty(state))
{
v = v.Where(a => a.State == state);
}
For sorting, we need to add a reference of System.Linq.Dynamic.
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
v = v.OrderBy(sortColumn + " " + sortColumnDir);
}
Output
During the initial load, the processing loader will look like below.
![All states]()
Filter records using state dropdown, as shown below.
![Filter records]()
Filter records using city textbox, as shown below.
![Filter records using city]()
Filter records using both, City and State, as shown below.
![Filter records using city]()
Summary
In this write-up, we have learned how to
- Filter records using custom multicolumn server-side features.
- Remove default global search box of jQuery DataTable by using initialisation variable in DOM.
- Get record's length, sorting and pagination information with the DataTable plugin.
- Get Server-side integration of DataTable plugin with ASP.NET MVC 5.