Post
Ask Question

Server Side Pagination Using AngularJS, Web API And SQL Server

Server side pagination is very useful when we are dealing with huge amounts of data. At that time client side pagination will take a long time to get all the data at the same time, so it's better to make a Server call on every page request.

We are going to see all employee data with pagination and we will make a Server call on every page request.

Also, we are going to use Web API for HTTP Service call and will use ADO.NET to access the data from the database. Our database will be SQL Server and we are using AngularJS and ui-bootstrap library to make UI ready.

Let's start with a sample Application for better understanding. We are going to use Visual Studio 2015 for this example.

First, open the Visual Studio and click File -> New - > Project, as shown below.

AngularJS

Now, select ASP.NET Web Application and enter the name as ServerSidePaginationInAngularJsAndWebAPI and click OK, as shown below.

AngularJS

Now, select Empty from a template and check Web API from check box list and click OK, as shown below.

AngularJS

Now, our Web API project is ready. Right click on Controller folder and click Add -> Controller from Solution Explorer, as shown below.

AngularJS

Select Web API 2 Controller - Empty and click Add button, as shown below.

AngularJS

Give the Controller name as EmployeeController and click Add button, as shown below.

AngularJS

Our Controller is ready for writing HTTP action methods. We will write GET Action method later in this article.

Now, right click on Models folder and add click Add -> Class, as shown below.

AngularJS

Give the class name Employee.cs, as shown below.

AngularJS

Write all the properties in Employee.cs class, as shown below. 

  1. namespace ServerSidePaginationInAngularJsAndWebAPI.Models  
  2. {  
  3.     public class Employee  
  4.     {  
  5.         public string Id { get; set; }  
  6.         public string Name { get; set; }  
  7.         public string Email { get; set; }  
  8.         public string Address { get; set; }  
  9.     }  
  10. }   

In a similar way, add one more class as EmployeeList.cs to get the employee list and total count for pagination details, as shown below. 

  1. using System.Collections.Generic;  
  2.   
  3. namespace ServerSidePaginationInAngularJsAndWebAPI.Models  
  4. {  
  5.     public class EmployeeList  
  6.     {  
  7.         public List<Employee> employees { get; set; }  
  8.         public string totalCount { get; set; }  
  9.     }  
  10. }   

Now, add one more folder for database operation as DBOperation and inside this folder, add class as EmployeeInfo.cs and write the code snippet given below. 

  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Data.SqlClient;  
  4. using System.Data;  
  5. using System.Configuration;  
  6. using ServerSidePaginationInAngularJsAndWebAPI.Models;  
  7.   
  8.   
  9. namespace ServerSidePaginationInAngularJsAndWebAPI.DBOperation  
  10. {  
  11.     public class EmployeeInfo  
  12.     {  
  13.         public EmployeeList GetEmployees(int pageIndex, int pageSize)  
  14.         {  
  15.             EmployeeList employeeList = new EmployeeList();  
  16.               
  17.             using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))  
  18.             {  
  19.                 SqlCommand cmd = new SqlCommand("GetEmployees", connection);  
  20.                 cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;  
  21.                 cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;  
  22.                 cmd.CommandType = CommandType.StoredProcedure;  
  23.                 try  
  24.                 {  
  25.                     connection.Open();  
  26.                     SqlDataReader dr = cmd.ExecuteReader();  
  27.                     List<Employee> listEmp = new List<Employee>();  
  28.                     while (dr.Read())  
  29.                     {  
  30.                         Employee emp = new Employee();  
  31.                         emp.Id = dr["id"].ToString();  
  32.                         emp.Name = dr["name"].ToString();  
  33.                         emp.Email = dr["email"].ToString();  
  34.                         emp.Address = dr["address"].ToString();  
  35.                         listEmp.Add(emp);  
  36.                     }  
  37.   
  38.                     dr.NextResult();  
  39.   
  40.                     while (dr.Read())  
  41.                     {  
  42.                         employeeList.totalCount = dr["totalCount"].ToString();  
  43.                     }  
  44.                     employeeList.employees = listEmp;  
  45.                 }  
  46.                 catch (Exception ex)  
  47.                 {  
  48.                     throw;  
  49.                 }  
  50.   
  51.             }  
  52.             return employeeList;  
  53.         }  
  54.     }  
  55. }   

Inside this class, we are using ADO.NET to make a SQL Server database call and retrieve all employee information and the total count of the employees.

Now, time to write our GET Action in the controller has come, where you can get the employees information from GetEmployees methods.

The code snippet for Web API Controller is given below. 

  1. using System.Web.Http;  
  2. using ServerSidePaginationInAngularJsAndWebAPI.Models;  
  3. using ServerSidePaginationInAngularJsAndWebAPI.DBOperation;  
  4.   
  5. namespace ServerSidePaginationInAngularJsAndWebAPI.Controllers  
  6. {  
  7.     public class EmployeeController : ApiController  
  8.     {  
  9.         public EmployeeList GetEmployees(int pageIndex, int pageSize)  
  10.         {  
  11.             EmployeeInfo empInfo = new EmployeeInfo();  
  12.             EmployeeList empList = empInfo.GetEmployees(pageIndex, pageSize);  
  13.             return empList;  
  14.         }  
  15.     }  
  16. }   

In the code given above, we are using two parameters as pageIndex and pageSize to get the current page information and a total number of employees to be displayed respectively.

Now, it’s time to create a database, table and stored procedure.

Create a database

We are going to create a database named as sample.

The query is given below to create a database in our SQL Server. 

  1. USE [master]  
  2. GO  
  3.   
  4. CREATE DATABASE [sample]  
  5.  CONTAINMENT = NONE  
  6.  ON  PRIMARY   
  7. ( NAME = N'sample', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\sample.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )  
  8.  LOG ON   
  9. ( NAME = N'sample_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\sample_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )  
  10. GO  
  11.   
  12. ALTER DATABASE [sample] SET COMPATIBILITY_LEVEL = 130  
  13. GO  
  14.   
  15. IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
  16. begin  
  17. EXEC [sample].[dbo].[sp_fulltext_database] @action = 'enable'  
  18. end  
  19. GO  
  20.   
  21. ALTER DATABASE [sample] SET ANSI_NULL_DEFAULT OFF   
  22. GO  
  23.   
  24. ALTER DATABASE [sample] SET ANSI_NULLS OFF   
  25. GO  
  26.   
  27. ALTER DATABASE [sample] SET ANSI_PADDING OFF   
  28. GO  
  29.   
  30. ALTER DATABASE [sample] SET ANSI_WARNINGS OFF   
  31. GO  
  32.   
  33. ALTER DATABASE [sample] SET ARITHABORT OFF   
  34. GO  
  35.   
  36. ALTER DATABASE [sample] SET AUTO_CLOSE OFF   
  37. GO  
  38.   
  39. ALTER DATABASE [sample] SET AUTO_SHRINK OFF   
  40. GO  
  41.   
  42. ALTER DATABASE [sample] SET AUTO_UPDATE_STATISTICS ON   
  43. GO  
  44.   
  45. ALTER DATABASE [sample] SET CURSOR_CLOSE_ON_COMMIT OFF   
  46. GO  
  47.   
  48. ALTER DATABASE [sample] SET CURSOR_DEFAULT  GLOBAL   
  49. GO  
  50.   
  51. ALTER DATABASE [sample] SET CONCAT_NULL_YIELDS_NULL OFF   
  52. GO  
  53.   
  54. ALTER DATABASE [sample] SET NUMERIC_ROUNDABORT OFF   
  55. GO  
  56.   
  57. ALTER DATABASE [sample] SET QUOTED_IDENTIFIER OFF   
  58. GO  
  59.   
  60. ALTER DATABASE [sample] SET RECURSIVE_TRIGGERS OFF   
  61. GO  
  62.   
  63. ALTER DATABASE [sample] SET  DISABLE_BROKER   
  64. GO  
  65.   
  66. ALTER DATABASE [sample] SET AUTO_UPDATE_STATISTICS_ASYNC OFF   
  67. GO  
  68.   
  69. ALTER DATABASE [sample] SET DATE_CORRELATION_OPTIMIZATION OFF   
  70. GO  
  71.   
  72. ALTER DATABASE [sample] SET TRUSTWORTHY OFF   
  73. GO  
  74.   
  75. ALTER DATABASE [sample] SET ALLOW_SNAPSHOT_ISOLATION OFF   
  76. GO  
  77.   
  78. ALTER DATABASE [sample] SET PARAMETERIZATION SIMPLE   
  79. GO  
  80.   
  81. ALTER DATABASE [sample] SET READ_COMMITTED_SNAPSHOT OFF   
  82. GO  
  83.   
  84. ALTER DATABASE [sample] SET HONOR_BROKER_PRIORITY OFF   
  85. GO  
  86.   
  87. ALTER DATABASE [sample] SET RECOVERY SIMPLE   
  88. GO  
  89.   
  90. ALTER DATABASE [sample] SET  MULTI_USER   
  91. GO  
  92.   
  93. ALTER DATABASE [sample] SET PAGE_VERIFY CHECKSUM    
  94. GO  
  95.   
  96. ALTER DATABASE [sample] SET DB_CHAINING OFF   
  97. GO  
  98.   
  99. ALTER DATABASE [sample] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )   
  100. GO  
  101.   
  102. ALTER DATABASE [sample] SET TARGET_RECOVERY_TIME = 60 SECONDS   
  103. GO  
  104.   
  105. ALTER DATABASE [sample] SET DELAYED_DURABILITY = DISABLED   
  106. GO  
  107.   
  108. ALTER DATABASE [sample] SET QUERY_STORE = OFF  
  109. GO  
  110.   
  111. USE [sample]  
  112. GO  
  113.   
  114. ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;  
  115. GO  
  116.   
  117. ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;  
  118. GO  
  119.   
  120. ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
  121. GO  
  122.   
  123. ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;  
  124. GO  
  125.   
  126. ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;  
  127. GO  
  128.   
  129. ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;  
  130. GO  
  131.   
  132. ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;  
  133. GO  
  134.   
  135. ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;  
  136. GO  
  137.   
  138. ALTER DATABASE [sample] SET  READ_WRITE   
  139. GO   

Creating a table

Now, we are going to create a table named as an employee.

The query is given below to create a table in the database. 

  1. USE [sample]  
  2. GO  
  3.   
  4. SET ANSI_NULLS ON  
  5. GO  
  6.   
  7. SET QUOTED_IDENTIFIER ON  
  8. GO  
  9.   
  10. CREATE TABLE [dbo].[employee](  
  11.     [id] [int] IDENTITY(1,1) NOT NULL,  
  12.     [name] [nvarchar](50) NULL,  
  13.     [email] [nvarchar](max) NULL,  
  14.     [address] [nvarchar](max) NULL,  
  15.  CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED   
  16. (  
  17.     [id] ASC  
  18. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
  19. ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  
  20.   
  21. GO   

Now, insert dummy values in our created employee table, as shown below. 

  1. USE [sample]  
  2. GO  
  3. SET IDENTITY_INSERT [dbo].[employee] ON   
  4.   
  5. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (1, N'Vivek', N'[email protected]', N'Hyderabad')  
  6. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (2, N'Ranjeet', N'[email protected]', N'Hyderabad')  
  7. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (3, N'Sunil', N'[email protected]', N'Hyderabad')  
  8. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (4, N'Ganesh', N'[email protected]', N'Hyderabad')  
  9. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (5, N'Subhadip', N'[email protected]', N'Hyderabad')  
  10. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (6, N'Vijay', N'[email protected]', N'Hyderabad')  
  11. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (7, N'Gajanan', N'[email protected]', N'Hyderabad')  
  12. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (8, N'Santosh', N'[email protected]', N'Hyderabad')  
  13. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (9, N'Praveen', N'[email protected]', N'Hyderabad')  
  14. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (10, N'Suresh', N'[email protected]', N'Hyderabad')  
  15. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (11, N'Priya', N'[email protected]', N'Hyderabad')  
  16. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (12, N'Sharath', N'[email protected]', N'Hyderabad')  
  17. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (13, N'Nishu', N'[email protected]', N'Hyderabad')  
  18. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (14, N'Mukesh', N'[email protected]', N'Hyderabad')  
  19. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (15, N'Raghavendra', N'[email protected]', N'Hyderabad')  
  20. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (16, N'Ashish', N'[email protected]', N'Hyderabad')  
  21. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (17, N'Saroj', N'[email protected]', N'Hyderabad')  
  22. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (18, N'Sarthak', N'[email protected]', N'Hyderabad')  
  23. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (19, N'Rajeev', N'[email protected]', N'Hyderabad')  
  24. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (20, N'Kaveri', N'[email protected]', N'Hyderabad')  
  25. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (21, N'Sunil', N'[email protected]', N'Hyderabad')  
  26. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (22, N'Nagalaxmi', N'[email protected]', N'Hyderabad')  
  27. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (23, N'Anusha', N'[email protected]', N'Hyderabad')  
  28. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (24, N'Vicky', N'[email protected]', N'Hyderabad')  
  29. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (25, N'Anu', N'[email protected]', N'Hyderabad')  
  30. INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (26, N'Divya', N'[email protected]', N'Hyderabad')  
  31. SET IDENTITY_INSERT [dbo].[employee] OFF   

Create a stored procedure

We are going to use GetEmployees stored procedure to get all the employee information from Web API.

The script is given below to create a stored procedure. 

  1. USE [sample]  
  2. GO  
  3.   
  4. SET ANSI_NULLS ON  
  5. GO  
  6.   
  7. SET QUOTED_IDENTIFIER ON  
  8. GO  
  9.   
  10.   
  11. CREATE Procedure [dbo].[GetEmployees]  
  12. (  
  13.  @PageIndex INT,  
  14.  @pageSize INT   
  15. )   
  16. As  
  17.  Begin  
  18.  SELECT * FROM employee ORDER BY id OFFSET @PageSize*(@PageIndex-1) ROWS FETCH NEXT @PageSize ROWS ONLY;  
  19.   
  20.  SELECT count(*) as totalCount FROM employee;  
  21.  End  
  22. GO   

Now, It’s time for UI coding.

Right click on the project from Solution Explorer and click add -> New Folder and give the name as UI.

Similarly, inside the UI folder, create one more folder named as Resources and keep the angular.js, ui-bootstrap-tpls-0.13.4.min.js and bootstrap.min.css files for our use.

Create HTML page named as Index.html and JavaScript file named as Index.js files.

Our solution structure looks, as shown below.

AngularJS

Here, the complete code for index.html page is given below.

Complete code for HTML 

  1. <!DOCTYPE html>  
  2. <html>  
  3. <head>  
  4.     <title>Employee List</title>  
  5.     <script src="Resources/angular.js"></script>  
  6.     <script src="Resources/ui-bootstrap-tpls-0.13.4.min.js"></script>  
  7.     <script src="Index.js"></script>  
  8.     <link href="Resources/bootstrap.min.css" rel="stylesheet" />  
  9. </head>  
  10.   
  11. <body>  
  12.     <div ng-app="employeeApp" ng-controller="employeeCtrl">  
  13.   
  14.         <div class="container">  
  15.             <div class="row">  
  16.                 <h1>Employee List</h1>  
  17.                 <div class="table-responsive">  
  18.                     <table class="table table-striped table-bordered table-hover tabel-condensed">  
  19.                         <thead>  
  20.                             <tr>  
  21.                                 <th>Id</th>  
  22.                                 <th>Name</th>  
  23.                                 <th>Email</th>  
  24.                                 <th>Address</th>  
  25.                             </tr>  
  26.                         </thead>  
  27.                         <tbody>  
  28.                             <tr ng-repeat="employee in employees">  
  29.                                 <td>{{employee.Id}}</td>  
  30.                                 <td>{{employee.Name}}</td>  
  31.                                 <td>{{employee.Email}}</td>  
  32.                                 <td>{{employee.Address}}</td>  
  33.                             </tr>  
  34.                         </tbody>  
  35.                         <tfoot>  
  36.                             <tr>  
  37.                                 <td align="center" colspan="6   ">  
  38.                                     <span class="form-group pull-left page-size form-inline">  
  39.                                         <select id="ddlPageSize" class="form-control control-color"  
  40.                                                 ng-model="pageSizeSelected"  
  41.                                                 ng-change="changePageSize()">  
  42.                                             <option value="5">5</option>  
  43.                                             <option value="10">10</option>  
  44.                                             <option value="25">25</option>  
  45.                                             <option value="50">50</option>  
  46.                                         </select>  
  47.                                     </span>  
  48.                                     <div class="pull-right">  
  49.                                         <pagination total-items="totalCount" ng-change="pageChanged()" items-per-page="pageSizeSelected" direction-links="true" ng-model="pageIndex" max-size="maxSize" class="pagination" boundary-links="true" rotate="false" num-pages="numPages"></pagination>  
  50.                                         <a class="btn btn-primary">Page: {{pageIndex}} / {{numPages}}</a>  
  51.                                     </div>  
  52.                                 </td>  
  53.                             </tr>  
  54.                         </tfoot>  
  55.                     </table>  
  56.                 </div>  
  57.             </div>  
  58.         </div>  
  59.     </div>  
  60. </body>  
  61. </html>   

In the code given above, the main thing is that we have to understand is pagination element and its attributes.

Below is the brief description for the attributes of pagination element.

  • total-items - Total number of items in all the pages.
  • items-per-page (Defaults: 10) - Maximum number of items per page. A value less than one indicates all the items on one page.
  • max-size (Defaults: null) - Limit number for pagination display number.
  • ng-change - It can be used together with ng-model to call a function whenever the page changes.
  • num-pages - It is read-only and an optional expression assigns the total number of pages to display.
  • rotate (Defaults: true) - Whether to keep the current page in the middle of the visible ones.
  • direction-links(Default: true) - Whether to display Previous/ Next buttons.
  • boundary-links (Default: false) - Whether to display First/ Last buttons.
  • ng-model - Current page number.
  • first-text (Default: First) - Text for First button.
  • last-text (Default: Last) - Text for Last button.
  • previous-text (Default: Previous) - Text for Previous button.
  • next-text (Default: Next) - Text for Next button.

Here, I didn’t use first-text, last-text, previous-text and next-text attributes because we are going to use default value for them.

Now, we are going to write the code for index.js page.

Complete code for AngularJS. 

  1. var app = angular.module('employeeApp', ['ui.bootstrap']);  
  2.   
  3. app.controller('employeeCtrl', function ($scope, $http) {  
  4.   
  5.     $scope.maxSize = 5;     // Limit number for pagination display number.  
  6.     $scope.totalCount = 0;  // Total number of items in all pages. initialize as a zero  
  7.     $scope.pageIndex = 1;   // Current page number. First page is 1.-->  
  8.     $scope.pageSizeSelected = 5; // Maximum number of items per page.  
  9.   
  10.     $scope.getEmployeeList = function () {  
  11.         $http.get("http://localhost:52859/api/Employee?pageIndex=" + $scope.pageIndex + "&pageSize=" + $scope.pageSizeSelected).then(  
  12.                        function (response) {  
  13.                            $scope.employees = response.data.employees;  
  14.                            $scope.totalCount = response.data.totalCount;  
  15.                        },  
  16.                        function (err) {  
  17.                            var error = err;  
  18.                        });  
  19.     }  
  20.   
  21.     //Loading employees list on first time  
  22.     $scope.getEmployeeList();  
  23.   
  24.     //This method is calling from pagination number  
  25.     $scope.pageChanged = function () {  
  26.         $scope.getEmployeeList();  
  27.     };  
  28.   
  29.     //This method is calling from dropDown  
  30.     $scope.changePageSize = function () {  
  31.         $scope.pageIndex = 1;  
  32.         $scope.getEmployeeList();  
  33.     };  
  34.   
  35. });   

In the code given above, we are using getEmployeeList() Method and passing pageIndex and pageSizeSelected as a query string for getting all the employees information from the Web API.

Whenever a user will click any page number, the pageIndex value will change and it will call pageChanged() method and we are calling getEmployeeList() method from pageChanged() method.

Here, the users can also change the maximum number of items per page by using the drop-down. Whenever a user will change the drop-down value from the drop-down list, the changePageSize() Method will be called and inside the method, we are setting pageIndex = 1 and calling the getEmployeeList() method.

Output

AngularJS

Summary

In this article, we have covered the Server side pagination, using AngularJS, Web API and SQL Server. We also saw that we can change the maximum number of items per page from the dropdown.

Download the attachment for the source code of the sample Application and the script files for the database operations.


Similar Articles