Part 2 - Datatables server-side paging, sorting and filtering in angularjs



Introduction

In the previous article, I have explained how to Implement datatables in AngularJS and asp.net mvc. Today we will see datatables server-side paging, sorting and filtering in AngularJS and asp.net MVC.

In the previous article, I have implemented very basic datatable in angularjs 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 AngularJS and asp.net MVC application in this article.

Just follow the following steps in order to implement Datatables server-side paging, sorting and filtering in angularjs and asp.net mvc application.

[Note: If you already visited my previous article and implemented the same, you have to just follow  Step-8 (myApp.js file code updated), Step-12 (add reference) and Step-13 (server-side action code for sorting, paging and filtering) ]
Here In this article, I have used Visual Studio 2013

Step-1: Create New Project.

Go to File > New > Project > ASP.NET  Web Application (under web) > Entry Application Name > Click OK > Select Empty template > Checked MVC (under "Add folders and core references for" option) > 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.

Step-3: Create a table.

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: Add required js (dependencies of angular-datatables)  files in the application.

Here I have created a folder named "Scripts" first, and then I have added dependencies js files of angular-datatables.
Right Click on your solution file (from solution explorer) > Add > New Folder > Renamed your folder (here I have renamed as "Scripts"). and then
Right click on your folder (just created) > Add > Existing Item > select  dependencies js files file > Add. (Download)

Step-6: Add required css files in the application.

Here I have created a folder named "css" first, and then I have added 2 css files.
Right Click on your solution file (from solution explorer) > Add > New Folder > Renamed your folder (here I have renamed as "css"). and then
Right click on your folder (just created) > Add > Existing Item > select  css files file > Add. (Download)

Step-7: Add 3 image files for sorting icons.

Same way I have added a folder named "images" here and then I have added 3 image files for sorting icon.
Right Click on your solution file (from solution explorer) > Add > New Folder > Renamed your folder (here I have renamed as "Images"). and then
Right click on your folder (just created) > Add > Existing Item > select  image files file > Add. (Download)

Solution Explorer

Step-8: Create a javascript file for angular components.

Here I have added a javascript file in the "Scripts" folder for add angular components (module, controller etc).

Right click on your "Scripts" folder > Add > New Item > select "javascript" file > Enter name (here "myApp.js")> Ok.
Write following code
var app = angular.module('MyApp', ['datatables']);
app.controller('homeCtrl', ['$scope', '$http', 'DTOptionsBuilder', 'DTColumnBuilder',
    function ($scope, $http, DTOptionsBuilder, DTColumnBuilder) {
        $scope.dtColumns = [
            //here We will add .withOption('name','column_name') for send column name to the server 
            DTColumnBuilder.newColumn("CustomerID", "Customer ID").withOption('name', 'CustomerID'),
            DTColumnBuilder.newColumn("CompanyName", "Company Name").withOption('name', 'CompanyName'),
            DTColumnBuilder.newColumn("ContactName", "Contact Name").withOption('name', 'ContactName'),
            DTColumnBuilder.newColumn("Phone", "Phone").withOption('name', 'Phone'),
            DTColumnBuilder.newColumn("City", "City").withOption('name', 'City')
        ]

        $scope.dtOptions = DTOptionsBuilder.newOptions().withOption('ajax', {
            dataSrc: "data",
            url: "/home/getdata",
            type:"POST"
        })
        .withOption('processing', true) //for show progress bar
        .withOption('serverSide', true) // for server side processing
        .withPaginationType('full_numbers') // for get full pagination options // first / last / prev / next and page numbers
        .withDisplayLength(10) // Page size
        .withOption('aaSorting',[0,'asc']) // for default sorting column // here 0 means first column
    }])
you can see here I have included the datatables module in our module (here "MyApp") for implement datatables in our angularjs application.

If you already visited the previous article about implement datatables in angularjs application then you can see here in this step I have added .withOption("name","column_name") with each column definition for send column name to the server for server-side processing.  

Step-9: 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 named "HomeController"

Step-10: Add new action into your controller for getting the view, where we will show data in datatables. 

Here I have added "Index" Action into "Home" Controller. Please write this following code
public ActionResult Index()
{
    return View();
}

Step-11: Add view for your Action & design for showing data in datatables.

Right Click on Action Method (here right click on Index action) > Add View... > Enter View Name > Select View Engine (Razor) > Add.
HTML Code
@{
    ViewBag.Title = "Index";
}
<h2>Index</h2>
@* CSS *@
<link href="~/css/bootstrap.css" rel="stylesheet" />
<link href="~/css/jquery.dataTables.min.css" rel="stylesheet" />
@* JS for angularJS and Datatable *@
<script src="~/Scripts/jquery.js"></script>
<script src="~/Scripts/jquery.dataTables.js"></script>
<script src="~/Scripts/angular.js"></script>
<script src="~/Scripts/angular-datatables.js"></script>
@* JS for our angularjs module, controller etc. *@
<script src="~/Scripts/myApp.js"></script>
@* HTML *@

<div ng-app="MyApp" class="container">
    <div ng-controller="homeCtrl">
        <table id="entry-grid" datatable="" dt-options="dtOptions" dt-columns="dtColumns" class="table table-hover"></table> 
    </div>
</div>

Step-12: 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-13: Add an another action into your controller for getting data from a database for showing in datatables.

public ActionResult getData()
{
    //Datatable parameter
    var draw = Request.Form.GetValues("draw").FirstOrDefault();
    //paging parameter
    var start = Request.Form.GetValues("start").FirstOrDefault();
    var length = Request.Form.GetValues("length").FirstOrDefault();
    //sorting parameter
    var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
    var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
    //filter parameter
    var searchValue = Request.Form.GetValues("search[value]").FirstOrDefault();
    List<Customer> allCustomer = new List<Customer>();
    int pageSize = length != null ? Convert.ToInt32(length) : 0;
    int skip = start != null ? Convert.ToInt32(start) : 0;
    int recordsTotal = 0;
    //Database query
    using (MyDatabaseEntities dc = new MyDatabaseEntities())
    {
        var v = (from a in dc.Customers select a);
        //search
        if (!string.IsNullOrEmpty(searchValue))
        {
            v = v.Where(a => 
                a.CustomerID.Contains(searchValue) ||
                a.CompanyName.Contains(searchValue) ||
                a.ContactName.Contains(searchValue) || 
                a.Phone.Contains(searchValue) ||
                a.City.Contains(searchValue)
                );
        }

        //sort
        if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
        {
            //for make sort simpler we will add Syste.Linq.Dynamic reference
            v = v.OrderBy(sortColumn + " " + sortColumnDir);
        }

        recordsTotal = v.Count();
        allCustomer = v.Skip(skip).Take(pageSize).ToList();
    }

    return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = allCustomer });
}

Step-14: Run application.


Hello ! My name is Sourav Mondal. I am a software developer working in Microsoft .NET technologies since 2010.

I like to share my working experience, research and knowledge through my site.

I love developing applications in Microsoft Technologies including Asp.Net webforms, mvc, winforms, c#.net, sql server, entity framework, Ajax, Jquery, web api, web service and more.