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

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.