Server Side Custom Paging In Angular-UI Grid Using Web API

Custom Paging

Custom Paging improves the performance of default paging by retrieving only those records from the database that must be displayed on the particular page of data requested by the user. However, custom paging involves a bit more efforts to implement than that for default paging.

Read the below articles first to understand the AngularJS UI-Grid.

Prerequisites

Visual Studio 2017 is the prerequisite to work with this article.

Thus, let's just use the sections with which we can implement the functionality.

  • Create ASP.NET MVC 5 Application.
  • Adding Model.
  • Scaffolding in MVC 5.
  • View in MVC 5.
  • Log in an Entity Framework.

Create ASP.NET MVC 5 Application

In this section, we'll create an ASP.NET Web Application with the MVC 5 Project Template. Use the procedure given below.

Step 1

Open Visual Studio 2015 and click "New Project".

Step 2

Select "Web" from the left pane and create ASP.NET Web application.

Step 3

Select the MVC Project template in the next ASP.NET wizard.

Visual Studio automatically creates the MVC 5 Application, adds some files and folders to the solution.

Working with Entity Framework

Step 1

Right click on Models folder, click Add New Item, select ADO.NET Entity Data Model from Data template and give a name.

Step 2

Select EF Designer from the database.

Step 3

Make a new connection and select a connection, if you already have a connection.

Step 4

Select tables, view, and stored procedures and click Finish.

Stored Procedure

  1. USE [NORTHWND]  
  2. GO  
  3. /****** Object: StoredProcedure [dbo].[GetEmployeeWithPaging] Script Date: 11/13/2017 12:56:50 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. ALTER PROCEDURE [dbo].[GetEmployeeWithPaging]  
  9. @PageNumber INT = NULL,  
  10. @PageSize INT = NULL,  
  11. @OrderBy VARCHAR(50) = N'EmployeeID', -- Column to sort programs by  
  12. @SortDirection VARCHAR(4) = N'asc'  
  13. AS  
  14. DECLARE @totalCount int  
  15. SET @totalCount = (SELECT COUNT(EmployeeID) as totalRecords FROM Employees)  
  16. SELECT EmployeeID,  
  17. FirstName,  
  18. LastName,  
  19. City,  
  20. Region,  
  21. PostalCode,  
  22. Country,  
  23. Notes,  
  24. @totalCount as TotalCount  
  25. FROM Employees  
  26. ORDER BY  
  27. CASE WHEN @SortDirection = 'asc' AND @OrderBy = 'FirstName' THEN FirstName END ASC  
  28. ,CASE WHEN @SortDirection = 'desc' AND @OrderBy = 'FirstName' THEN FirstName END DESC  
  29. ,CASE WHEN @SortDirection = 'asc' AND @OrderBy = 'LastName' THEN LastName END ASC  
  30. ,CASE WHEN @SortDirection = 'desc' AND @OrderBy = 'LastName' THEN LastName END DESC  
  31. ,CASE WHEN @SortDirection = 'asc' AND @OrderBy = 'City' THEN City END ASC  
  32. ,CASE WHEN @SortDirection = 'desc' AND @OrderBy = 'City' THEN City END DESC  
  33. ,CASE WHEN @SortDirection = 'asc' AND @OrderBy = 'Region' THEN Region END ASC  
  34. ,CASE WHEN @SortDirection = 'desc' AND @OrderBy = 'Region' THEN Region END DESC  
  35. ,CASE WHEN @SortDirection = 'asc' AND @OrderBy = 'PostalCode' THEN PostalCode END ASC  
  36. ,CASE WHEN @SortDirection = 'desc' AND @OrderBy = 'PostalCode' THEN PostalCode END DESC  
  37. ,CASE WHEN @SortDirection = 'asc' AND @OrderBy = 'Country' THEN Country END ASC  
  38. ,CASE WHEN @SortDirection = 'desc' AND @OrderBy = 'Country' THEN Country END DESC  
  39. OFFSET ((@PageNumber-1) * @PageSize) ROWS  
  40. FETCH NEXT @PageSize ROWS ONLY  
Here, you have 2 ways to add Web API class. You can add new Web API controller and select model class and data source that will generate API class with entity framework. Another way is to generate it manually. So in this article, I will create a manual API class.
  1. public partial class GetEmployeeWithPaging_Result {  
  2.     public int EmployeeID {  
  3.         get;  
  4.         set;  
  5.     }  
  6.     public string FirstName {  
  7.         get;  
  8.         set;  
  9.     }  
  10.     public string LastName {  
  11.         get;  
  12.         set;  
  13.     }  
  14.     public string City {  
  15.         get;  
  16.         set;  
  17.     }  
  18.     public string Region {  
  19.         get;  
  20.         set;  
  21.     }  
  22.     public string PostalCode {  
  23.         get;  
  24.         set;  
  25.     }  
  26.     public string Country {  
  27.         get;  
  28.         set;  
  29.     }  
  30.     public string Notes {  
  31.         get;  
  32.         set;  
  33.     }  
  34.     public Nullable < int > TotalCount {  
  35.         get;  
  36.         set;  
  37.     }  
  38. }  
Now, add a builder class.

  1. /// <summary>    
  2. ///    
  3. /// </summary>    
  4. /// <param name="PageNumber"></param>    
  5. /// <param name="PageSize"></param>    
  6. /// <param name="OrderBy"></param>    
  7. /// <param name="SortDirection"></param>    
  8. /// <returns></returns>    
  9. public async Task < List < GetEmployeeWithPaging_Result >> GetEmployeeWithPaging(int PageNumber, int PageSize, string OrderBy, string SortDirection) {  
  10.     try {  
  11.         // Create the SQL param with the id provided by the caller    
  12.         var pageNumber = new SqlParameter("PageNumber", PageNumber == 0 ? (object) DBNull.Value : PageNumber);  
  13.         var pageSize = new SqlParameter("PageSize", PageSize == 0 ? (object) DBNull.Value : PageSize);  
  14.         var orderBy = new SqlParameter("OrderBy", OrderBy == null ? (object) DBNull.Value : OrderBy);  
  15.         var sortDirection = new SqlParameter("SortDirection", SortDirection == null ? (object) DBNull.Value : SortDirection);  
  16.         // Create an enumerated list of SearchProgramOptions objects    
  17.         var result = await db.Database.SqlQuery < GetEmployeeWithPaging_Result > ("GetEmployeeWithPaging @PageNumber,@PageSize,@OrderBy,@SortDirection", pageNumber, pageSize, orderBy, sortDirection).ToListAsync();  
  18.         return result;  
  19.     } catch (Exception ex) {  
  20.         // Something didn't go as expected - this Exception handler should be more verbose    
  21.         throw ex;  
  22.     }  
  23. }  

If you want to use a stored procedure, then read my previous article.

Web API

Right click on controller folder, click Add and select Controller and select Web API 2 Controller – Empty.

  1. [Route("GetEmployeeWithPaging")]  
  2. public async Task < IEnumerable < GetEmployeeWithPaging_Result >> GetEmployeeWithPaging(int PageNumber = 0, int PageSize = 0, string OrderBy = null, string SortDirection = null)  
  3. {  
  4.     return await _employeeVMBuilder.GetEmployeeWithPaging(PageNumber, PageSize, OrderBy, SortDirection);  
Thus, we are done with Entity framework and API Controller here. Now, install the files given below, using "Manage NuGet Package".


Add JavaScript files and CSS reference in BundleConfig.cs.

  1. bundles.Add(new ScriptBundle("~/bundles/angular").Include(  
  2. "~/Scripts/angular.js",  
  3. "~/Scripts/angular-route.js",  
  4. "~/Scripts/ui-grid.js",  
  5. "~/Scripts/angular-ui/ui-bootstrap.js",  
  6. "~/Scripts/angular-ui/ui-bootstrap-tpls.js"));  
  7. bundles.Add(new ScriptBundle("~/bundles/employee").Include(  
  8. "~/Angular/app.js",  
  9. "~/Angular/Services/employeeService.js",  
  10. "~/Angular/Controller/employeeController.js",  
  11. "~/Angular/Controller/editEmployeeController.js",  
  12. "~/Angular/Controller/addEmployeeController.js"));  

And, render on _layout.cshtml.

  1. @Scripts.Render("~/bundles/jquery")  
  2. @Scripts.Render("~/bundles/bootstrap")  
  3. @Scripts.Render("~/bundles/angular")  
  4. @Scripts.Render("~/bundles/employee")  
  5. @RenderSection("scripts", required: false)  

Now, add a new Angular Controller with scope. I am using just one script for Module, Service, and Controller. You can have it separate if working on a big project.

Module

  1. //Define an angular module for our app      
  2. var app = angular.module('app', ['ngRoute''ui.grid''ui.grid.edit''ui.grid.pagination''ui.grid.autoResize''ui.grid.expandable''ui.grid.selection''ui.grid.pinning''ui.bootstrap']).config(function($routeProvider, $locationProvider) {    
  3.     $locationProvider.hashPrefix('');    
  4.     $routeProvider.when('/', {    
  5.         templateUrl: 'Home',    
  6.         controller: 'homeController'    
  7.     }).when('/employee', {    
  8.         templateUrl: 'Employee',    
  9.         controller: 'employeeController'    
  10.     });    
  11.     //$locationProvider.html5Mode(true);      
  12.     //$locationProvider.html5Mode(true).hashPrefix('*');      
  13. });    

Service

  1. //get employees with paging params  
  2. this.getEmployeeWithPaging = function (PageNumber,PageSize,OrderBy,SortDirection) {  
  3. var req = $http.get('api/EmployeeAPI/GetEmployeeWithPaging', { params: { PageNumber:PageNumber,  
  4. PageSize:PageSize,  
  5. OrderBy:OrderBy,  
  6. SortDirection:SortDirection } });  
  7.    return req;  
  8. };  

Controller

  1. app.controller("employeeController"function($scope, $filter, employeeService, $window, $http, $log, $interval, $uibModal) {    
  2.     init();    
  3.     
  4.     function init() {    
  5.         //Pagination varibles      
  6.         var paginationOptions = {    
  7.             pageNumber: 1,    
  8.             pageSize: 10,    
  9.             sort: {    
  10.                 columnName: 'EmployeeID',    
  11.                 isAscending: false,    
  12.             }    
  13.         };    
  14.         $scope.currentPage = 1;    
  15.         $scope.pageSize = paginationOptions.pageSize;    
  16.         //end here      
  17.         $scope.loadData = function() {    
  18.             employeeService.getEmployeeWithPaging(paginationOptions.pageNumber, paginationOptions.pageSize, null, paginationOptions.sort).then(function(result) {    
  19.                 $scope.gridOptions.totalItems = result.data[0].TotalCount;    
  20.                 $scope.totalPage = Math.ceil($scope.gridOptions.totalItems / $scope.pageSize);    
  21.                 $scope.gridOptions.data = result.data;    
  22.                 console.log($scope.Employees);    
  23.             }, function(error) {    
  24.                 $window.alert('Oops! Something went wrong while fetching employee data.');    
  25.             });    
  26.         };    
  27.         $scope.loadData();    
  28.         $scope.gridOptions = {    
  29.             enableRowSelection: true,    
  30.             selectionRowHeaderWidth: 35,    
  31.             enableRowHeaderSelection: false,    
  32.             //Added for custom paging      
  33.             paginationPageSizes: [$scope.pageSize, $scope.pageSize * 2, $scope.pageSize * 3],    
  34.             paginationPageSize: paginationOptions.pageSize,    
  35.             useExternalPagination: true// custom      
  36.             useExternalSorting: true// custom      
  37.             useExternalFiltering: true// custom      
  38.             enableSorting: true,    
  39.             columnDefs: [{    
  40.                 name: 'Edit',    
  41.                 field: 'EmployeeID',    
  42.                 width: '10%',    
  43.                 enableColumnMenu: false,    
  44.                 cellTemplate: '<button title="Edit" class="btn btn-xs btn-primary fa fa-edit" ng-click="grid.appScope.editEmployee(row)">Edit </button>',    
  45.                 width: 50,    
  46.                 pinnedLeft: false,    
  47.                 enableHiding: false,    
  48.                 exporterSuppressExport: true,    
  49.                 enableSorting: false,    
  50.                 enableFiltering: false    
  51.             }, {    
  52.                 name: 'First Name',    
  53.                 field: 'FirstName',    
  54.                 headerCellClass: 'tablesorter-header-inner',    
  55.                 enableFiltering: true,    
  56.                 enableCellEdit: true,    
  57.             }, {    
  58.                 name: 'Last Name',    
  59.                 field: 'LastName',    
  60.                 headerCellClass: 'tablesorter-header-inner',    
  61.                 enableFiltering: true,    
  62.                 enableCellEdit: true,    
  63.             }, {    
  64.                 name: 'City',    
  65.                 field: 'City',    
  66.                 headerCellClass: 'tablesorter-header-inner',    
  67.                 enableFiltering: true,    
  68.                 enableCellEdit: true,    
  69.             }, {    
  70.                 name: 'Region',    
  71.                 field: 'Region',    
  72.                 enableCellEdit: false,    
  73.                 headerCellClass: 'tablesorter-header-inner',    
  74.                 enableFiltering: true    
  75.             }, {    
  76.                 name: 'Postal Code',    
  77.                 field: 'PostalCode',    
  78.                 enableCellEdit: false,    
  79.                 headerCellClass: 'tablesorter-header-inner',    
  80.                 enableFiltering: true    
  81.             }, {    
  82.                 name: 'Country',    
  83.                 field: 'Country',    
  84.                 enableCellEdit: false,    
  85.                 headerCellClass: 'tablesorter-header-inner',    
  86.                 enableFiltering: true    
  87.             }, {    
  88.                 name: 'Notes',    
  89.                 field: 'Notes',    
  90.                 width: '20%',    
  91.                 enableCellEdit: false,    
  92.                 headerCellClass: 'tablesorter-header-inner',    
  93.                 enableFiltering: true    
  94.             }],    
  95.             //This code used for export grid data in csv file      
  96.             enableGridMenu: true,    
  97.             enableSelectAll: true,    
  98.             exporterMenuPdf: false,    
  99.             enableFiltering: true,    
  100.             exporterCsvFilename: 'EmployeeList_' + $filter('date')(new Date(), 'MM/dd/yyyy') + '.csv',    
  101.             exporterCsvLinkElement: angular.element(document.querySelectorAll(".custom-csv-link-location")),    
  102.             onRegisterApi: function(gridApi) {    
  103.                 $scope.gridApi = gridApi;    
  104.                 gridApi.selection.on.rowSelectionChanged($scope, function(row) {    
  105.                     var msg = 'row selected ' + row.isSelected;    
  106.                     $log.log(msg);    
  107.                     console.log(msg);    
  108.                     //$window.alert(msg);      
  109.                 });    
  110.                 gridApi.selection.on.rowSelectionChangedBatch($scope, function(rows) {    
  111.                     var msg = 'rows changed ' + rows.length;    
  112.                     $log.log(msg);    
  113.                     // $window.alert(msg);      
  114.                     console.log(msg);    
  115.                 });    
  116.                 //Added for custom paging      
  117.                 gridApi.pagination.on.paginationChanged($scope, function(newPage, pageSize) {    
  118.                     paginationOptions.pageNumber = newPage;    
  119.                     paginationOptions.pageSize = pageSize;    
  120.                     $scope.pageSize = pageSize;    
  121.                     $scope.currentPage = newPage;    
  122.                     $scope.totalPage = Math.ceil($scope.gridOptions.totalItems / $scope.pageSize);    
  123.                     $scope.loadData();    
  124.                 });    
  125.                 //custom sort      
  126.                 $scope.gridApi.core.on.sortChanged($scope, function(grid, sortColumns) {    
  127.                     if (sortColumns.length == 0) {    
  128.                         paginationOptions.sort = null;    
  129.                     } else {    
  130.                         paginationOptions.sort = sortColumns[0].sort.direction;    
  131.                     }    
  132.                     $scope.loadData();    
  133.                 });    
  134.             },    
  135.             //end here      
  136.             //data for grid      
  137.             // data: 'employees'      
  138.         };    
  139.     }    
  140.     //Delete employee      
  141.     $scope.deleteSelected = function() {    
  142.         angular.forEach($scope.gridApi.selection.getSelectedRows(), function(data, index) {    
  143.             $scope.employees.splice($scope.employees.lastIndexOf(data), 1);    
  144.         });    
  145.     }*/      
  146.     //This function is used to open update request popup window      
  147.     $scope.addEmployee = function() {    
  148.         var modalInstance = $uibModal.open({    
  149.             //animation: $ctrl.animationsEnabled,      
  150.             templateUrl: 'Template/add.html',    
  151.             controller: 'addEmployeeController'// a controller for modal instance      
  152.             //controllerUrl: 'controller/test-controller', // can specify controller url path      
  153.             //controllerAs: 'ctrl', // controller as syntax      
  154.             //scope: $scope,      
  155.             size: 'md'// model size      
  156.             backdrop: 'static',    
  157.             keyboard: false// ESC key close enable/disable      
  158.             //appendTo: parentElem,      
  159.             resolve: {    
  160.                 //grid: function () { return grid; },      
  161.                 //refreshGrid: function () { return $scope.RefreshGrid.bind(controller, $scope.searchData); },      
  162.                 //row: function () { return row.entity; }      
  163.                 row: function() {    
  164.                     return null;    
  165.                 }    
  166.             } // data passed to the controller      
  167.         }).closed.then(function() {    
  168.             $scope.RefreshGridData();    
  169.             $scope.showGrid = true;    
  170.         }, function() {});    
  171.     };    
  172.     //Open edit dialog      
  173.     $scope.editEmployee = function(row) {    
  174.         var modalInstance = $uibModal.open({    
  175.             //animation: $ctrl.animationsEnabled,      
  176.             templateUrl: 'Template/edit.html',    
  177.             controller: 'editEmployeeController'// a controller for modal instance      
  178.             //controllerUrl: 'controller/test-controller', // can specify controller url path      
  179.             //controllerAs: 'ctrl', // controller as syntax      
  180.             //scope: $scope,      
  181.             size: 'md'// model size      
  182.             backdrop: 'static',    
  183.             keyboard: false// ESC key close enable/disable      
  184.             //appendTo: parentElem,      
  185.             resolve: {    
  186.                 //grid: function () { return grid; },      
  187.                 //refreshGrid: function () { return $scope.RefreshGrid.bind(controller, $scope.searchData); },      
  188.                 row: function() {    
  189.                     return row.entity;    
  190.                 }    
  191.             } // data passed to the controller      
  192.         }).closed.then(function() {    
  193.             $scope.RefreshGridData();    
  194.             $scope.showGrid = true;    
  195.         }, function() {});    
  196.     }    
  197.     //refresh grid data after save of update      
  198.     $scope.RefreshGridData = function() {    
  199.         /* employeeService.getEmployees().then(function (result) {   
  200.         $scope.gridOptions.data = result.data;   
  201.         console.log($scope.Employees);   
  202.         }, function (error) {   
  203.         $window.alert('Oops! Something went wrong while fetching employee data.');   
  204.         }); */    
  205.         $scope.loadData();    
  206.     }    
  207.     //end      
  208. });   

Index

  1. @{      
  2.     ViewBag.Title = "Index";      
  3.     Layout = "~/Views/Shared/_Layout.cshtml";      
  4. }      
  5. <h2>Employee</h2>      
  6. <div ng-controller="employeeController">      
  7. <div style="padding-left: 30px;">      
  8.     <button type="button" id="addRow" class="btn btn-success" ng-click="addEmployee()">Add New Employee</button>      
  9. </div>      
  10. <br />      
  11. <div ui-grid="gridOptions"      
  12.     ui-grid-pagination      
  13.     ui-grid-selection      
  14.     ui-grid-exporter      
  15.     ui-grid-resize-columns      
  16.     ui-grid-auto-resize      
  17.     class="grid">      
  18. </div>      
  19. </div>     
As everything is done, run the application.


In the given screenshot, you can see the page index and page size. Click "Next" to see the next page index. Best way to check page index debug your application in debug mode. Every time when you press next you pass updated page index and page size and sorting order. The biggest benefit of server-side custom paging is when you have millions of records and loading is slow then go with custom paging, after custom paging no matter how big database is, you will get result fast because you are fetching 10 records each time.

Conclusion

In this article, we have seen how to implement server-side custom paging with Angular UI-Grid with Web API and Entity Framework in MVC. If you have any questions or comments, drop me a line in the comments section.

In the given screenshot, you can see the page index and page size. Click "Next" to see the next page index. Best way to check page index debug your application in debug mode. Every time when you press next you pass updated page index and page size and sorting order. The biggest benefit of server-side custom paging is when you have millions of records and loading is slow then go with custom paging, after custom paging no matter how big database is, you will get result fast because you are fetching 10 records each time.

Conclusion

In this article, we have seen how to implement server-side custom paging with Angular UI-Grid with Web API and Entity Framework in MVC. If you have any questions or comments, drop me a line in the comments section.

Become a master of the Rubik's Cube with this tutorial. Learn how to solve the cube with the beginner's method!