ASP.NET MVC jQuery Server Side Datatable Example

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.

15 Comments

Join the conversation! Your thoughts help the community grow.

Tasneem Nomani2y ago
Idisplaylength, idisplastart, where are these set?
Tasneem Nomani2y ago
I tried to implement your code, but there are parts of code missing
karthik lal4y ago
How to pass column filter parameter in ajax call
Nikhil Kurkure4y ago
Can you please share source code?
Nurana Zeynalli4y ago
Hi,Can you share source code with me Really i need it
Brice Hilarion5y ago
Very interesting !
Darwin UM5y ago
How to pass different url ajax to JQuery Datatable to send multiple queries to the same table. for example listAll, listbyrange, listname, etc, and call the table to load the data from those queries.
Harish Penugonda5y ago
One doubt GetEmployee() pulls all the records from database so there will be a performance impact right
- Abhishek Mishra5y ago
  Yes , there will be slight performance impact , If you have more number of records use stored procedure instead of linq query
Srivatshan Br6y ago
Nice article..its working..
Tony Wolf6y ago
There are something still bugging me, the part of GetEmployee() method in controller it say that the method does not exist in current context
- Abhishek Mishra6y ago
  Hi , you have to create method which will read data from database and will return the list , for sake of simplicity , I have not added it here
- Tony Wolf6y ago
  Understood and have resolve that, thanks. But another problem arise, the part of import 2 .css files into the .cshtml have conflict with my shared layout and it return: "The following sections have been defined but have not been rendered for the layout page"
Dan Colgan6y ago
How do I pass a parameter to limit the data initially
nomi ali6y ago
There is an error: DataTables warning (table id = 'tblStudent'): Requested unknown parameter '0' from the data source for row 0