Introduction
Today I will demonstrate the implementation of jQuery server side Datatable in MVC appplication with server side filter, sorting and Pagination.
Datatable is a jQuery plugin to display data in tabular format with sorting, filter and pagination written in javascript. Datatable is a flexible tool which allows the customization of the plugin as per our requirement.
Today we will create an MVC application which will be using Datatable plugin with searching, sorting, pagination functionality.
Prerequisites
Basic knowledge of MVC application (Controller, Action and Views) jQuery and Ajax.
Step 1
Create a basic Web Application with MVC framework, build it and launch the application once to check whether everything is configured properly or not.
Step 2
We will create a simple table to display employee detail in datatable. First of all create a new controller (EmployeeController) in your Application.
and create a view for Action Index named as Index.cshtml.
Step 3
Import the CDN (Content Delivery Network) or download the required css and js file. Here I am using CDN for required js and css.
Imported css
Import below css in your view Index.cshtml,
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap.min.css" rel="stylesheet" />
Imported javascript
Import below js in your view Index.cshtml
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap.min.js"></script>
Step 4
Create Model to bind the request data. Create a class file named as JqueryDatatableParam and paste the code as below.
public class JqueryDatatableParam
{
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 iSortCol_0 { get; set; }
public string sSortDir_0 { get; set; }
public int iSortingCols { get; set; }
public string sColumns { get; set; }
}
Step 5
Create Model to bind the request data. Create a class file named as Employee and paste the code as below.
public class Employee
{
public string Name { get; set; }
public string Position { get; set; }
public string Location { get; set; }
public int Age { get; set; }
public DateTime StartDate { get; set; }
public string StartDateString { get; set; }
public int Salary { get; set; }
}
Step 6
We will create an action method to in our EmployeeController to return the data which we will display in view.
Create a method named GetData in EmployeeController as below.
public ActionResult GetData(JqueryDatatableParam param)
{
var employees = GetEmployees(); //This method is returning the IEnumerable employee from database
}
Filter
Add the following code in GetData method to apply server side filter.
if (!string.IsNullOrEmpty(param.sSearch))
{
employeesemployees = employees.Where(x => x.Name.ToLower().Contains(param.sSearch.ToLower())
|| x.Position.ToLower().Contains(param.sSearch.ToLower())
|| x.Location.ToLower().Contains(param.sSearch.ToLower())
|| x.Salary.ToString().Contains(param.sSearch.ToLower())
|| x.Age.ToString().Contains(param.sSearch.ToLower())
|| x.StartDate.ToString("dd'/'MM'/'yyyy").ToLower().Contains(param.sSearch.ToLower())).ToList();
}
From html page whatever keyword we are typing in search textbox is sent to request in querystring as `sSearch` name. In the above code snippet we are checking if the same parameter `sSearch` is not null then we are checking the same keyword in our list to apply filter.
Note
To ignore case sensitive search before comparing we are converting the string to lower case.
Sorting
To apply Sorting add the following code in same GetData method.
var sortColumnIndex = Convert.ToInt32(HttpContext.Request.QueryString["iSortCol_0"]);
var sortDirection = HttpContext.Request.QueryString["sSortDir_0"];
if (sortColumnIndex == 3) {
employees = sortDirection == "asc" ? employees.OrderBy(c => c.Age) : employees.OrderByDescending(c => c.Age);
} else if (sortColumnIndex == 4) {
employees = sortDirection == "asc" ? employees.OrderBy(c => c.StartDate) : employees.OrderByDescending(c => c.StartDate);
} else if (sortColumnIndex == 5) {
employees = sortDirection == "asc" ? employees.OrderBy(c => c.Salary) : employees.OrderByDescending(c => c.Salary);
} else {
Func < Employee, string > orderingFunction = e => sortColumnIndex == 0 ? e.Name : sortColumnIndex == 1 ? e.Position : e.Location;
employees = sortDirection == "asc" ? employees.OrderBy(orderingFunction) : employees.OrderByDescending(orderingFunction);
}
To apply sorting we required two parameters, Sorting direction (Ascendening or descding) and SortColumnName. We are storing the sortdirection value in variable `sortDirection` and sortcolumn no in variable `sortColumnIndex`. And based on sortColumnIndex we are sorting the list.
Pagination
To apply the pagination add the below code in GetData() method.
var displayResult = employees.Skip(param.iDisplayStart)
.Take(param.iDisplayLength).ToList();
var totalRecords = employees.Count();
For pagination also we require two value page numbers and count to display in per page. PageNumber is bound in `iDisplayStart` parameter and per page count is bound in `iDisplayLength` request parameter. And we are using the Take() and Skip() method of linq to perform the pagination based on value of both parameters.
Now add the below code snippet to send filtered and sorted data as response of ajax call in json format.
return Json(new
{
param.sEcho,
iTotalRecords = totalRecords,
iTotalDisplayRecords = totalRecords,
aaData = displayResult
}, JsonRequestBehavior.AllowGet);
Step 6
We have completed our method to perform all operations. Now we have to call this method from view and we have to bind all column values in table. Add the below code snippet in Index.cshtml page.
<script>
$(document).ready(function () {
bindDatatable();
});
function bindDatatable() {
datatable = $('#tblStudent')
.DataTable({
"sAjaxSource": "/Employee/GetData",
"bServerSide": true,
"bProcessing": true,
"bSearchable": true,
"order": [[1, 'asc']],
"language": {
"emptyTable": "No record found.",
"processing":
'<i class="fa fa-spinner fa-spin fa-3x fa-fw" style="color:#2a2b2b;"></i><span class="sr-only">Loading...</span> '
},
"columns": [
{
"data": "Name",
"autoWidth": true,
"searchable": true
},
{
"data": "Position",
"autoWidth": true,
"searchable": true
},
{
"data": "Location",
"autoWidth": true,
"searchable": true
},
{
"data": "Age",
"autoWidth": true,
"searchable": true
}, {
"data": "StartDateString",
"autoWidth": true,
"searchable": true
}, {
"data": "Salary",
"autoWidth": true,
"searchable": true
},
]
});
}
</script>
In the above code snippet we are calling function bindDatatable() on document ready method. and we have defined the bindDatatable method as well.
To convert table to datatable we can use below code snippet based on table id.
$('#tblStudent').DataTable(); //tblStudent is the id of table rendered in dom
Description of other properties that we have set are as below.
- AjaxSource : This attribute is used to set the url to make ajax call for fetching data. Here we have provided the url to call our method GetData from emloyeeController created in step 5.
- ServerSide : This attribute is used to set the value whether datatable is server side or client side.
- order : This attribute is used to set the default order value (i.e SortColumnName and SortDirection)
- columns : This attribute is used to bind all columns.
The full code snippet of both files is as below
Index.Cshtml
<h2>Index</h2>
@section styles{
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/1.10.20/css/dataTables.bootstrap.min.css" rel="stylesheet" />
}
<div class="row">
<div class="col-sm-12">
<table class="table table-bordered table-striped" id="tblStudent">
<thead>
<tr>
<th scope="col">Name</th>
<th scope="col">Position</th>
<th scope="col">Location</th>
<th scope="col">Age</th>
<th scope="col">Start Date</th>
<th scope="col">Salary</th>
</tr>
</thead>
</table>
</div>
</div>
@section scripts{
<script src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.10.20/js/dataTables.bootstrap.min.js"></script>
<script>
$(document).ready(function () {
bindDatatable();
});
function bindDatatable() {
datatable = $('#tblStudent')
.DataTable({
"sAjaxSource": "/Employee/GetData",
"bServerSide": true,
"bProcessing": true,
"bSearchable": true,
"order": [[1, 'asc']],
"language": {
"emptyTable": "No record found.",
"processing":
'<i class="fa fa-spinner fa-spin fa-3x fa-fw" style="color:#2a2b2b;"></i><span class="sr-only">Loading...</span> '
},
"columns": [
{
"data": "Name",
"autoWidth": true,
"searchable": true
},
{
"data": "Position",
"autoWidth": true,
"searchable": true
},
{
"data": "Location",
"autoWidth": true,
"searchable": true
},
{
"data": "Age",
"autoWidth": true,
"searchable": true
}, {
"data": "StartDateString",
"autoWidth": true,
"searchable": true
}, {
"data": "Salary",
"autoWidth": true,
"searchable": true
},
]
});
}
</script>
}
EmployeeController.cs
public class EmployeeController : Controller
{
// GET: Student
public ActionResult Index()
{
return View();
}
public ActionResult GetData(JqueryDatatableParam param)
{
var employees = GetEmployees();
employees.ToList().ForEach(x => x.StartDateString = x.StartDate.ToString("dd'/'MM'/'yyyy"));
if (!string.IsNullOrEmpty(param.sSearch))
{
employees = employees.Where(x => x.Name.ToLower().Contains(param.sSearch.ToLower())
|| x.Position.ToLower().Contains(param.sSearch.ToLower())
|| x.Location.ToLower().Contains(param.sSearch.ToLower())
|| x.Salary.ToString().Contains(param.sSearch.ToLower())
|| x.Age.ToString().Contains(param.sSearch.ToLower())
|| x.StartDate.ToString("dd'/'MM'/'yyyy").ToLower().Contains(param.sSearch.ToLower())).ToList();
}
var sortColumnIndex = Convert.ToInt32(HttpContext.Request.QueryString["iSortCol_0"]);
var sortDirection = HttpContext.Request.QueryString["sSortDir_0"];
if (sortColumnIndex == 3)
{
employees = sortDirection == "asc" ? employees.OrderBy(c => c.Age) : employees.OrderByDescending(c => c.Age);
}
else if (sortColumnIndex == 4)
{
employees = sortDirection == "asc" ? employees.OrderBy(c => c.StartDate) : employees.OrderByDescending(c => c.StartDate);
}
else if (sortColumnIndex == 5)
{
employees = sortDirection == "asc" ? employees.OrderBy(c => c.Salary) : employees.OrderByDescending(c => c.Salary);
}
else
{
Func<Employee, string> orderingFunction = e => sortColumnIndex == 0 ? e.Name :
sortColumnIndex == 1 ? e.Position :
e.Location ;
employees = sortDirection == "asc" ? employees.OrderBy(orderingFunction) : employees.OrderByDescending(orderingFunction);
}
var displayResult = employees.Skip(param.iDisplayStart)
.Take(param.iDisplayLength).ToList();
var totalRecords = employees.Count();
return Json(new
{
param.sEcho,
iTotalRecords = totalRecords,
iTotalDisplayRecords = totalRecords,
aaData = displayResult
}, JsonRequestBehavior.AllowGet);
}
}
Now launch the application and go to the url `Employee/Index` and you will get the following output.
![ASP.NET MVC jQuery Server Side Datatable Example]()
Thanks for reading my article, please let me know your feedback.