Server-Side Pagination Using ASP.NET Core and Angular 8

gowebing

Introduction

In this article, we will learn how to create server-side pagination, which is very useful whenever we have to display a large number of records.  

This will come in a total of three articles. In this article, we are displaying a number of records, and with that, we will calculate the number of pages needed. But, in one page, we can see only selected records, so rather than fetching all records at one time, we are going to fetch records based on pages. This will increase our performance. 

How Will it Work?

Suppose we have 500 records to display in the frontend and we are displaying only 100 records per page. When you click on page 2, it will display the next 100 records and so on. In Angular, we can use pipes and install some packages to display pagination, but here, if we are only showing 100 records at one time, then why fetch all 500 records at once?

It would be better if we fetch only 100 records at one time and when you click on next page, the application will fetch the next 100 records of that particular page.

Prerequisites

You may also like: Pagination and Sorting With Spring Data JPA.

Backend 

Here we will be implementing most of our logic with SQL server. The very first step is to create a database.

Step 1

Let’s create a database on your local SQL Server. I hope you have installed SQL Server 2017 in your machine (you can use SQL Server 2008, 2012, or 2016, as well). 

SQL
 




x


1 
create database company


 

Step 2

Create Company Details Table by using the following code:

SQL
  




xxxxxxxxxx
1
13


 
1 
CREATE TABLE [dbo].[CompanyDetails](  
2 
    [CompanyId] [int] IDENTITY(1,1) NOT NULL,  
3 
    [CompanyName] [nvarchar](100) NULL,  
4 
    [City] [nvarchar](50) NULL,  
5 
    [State] [nvarchar](50) NULL,  
6 
    [Owner] [nvarchar](50) NULL,  
7 
    [PublishYear] [int] NULL,  
8 
 CONSTRAINT [PK_CompanyDetails] PRIMARY KEY CLUSTERED  
9 
(  
10 
    [CompanyId] ASC  
11 
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
12 
) ON [PRIMARY]  
13 
GO  



Now, let's add a Store Procedure.

Step 3

Create a Stored Procedure with the following code: 

GetAllCompanies 

SQL
  




xxxxxxxxxx
1
13


 
1 
Create Proc [dbo].[Usp_GetAllCompanies]  
2 
 @PageNo INT ,  
3 
 @PageSize INT ,  
4 
 @SortOrder VARCHAR(200)  
5 
As  
6 
Begin  
7 
  
8 
    Select * From   (Select ROW_NUMBER() Over (  
9 
    Order by CompanyName ) AS 'RowNum', *  
10 
         from   [CompanyDetails]  
11 
        )t  where t.RowNum Between ((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)  
12 
        
13 
End  



GetAllCompaniesCount 

SQL
  




xxxxxxxxxx
1


 
1 
Create Proc [dbo].[Usp_getAllCompaniesCount]  
2 
As  
3 
  
4 
Begin  
5 
        select count(CompanyId) from   [CompanyDetails]  
6 
End  



Web API

Create an ASP.NET Core application 

Follow these steps to create an ASP.NET Core application.

Step 1

In Visual Studio 2019, click on File -> New -> Project.

 

Step 2 

Choose the Create option and select ASP.NET web application.

Step 3 

Select Web API and click Ok.

Step 4

Now, right-click on controller and then add a new item.

Step 5

Choose ADO.NET Entity Data Model and then click on Add.

Step 6

The next step is EF Designer; just click on Next. 

Step 7

A new pop-up will show. Click on next. If yours isn't established, then click on new connection. 

Step 8

Copy your database connection server name and paste it in the server name textbox. You will see all the databases; select your database and click Ok.  

Step 9

The next popup will show. Paste your database server name, choose the correct database, test for the connection, and then click Next. Here, in the new screen, select your tables and store the procedure. Then, click Finish. 

  

Our next step is to right-click on the controllers folder and add a new controller. Name it Paginationcontroller and add the following namespace in the Paginationcontroller.

Here is the complete code for getting all the Pagination records. 

Complete Pagination controller code

C#
  




xxxxxxxxxx
1
30


1 
using System.Collections.Generic;  
2 
using System.Data.Entity.Core.Objects;  
3 
using System.Linq;  
4 
using System.Web.Http;  
5 
using Pagination.Models;  
6 
namespace Pagination.Controllers  
7 
{  
8 
    public class PaginationController : ApiController  
9 
    {  
10 
        CompanyEntities2 db = new CompanyEntities2();  
11 
  
12 
        [HttpGet]  
13 
        public object getAllCompanies(int pageNo, int pageSize, string sortOrder)  
14 
        {  
15 
  
16 
            var oMyString = new ObjectParameter("totalCount", typeof(int));  
17 
  
18 
            var companyDetails = db.Usp_GetAllCompanies(pageNo, pageSize, sortOrder).ToList();  
19 
            return companyDetails;  
20 
        }  
21 
  
22 
        [HttpGet]  
23 
        public object getAllCompaniesCount()  
24 
        {  
25 
  
26 
            var companyDetailsCount = db.Usp_getAllCompaniesCount().SingleOrDefault();        
27 
            return companyDetailsCount;  
28 
        }  
29 
    }  
30 
}  



Frontend

Step 1

Let's create an Angular project using the following npm command:

JavaScript
  




xxxxxxxxxx
1


1 
ng new pagination


 

Step 2

 Open the newly created project in Visual Studio Code and install Bootstrap in the project:

JavaScript
  




xxxxxxxxxx
1


1 
npm install bootstrap --save  


 

Now, open the styles.css file and add a Bootstrap file reference. To add a reference in the styles.css file, add this line:

JavaScript
  




xxxxxxxxxx
1


1 
@import '~bootstrap/dist/css/bootstrap.min.css';  



Step 3

Now, let's create a new component by using the following command:

JavaScript
  




xxxxxxxxxx
1


1 
ng g c pagination  



Step 4

Now, create a new service using the following command:

JavaScript
  




xxxxxxxxxx
1


1 
ng generate service pagination  



Step 5 

Now, open the pagination.component.html file and paste the following code to see the HTML template. 

HTML
  




xxxxxxxxxx
1
62


1 
<div class="row">    
2 
  <div class="col-12 col-md-12">    
3 
    <div class="card">    
4 
      <div class="card-header">    
5 
        Companies 1-{{pageSize}} (Total:{{totalCompaniesCount}})    
6 
      </div>    
7 
      <div class="card-body position-relative">    
8 
          
9 
        <div class="table-responsive cnstr-record companie-tbl">    
10 
          <table class="table table-bordered heading-hvr">    
11 
            <thead>    
12 
              <tr>    
13 
                <th style="cursor: pointer;" [ngClass]="order =='CompanyNumber'? 'active':''"    
14 
                  (click)="setOrder('CompanyNumber')" width="80">Company Name.</th>    
15 
                <th style="cursor: pointer;" [ngClass]="order =='CompanyType'? 'active':''"    
16 
                  (click)="setOrder('CompanyType')" width="75">City</th>    
17 
                <th [ngClass]="order =='CompanyName'? 'active':''" style="cursor: pointer;"    
18 
                  (click)="setOrder('CompanyName')">State    
19 
                </th>    
20 
                <th [ngClass]="order =='OrgNo'? 'active':''" style="cursor: pointer;" (click)="setOrder('OrgNo')"    
21 
                  width="75">Owner    
22 
                </th>    
23 
                <th [ngClass]="order =='Street'? 'active':''" style="cursor: pointer; width:250px"    
24 
                  (click)="setOrder('Street')">Publish Year</th>    
25 
              </tr>    
26 
            </thead>    
27 
            <tbody>    
28 
              <tr *ngFor="let item of companies">    
29 
                <td>{{item.CompanyName}}</td>    
30 
                <td>{{item.City}}</td>    
31 
                <td>{{item.State}}</td>    
32 
                <td>{{item.Owner}}</td>    
33 
                <td>{{item.PublishYear}}</td>    
34 
    
35 
              </tr>    
36 
            </tbody>    
37 
          </table>    
38 
    
39 
              
40 
        </div>    
41 
        <!-- Code by pagination -->    
42 
        <div class="container mw-100">    
43 
          <div class="row">    
44 
            <div class="col-md-3"> </div>    
45 
            <div *ngIf="companies !=0" class="col-md-6">    
46 
              <ul class="pagination justify-content-center">    
47 
                <li *ngFor="let page of pageField;let i=index" class="page-item">    
48 
                  <a (click)="showCompaniesByPageNumber(page,i)" [ngClass]="pageNumber[i] ? 'pageColor':'page-link'"    
49 
                    style=" margin-right: 5px;;margin-top: 5px">{{page}}</a>    
50 
                      
51 
                </li>    
52 
              </ul>    
53 
              <div style="text-align: center;">    
54 
                Page {{currentPage}} of Total page {{paginationService.exactPageList}}    
55 
              </div>    
56 
            </div>    
57 
          </div>    
58 
        </div>    
59 
      </div>    
60 
    </div>    
61 
  </div>    
62 
</div>    



Step 6 

After this, open pagination.component.ts file and add the following code in this file:

JavaScript
  




xxxxxxxxxx
1
69


1 
import { Component, OnInit } from '@angular/core';  
2 
import { ApiService } from './api.service';  
3 
import { PaginationService } from './pagination.service';  
4 
  
5 
@Component({  
6 
  selector: 'app-pagination',  
7 
  templateUrl: './pagination.component.html',  
8 
  styleUrls: ['./pagination.component.css']  
9 
})  
10 
export class PaginationComponent implements OnInit {  
11 
  companies = [];  
12 
  pageNo: any = 1;  
13 
  pageNumber: boolean[] = [];  
14 
  sortOrder: any = 'CompanyName';  
15 
  //Pagination Variables  
16 
  
17 
  pageField = [];  
18 
  exactPageList: any;  
19 
  paginationData: number;  
20 
  companiesPerPage: any = 5;  
21 
  totalCompanies: any;  
22 
  totalCompaniesCount: any;  
23 
  
24 
  constructor(public service: ApiService, public paginationService: PaginationService) { }  
25 
  
26 
  ngOnInit() {  
27 
    this.pageNumber[0] = true;  
28 
    this.paginationService.temppage = 0;  
29 
    this.getAllCompanies();  
30 
  }  
31 
  getAllCompanies() {  
32 
    this.service.getAllCompanies(this.pageNo, this.companiesPerPage, this.sortOrder).subscribe((data: any) => {  
33 
      this.companies = data;  
34 
      this.getAllCompaniesCount();  
35 
    })  
36 
  }  
37 
  
38 
  //Method For Pagination  
39 
  totalNoOfPages() {  
40 
  
41 
    this.paginationData = Number(this.totalCompaniesCount / this.companiesPerPage);  
42 
    let tempPageData = this.paginationData.toFixed();  
43 
    if (Number(tempPageData) < this.paginationData) {  
44 
      this.exactPageList = Number(tempPageData) + 1;  
45 
      this.paginationService.exactPageList = this.exactPageList;  
46 
    } else {  
47 
      this.exactPageList = Number(tempPageData);  
48 
      this.paginationService.exactPageList = this.exactPageList  
49 
    }  
50 
    this.paginationService.pageOnLoad();  
51 
    this.pageField = this.paginationService.pageField;  
52 
  
53 
  }  
54 
  showCompaniesByPageNumber(page, i) {  
55 
    this.companies = [];  
56 
    this.pageNumber = [];  
57 
    this.pageNumber[i] = true;  
58 
    this.pageNo = page;  
59 
    this.getAllCompanies();  
60 
  }  
61 
  
62 
  getAllCompaniesCount() {  
63 
    this.service.getAllCompaniesCount().subscribe((res: any) => {  
64 
      this.totalCompaniesCount = res;  
65 
      this.totalNoOfPages();  
66 
    })  
67 
  }  
68 
  
69 
}  



Step 7 

Next, open the pagination.component.css file and paste the code to add some styling:

CSS
  




xxxxxxxxxx
1
104


1 
@charset "utf-8";    
2 
/* CSS Document */    
3 
@media all{    
4 
    *{padding:0px;margin:0px;}    
5 
div{vertical-align:top;}    
6 
img{max-width:100%;}    
7 
html {-webkit-font-smoothing:antialiased; -moz-osx-font-smoothing:grayscale;}    
8 
body{overflow:auto!important; width:100%!important;}    
9 
html, body{background-color:#e4e5e6;}    
10 
html {position:relative; min-height:100%;}    
11 
    
12 
    
13 
.card{border-radius:4px;}    
14 
.card-header:first-child {border-radius:4px 4px 0px 0px;}    
15 
    
16 
/*Typekit*/    
17 
html, body{font-family:'Roboto', sans-serif; font-weight:400; font-size:13px;}    
18 
body{padding-top:52px;}    
19 
    
20 
p{font-family:'Roboto', sans-serif; color:#303030; font-weight:400; margin-bottom:1rem;}    
21 
input, textarea, select{font-family:'Roboto', sans-serif;}    
22 
    
23 
h1,h2,h3,h4,h5,h6{font-family:'Roboto', sans-serif; font-weight:700;}    
24 
h1{font-size:20px; color:#000000; margin-bottom:10px;}    
25 
h2{font-size:30px;}    
26 
h3{font-size:24px;}    
27 
h4{font-size:18px;}    
28 
h5{font-size:14px;}    
29 
h6{font-size:12px;}    
30 
    
31 
.row {margin-right:-8px; margin-left:-8px;}    
32 
.col, .col-1, .col-10, .col-11, .col-12, .col-2, .col-3, .col-4, .col-5, .col-6, .col-7, .col-8, .col-9, .col-auto, .col-lg, .col-lg-1, .col-lg-10, .col-lg-11, .col-lg-12, .col-lg-2, .col-lg-3, .col-lg-4, .col-lg-5, .col-lg-6, .col-lg-7, .col-lg-8, .col-lg-9, .col-lg-auto, .col-md, .col-md-1, .col-md-10, .col-md-11, .col-md-12, .col-md-2, .col-md-3, .col-md-4, .col-md-5, .col-md-6, .col-md-7, .col-md-8, .col-md-9, .col-md-auto, .col-sm, .col-sm-1, .col-sm-10, .col-sm-11, .col-sm-12, .col-sm-2, .col-sm-3, .col-sm-4, .col-sm-5, .col-sm-6, .col-sm-7, .col-sm-8, .col-sm-9, .col-sm-auto, .col-xl, .col-xl-1, .col-xl-10, .col-xl-11, .col-xl-12, .col-xl-2, .col-xl-3, .col-xl-4, .col-xl-5, .col-xl-6, .col-xl-7, .col-xl-8, .col-xl-9, .col-xl-auto {padding-right:8px; padding-left:8px;}    
33 
    
34 
.card-header{background-color:#f0f3f5; border-bottom:1px solid #c8ced3; font-size:13px; font-weight:600; color:#464646; text-transform:uppercase; padding:.75rem 8px;}    
35 
    
36 
    
37 
.cnstr-record th{white-space:nowrap;padding:.45rem .2rem; font-size:13px; border-bottom-width:0px!important;}    
38 
.cnstr-record thead{background:#f0f3f5;}    
39 
    
40 
.cnstr-record .form-control{font-size:13px; padding:0px 0rem 0px 0.2rem; height:calc(2rem + 2px);}    
41 
.cnstr-record select.form-control{padding-left:.05rem;}    
42 
.cnstr-record .table td, .cnstr-record .table th {vertical-align:middle;}    
43 
.cnstr-record .table td{padding:.3rem;}    
44 
.cnstr-record .table td h4{margin:0px;}    
45 
    
46 
.wp-50{width:50px;}    
47 
.wp-60{width:60px;}    
48 
.wp-70{width:70px;}    
49 
.wp-80{width:80px;}    
50 
.wp-90{width:90px;}    
51 
.wp-100{width:100px;}    
52 
.mw-auto{min-width:inherit;}    
53 
.expand-row{width:100%; border:solid 1px #596269; display:inline-block; border-radius:3px; width:16px; height:16px; vertical-align:top; background:#596269; color:#ffffff!important;}    
54 
.expand-row img{vertical-align:top; position:relative; top:2px;}    
55 
.sub-table th{font-weight:400; font-size:12px;}    
56 
.sub-table td{background:#efefef;}    
57 
.no-bg td{background:inherit;}    
58 
.mw-100{max-width:100%;}    
59 
    
60 
  
61 
  
62 
.activeTabColor{  
63 
    color: #fff;  
64 
    background-color: #000000;  
65 
}  
66 
.page-item:first-child .page-link {  
67 
    margin-left: 0;  
68 
    border-top-left-radius: .25rem;  
69 
    border-bottom-left-radius: .25rem;  
70 
}  
71 
  
72 
.pageColor{  
73 
    position: relative;  
74 
    display: block;  
75 
    padding: .5rem .75rem;  
76 
    margin-left: -1px;  
77 
    line-height: 1.25;  
78 
    color: white!important;  
79 
    background-color: black!important;  
80 
    border: 1px solid #dee2e6;  
81 
}  
82 
.notAllowed{  
83 
    position: relative;  
84 
    display: block;  
85 
    padding: .5rem .75rem;  
86 
    margin-left: -1px;  
87 
    line-height: 1.25;  
88 
    color: #007bff;  
89 
    background-color: #fff;  
90 
    border: 1px solid #dee2e6;  
91 
    cursor: not-allowed;  
92 
}  
93 
.page-link {  
94 
    position: relative;  
95 
    display: block;  
96 
    padding: .5rem .75rem;  
97 
    margin-left: -1px;  
98 
    line-height: 1.25;  
99 
    color: #007bff;  
100 
    background-color: #fff;  
101 
    border: 1px solid #dee2e6;  
102 
}  
103 
  
104 
}    



Step 8 

Lastly, open pagination.service.ts file and add services to call our API.

JavaScript
  




xxxxxxxxxx
1
27


1 
import { Injectable } from '@angular/core';  
2 
  
3 
@Injectable()  
4 
  
5 
export class PaginationService {  
6 
    //Pagination Variables  
7 
  
8 
    temppage: number = 0;  
9 
    pageField = [];  
10 
    exactPageList: any;  
11 
  
12 
    constructor() {  
13 
    }  
14 
  
15 
    // On page load  
16 
    pageOnLoad() {  
17 
        if (this.temppage == 0) {  
18 
  
19 
            this.pageField = [];  
20 
            for (var a = 0; a < this.exactPageList; a++) {  
21 
                this.pageField[a] = this.temppage + 1;  
22 
                this.temppage = this.temppage + 1;  
23 
            }  
24 
        }  
25 
    }  
26 
  
27 
}



Step 9 

Let's add the following code in the api.service.ts file:

JavaScript
  




xxxxxxxxxx
1
23


1 
import { Injectable } from '@angular/core';  
2 
import { HttpClient } from '@angular/common/http';  
3 
import { Observable } from 'rxjs';  
4 
  
5 
@Injectable({  
6 
  providedIn: 'root'  
7 
})  
8 
export class ApiService {  
9 
    private url = "";  
10 
  
11 
    constructor(public http: HttpClient) {  
12 
    }    
13 
  
14 
getAllCompanies(pageNo,pageSize,sortOrder): Observable<any> {  
15 
    this.url = 'http://localhost:59390/api/Pagination/getAllCompanies?pageNo=' + pageNo+'&pageSize='+pageSize+'&sortOrder='+sortOrder;  
16 
    return this.http.get(this.url);  
17 
  }  
18 
  
19 
  getAllCompaniesCount(): Observable<any> {  
20 
    this.url = 'http://localhost:59390/api/Pagination/getAllCompaniesCount';  
21 
    return this.http.get(this.url);  
22 
  }  
23 
}  



Step 10

 The next and last step is to add the app module file in your project.

JavaScript
  




xxxxxxxxxx
1
22


 
1 
import { BrowserModule } from '@angular/platform-browser';  
2 
import { NgModule } from '@angular/core';  
3 
  
4 
import { AppComponent } from './app.component';  
5 
import { ApiService } from './pagination/api.service';  
6 
import { HttpClientModule } from '@angular/common/http';  
7 
import { PaginationService } from './pagination/pagination.service';  
8 
import { PaginationComponent } from './pagination/pagination.component';  
9 
  
10 
@NgModule({  
11 
  declarations: [  
12 
    AppComponent,  
13 
    PaginationComponent  
14 
  ],  
15 
  imports: [  
16 
    BrowserModule,  
17 
    HttpClientModule  
18 
  ],  
19 
  providers: [ApiService,PaginationService],  
20 
  bootstrap: [AppComponent]  
21 
})  
22 
export class AppModule { }  



Step 11 

Now, it's time to see the output. To do this, open your terminal and type ng serve -o to compile and open automatically in your browser.

Here, the total number of records is 33. Based on the logic implemented in our frontend, we will calculate the number of pages we need. We are displaying five records per page, so we need seven total pages, where the last will only display three records. 

On the second page, the total number of records to display is 5.

The last page will only show 3 records. 

With this step, we have successfully completed our frontend, Web API, and backend. 



Further Reading

 

 

 

 

Top