jQuery Datatable server side pagination and sorting in ASP.NET MVC

Introduction

In the previous post of this series, I have shown how to implement jQuery Datatable (basic) in ASP.NET MVC. Here in this article we will see jQuery Datatable server side pagination and sorting in ASP.NET MVC

In the previous article, I have implemented very basic datatable in ASP.NET MVC application where we fetched all the data from a server at once and done pagination, sorting, instant searching at the client side. This can be a performance issue fetching a large amount of data from server side at once. To resolve this performance issue, I am going to show you datatables server-side paging, sorting and filtering in asp.net MVC application.

Here we will see followings with ASP.NET MVC as server side...

Just follow the following steps in order Implement jQuery Datatable server side pagination and sorting in ASP.NET MVC

[Note: If you already visit my previous article and implemented the same, you can start from step 7]

Step-1: Create New Project.

Go to File > New > Project > Select asp.net MVC4 web application > Entry Application Name > Click OK > Select Basic > Select view engine Razor > OK

Step-2: Add a Database.

Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.
Here I have added a database for store some location information in our database for show in the google map.

Step-3: Create a table.

Here I will create 1 table (as below) for store data.
Open Database > Right Click on Table > Add New Table > Add Columns > Save > Enter table name > Ok.

In this example, I have used 1 table as below

Step-4: Add Entity Data Model.

Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.

Step-5: Create a Controller.

Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.

Here I have created a controller "HomeController"

Step-6: Add new action into the controller to get the view where we will implement jQuery DataTable with server-side paging and sorting.

Here I have added "Index" Action into "Home" Controller. Please write this following code

public ActionResult Index()
{
    return View();
}

Step-7: Add view for the action (here "Index") & design.

Right Click on Action Method (here right click on Index action) > Add View... > Enter View Name > Select View Engine (Razor) > Add.
Complete HTML code

@{
    ViewBag.Title = "Index";
}

<h2>Part 2: jQuery DataTable server side pagination and sorting in asp.net MVC</h2>
<div style="width:90%; margin:0 auto;">
    <table id="myTable">
        <thead>
            <tr>
                <th>Employee Name</th>
                <th>Company</th>
                <th>Phone</th>
                <th>Country</th>
                <th>City</th>
                <th>Postal Code</th>
            </tr>
        </thead>
    </table>
</div>

@* Load datatable css *@
<link href="//cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="stylesheet" />
@section Scripts{
    @* Load DataTable js here *@
    <script src="//cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
    <script>
        $(document).ready(function () {
            $("#myTable").DataTable({
                "processing": true, // for show progress bar
                "serverSide": true, // for process server side
                "filter": false, // this is for disable filter (search box)
                "orderMulti": false, // for disable multiple column at once
                "ajax": {
                    "url": "/home/LoadData",
                    "type": "POST",
                    "datatype": "json"
                },
                "columns": [
                        { "data": "ContactName", "name": "ContactName", "autoWidth": true },
                        { "data": "CompanyName", "name": "CompanyName", "autoWidth": true },
                        { "data": "Phone", "name": "Phone", "autoWidth": true },
                        { "data": "Country", "name": "Country", "autoWidth": true },
                        { "data": "City", "name": "City", "autoWidth": true },
                        { "data": "PostalCode", "name": "PostalCode", "autoWidth": true }
                ]
            });
        });
    </script>
}

Step-8: Add reference of System.Linq.Dynamic

Here I have added System.Linq.Dynamic reference from NuGet packages
Go to Solution Explorer > Right click on References > Manage NuGet packages > Search with "System.Linq.Dynamic" > Install.

Step-9: Add another action (here "LoadData") for fetch data from the database and implement logic for server side paging and sorting.

[HttpPost]
        public ActionResult LoadData()
        {

            var draw = Request.Form.GetValues("draw").FirstOrDefault();
            var start = Request.Form.GetValues("start").FirstOrDefault();
            var length = Request.Form.GetValues("length").FirstOrDefault();
            //Find Order Column
            var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
            var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();


            int pageSize = length != null? Convert.ToInt32(length) : 0;
            int skip = start != null ? Convert.ToInt32(start) : 0;
            int recordsTotal = 0;
            using (MyDatatableEntities dc = new MyDatatableEntities())
            {
                // dc.Configuration.LazyLoadingEnabled = false; // if your table is relational, contain foreign key
                var v = (from a in dc.Customers select a);

                //SORT
                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);
            }
        }

Step-10: Run Application.

Download & Live Demo