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. When there's a lot of data, client-side pagination will take a long time to get all the data to 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 the Web API for HTTP Service calls and will use ADO.NET to access the data from the database. Our database will be SQL Server and we are using AngularJS and the UI-bootstrap library for the front-end.

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.

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

Now, select Empty from the template and check Web API in checkbox list and click OK, as shown below.

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

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

Set the Controller's name as" EmployeeController" and click the Add button, as shown below.

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

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

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

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

namespace ServerSidePaginationInAngularJsAndWebAPI.Models  
{  
    public class Employee  
    {  
        public string Id { get; set; }  
        public string Name { get; set; }  
        public string Email { get; set; }  
        public string Address { get; set; }  
    }  
}

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

using System.Collections.Generic;  

namespace ServerSidePaginationInAngularJsAndWebAPI.Models  
{  
    public class EmployeeList  
    {  
        public List<Employee> employees { get; set; }  
        public string totalCount { get; set; }  
    }  
}

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

using System;  
using System.Collections.Generic;  
using System.Data.SqlClient;  
using System.Data;  
using System.Configuration;  
using ServerSidePaginationInAngularJsAndWebAPI.Models;  


namespace ServerSidePaginationInAngularJsAndWebAPI.DBOperation  
{  
    public class EmployeeInfo  
    {  
        public EmployeeList GetEmployees(int pageIndex, int pageSize)  
        {  
            EmployeeList employeeList = new EmployeeList();  

            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString))  
            {  
                SqlCommand cmd = new SqlCommand("GetEmployees", connection);  
                cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;  
                cmd.Parameters.Add("@pageSize", SqlDbType.Int).Value = pageSize;  
                cmd.CommandType = CommandType.StoredProcedure;  
                try  
                {  
                    connection.Open();  
                    SqlDataReader dr = cmd.ExecuteReader();  
                    List<Employee> listEmp = new List<Employee>();  
                    while (dr.Read())  
                    {  
                        Employee emp = new Employee();  
                        emp.Id = dr["id"].ToString();  
                        emp.Name = dr["name"].ToString();  
                        emp.Email = dr["email"].ToString();  
                        emp.Address = dr["address"].ToString();  
                        listEmp.Add(emp);  
                    }  

                    dr.NextResult();  

                    while (dr.Read())  
                    {  
                        employeeList.totalCount = dr["totalCount"].ToString();  
                    }  
                    employeeList.employees = listEmp;  
                }  
                catch (Exception ex)  
                {  
                    throw;  
                }  

            }  
            return employeeList;  
        }  
    }  
}

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, the time to write our GET Action in the controller has come, where you can get the employees information from the GetEmployees methods.

The code snippet for the Web API Controller is given below.

using System.Web.Http;  
using ServerSidePaginationInAngularJsAndWebAPI.Models;  
using ServerSidePaginationInAngularJsAndWebAPI.DBOperation;  

namespace ServerSidePaginationInAngularJsAndWebAPI.Controllers  
{  
    public class EmployeeController : ApiController  
    {  
        public EmployeeList GetEmployees(int pageIndex, int pageSize)  
        {  
            EmployeeInfo empInfo = new EmployeeInfo();  
            EmployeeList empList = empInfo.GetEmployees(pageIndex, pageSize);  
            return empList;  
        }  
    }  
}

In the code given above, we are using two parameters (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 "sample."

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

USE [master]  
GO  

CREATE DATABASE [sample]  
 CONTAINMENT = NONE  
 ON  PRIMARY   
( NAME = N'sample', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\sample.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )  
 LOG ON   
( 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 )  
GO  

ALTER DATABASE [sample] SET COMPATIBILITY_LEVEL = 130  
GO  

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))  
begin  
EXEC [sample].[dbo].[sp_fulltext_database] @action = 'enable'  
end  
GO  

ALTER DATABASE [sample] SET ANSI_NULL_DEFAULT OFF   
GO  

ALTER DATABASE [sample] SET ANSI_NULLS OFF   
GO  

ALTER DATABASE [sample] SET ANSI_PADDING OFF   
GO  

ALTER DATABASE [sample] SET ANSI_WARNINGS OFF   
GO  

ALTER DATABASE [sample] SET ARITHABORT OFF   
GO  

ALTER DATABASE [sample] SET AUTO_CLOSE OFF   
GO  

ALTER DATABASE [sample] SET AUTO_SHRINK OFF   
GO  

ALTER DATABASE [sample] SET AUTO_UPDATE_STATISTICS ON   
GO  

ALTER DATABASE [sample] SET CURSOR_CLOSE_ON_COMMIT OFF   
GO  

ALTER DATABASE [sample] SET CURSOR_DEFAULT  GLOBAL   
GO  

ALTER DATABASE [sample] SET CONCAT_NULL_YIELDS_NULL OFF   
GO  

ALTER DATABASE [sample] SET NUMERIC_ROUNDABORT OFF   
GO  

ALTER DATABASE [sample] SET QUOTED_IDENTIFIER OFF   
GO  

ALTER DATABASE [sample] SET RECURSIVE_TRIGGERS OFF   
GO  

ALTER DATABASE [sample] SET  DISABLE_BROKER   
GO  

ALTER DATABASE [sample] SET AUTO_UPDATE_STATISTICS_ASYNC OFF   
GO  

ALTER DATABASE [sample] SET DATE_CORRELATION_OPTIMIZATION OFF   
GO  

ALTER DATABASE [sample] SET TRUSTWORTHY OFF   
GO  

ALTER DATABASE [sample] SET ALLOW_SNAPSHOT_ISOLATION OFF   
GO  

ALTER DATABASE [sample] SET PARAMETERIZATION SIMPLE   
GO  

ALTER DATABASE [sample] SET READ_COMMITTED_SNAPSHOT OFF   
GO  

ALTER DATABASE [sample] SET HONOR_BROKER_PRIORITY OFF   
GO  

ALTER DATABASE [sample] SET RECOVERY SIMPLE   
GO  

ALTER DATABASE [sample] SET  MULTI_USER   
GO  

ALTER DATABASE [sample] SET PAGE_VERIFY CHECKSUM    
GO  

ALTER DATABASE [sample] SET DB_CHAINING OFF   
GO  

ALTER DATABASE [sample] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )   
GO  

ALTER DATABASE [sample] SET TARGET_RECOVERY_TIME = 60 SECONDS   
GO  

ALTER DATABASE [sample] SET DELAYED_DURABILITY = DISABLED   
GO  

ALTER DATABASE [sample] SET QUERY_STORE = OFF  
GO  

USE [sample]  
GO  

ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;  
GO  

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;  
GO  

ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;  
GO  

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;  
GO  

ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;  
GO  

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;  
GO  

ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;  
GO  

ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;  
GO  

ALTER DATABASE [sample] SET  READ_WRITE   
GO

Creating a Table

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

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

USE [sample]  
GO  

SET ANSI_NULLS ON  
GO  

SET QUOTED_IDENTIFIER ON  
GO  

CREATE TABLE [dbo].[employee](  
    [id] [int] IDENTITY(1,1) NOT NULL,  
    [name] [nvarchar](50) NULL,  
    [email] [nvarchar](max) NULL,  
    [address] [nvarchar](max) NULL,  
 CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED   
(  
    [id] ASC  
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  

GO

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

USE [sample]  
GO  
SET IDENTITY_INSERT [dbo].[employee] ON   

INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (1, N'Vivek', N'vivek@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (2, N'Ranjeet', N'ranjeet@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (3, N'Sunil', N'sunil@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (4, N'Ganesh', N'ganesh@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (5, N'Subhadip', N'Subhadip@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (6, N'Vijay', N'vijay@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (7, N'Gajanan', N'gajanan@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (8, N'Santosh', N'santosh@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (9, N'Praveen', N'praveen@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (10, N'Suresh', N'suresh@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (11, N'Priya', N'priya@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (12, N'Sharath', N'Sharath@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (13, N'Nishu', N'nishu@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (14, N'Mukesh', N'mukesh@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (15, N'Raghavendra', N'raghavendra@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (16, N'Ashish', N'ashish@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (17, N'Saroj', N'saroj@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (18, N'Sarthak', N'Sarthak@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (19, N'Rajeev', N'rajeev@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (20, N'Kaveri', N'kaveri@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (21, N'Sunil', N'sunil@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (22, N'Nagalaxmi', N'nagalaxmi@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (23, N'Anusha', N'anusha@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (24, N'Vicky', N'vicky@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (25, N'Anu', N'anu@techievivek.com', N'Hyderabad')  
INSERT [dbo].[employee] ([id], [name], [email], [address]) VALUES (26, N'Divya', N'divya@techievivek.com', N'Hyderabad')  
SET IDENTITY_INSERT [dbo].[employee] OFF

Create a Stored Procedure

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

The script is given below to create a stored procedure.

USE [sample]  
GO  

SET ANSI_NULLS ON  
GO  

SET QUOTED_IDENTIFIER ON  
GO  


CREATE Procedure [dbo].[GetEmployees]  
(  
 @PageIndex INT,  
 @pageSize INT   
)   
As  
 Begin  
 SELECT * FROM employee ORDER BY id OFFSET @PageSize*(@PageIndex-1) ROWS FETCH NEXT @PageSize ROWS ONLY;  

 SELECT count(*) as totalCount FROM employee;  
 End  
GO

Now, it's time for UI coding.

Right-click on the project in the Solution Explorer and click Add -> New Folder and set the name of the UI.

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

Create an HTML page named Index.html and JavaScript file named Index.js.

Our solution structure looks like this:

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

Complete Code for HTML

<!DOCTYPE html>  
<html>  
<head>  
    <title>Employee List</title>  
    <script src="Resources/angular.js"></script>  
    <script src="Resources/ui-bootstrap-tpls-0.13.4.min.js"></script>  
    <script src="Index.js"></script>  
    <link href="Resources/bootstrap.min.css" rel="stylesheet" />  
</head>  

<body>  
    <div ng-app="employeeApp" ng-controller="employeeCtrl">  

        <div class="container">  
            <div class="row">  
                <h1>Employee List</h1>  
                <div class="table-responsive">  
                    <table class="table table-striped table-bordered table-hover tabel-condensed">  
                        <thead>  
                            <tr>  
                                <th>Id</th>  
                                <th>Name</th>  
                                <th>Email</th>  
                                <th>Address</th>  
                            </tr>  
                        </thead>  
                        <tbody>  
                            <tr ng-repeat="employee in employees">  
                                <td>{{employee.Id}}</td>  
                                <td>{{employee.Name}}</td>  
                                <td>{{employee.Email}}</td>  
                                <td>{{employee.Address}}</td>  
                            </tr>  
                        </tbody>  
                        <tfoot>  
                            <tr>  
                                <td align="center" colspan="6   ">  
                                    <span class="form-group pull-left page-size form-inline">  
                                        <select id="ddlPageSize" class="form-control control-color"  
                                                ng-model="pageSizeSelected"  
                                                ng-change="changePageSize()">  
                                            <option value="5">5</option>  
                                            <option value="10">10</option>  
                                            <option value="25">25</option>  
                                            <option value="50">50</option>  
                                        </select>  
                                    </span>  
                                    <div class="pull-right">  
                                        <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>  
                                        <a class="btn btn-primary">Page: {{pageIndex}} / {{numPages}}</a>  
                                    </div>  
                                </td>  
                            </tr>  
                        </tfoot>  
                    </table>  
                </div>  
            </div>  
        </div>  
    </div>  
</body>  
</html>

In the code given above, the main thing is that we have to understand is the 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 of pages for pagination display.
• 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 the 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 the default value for them.

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

Complete Code for AngularJS.

var app = angular.module('employeeApp', ['ui.bootstrap']);  

app.controller('employeeCtrl', function ($scope, $http) {  

    $scope.maxSize = 5;     // Limit number for pagination display number.  
    $scope.totalCount = 0;  // Total number of items in all pages. initialize as a zero  
    $scope.pageIndex = 1;   // Current page number. First page is 1.-->  
    $scope.pageSizeSelected = 5; // Maximum number of items per page.  

    $scope.getEmployeeList = function () {  
        $http.get("http://localhost:52859/api/Employee?pageIndex=" + $scope.pageIndex + "&pageSize=" + $scope.pageSizeSelected).then(  
                       function (response) {  
                           $scope.employees = response.data.employees;  
                           $scope.totalCount = response.data.totalCount;  
                       },  
                       function (err) {  
                           var error = err;  
                       });  
    }  

    //Loading employees list on first time  
    $scope.getEmployeeList();  

    //This method is calling from pagination number  
    $scope.pageChanged = function () {  
        $scope.getEmployeeList();  
    };  

    //This method is calling from dropDown  
    $scope.changePageSize = function () {  
        $scope.pageIndex = 1;  
        $scope.getEmployeeList();  
    };  

});

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

Whenever a user clicks any page number, the pageIndex value will change and it will call the pageChanged() method and we are calling the getEmployeeList() method from the 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

Summary

In this article, we have covered 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 drop-down.

Click here to download the attachment for the source code of the sample Application and the script files for the database operations from my GitHub Account.