Server Side Pagination using ASP.NET Core and Angular 8

Introduction

 
In this article, we will learn how to create server-side paging, which is very useful whenever we have to display a large number of records.
 
This will come in a total of 3 articles. In this article, we are displaying the number of records. With that, it will calculate the number of pages, but on one page we can see only the selected records, so rather than fetching all of the records, we are going to fetch records based on pages all at once, which will increase our performance. 
 

How will it work?

 
Suppose that we have 500 records to display in the front end, and we are displaying only 100 records per page. After 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 we are fetching all 500 records this will decrease our performance.
 
So, it would be better if we fetch only 100 records at one time and when you click on the next page, it will fetch the next 100 records of that particular page. Here, every time when you click on the page, it will fetch records from the database table.
 
Prerequisites
  • Basic knowledge of Angular
  • Visual Studio Code must be installed
  • Angular CLI must be installed
  • Node JS must be installed
  • Microsoft Visual Studio 2017 must be installed
  • SQL server 2014.

BACK END

 
Here Backend related code we will do it using 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). 
  1. create database company  
Step 2
 
Create CompanyDetails Table using the following code
  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] [intNULL,  
  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 = ONON [PRIMARY]  
  12. ON [PRIMARY]  
  13. GO  
Now let's add Store Procedures
 
Step 3
 
Create Stored Procedure of the following:
 
GetAllCompanies 
  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 
  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.
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Step 2
 
Choose the Create option and select the ASP.NET web application.
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Step 3
 
Select Web API and click Ok.
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Step 4
 
Now right click on the controller and add a new item.
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Step 5
 
Choose Ado.net Entity Data Model and then click on Add
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Step 6
 
Next Step is EF Designer, just click on next.
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Step 7
 
A new pop-up will show. Click on next. If your's isn't established, then click on new connection
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Step 8
 
Copy your database connection server name and paste it in the server name textbox. You will see all the database, select your database and click on ok.  
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Step 9
 
The next popup will show, paste your database server name, and choose for the database and test for the connection then click on next. Here, in the new screen, select your tables and store the procedure. Then click on finish.
 
Server Side Pagination Using ASP.NET Core And Angular 8
 
Our next step is to right-click on the Controllers folder and add a new controller. Name it as "Paginationcontroller" and add the following namespace in the Paginationcontroller.
 
Here is the complete code for getting all the Pagination records.
 
Complete Pagination controller code
  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. }  

FRONT END

 
Step 1
            
Let's create an angular project using following npm command
  1. ng new pagination  
Step 2
 
Open the newly created project in visual studio code and install bootstrap in this project
  1. npm install bootstrap --save   
Now open styles.css file and add Bootstrap file reference.To add reference in styles.css file add this line.
  1. @import '~bootstrap/dist/css/bootstrap.min.css';  
Step 3
 
Now let's create a new component by using the following command.
  1. ng g c pagination  
Step 4
 
Now create a new service using the following command.
  1. ng generate service pagination   
Step 5
 
Now open the pagination.component.html and paste the following code to see the HTML template. 
  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
 
Open the pagination.component.ts file and add the following code in this file where our logic has been written. 
  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 pagination.component.css file and paste the code for some styling.
  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!importantwidth:100%!important;}    
  9. html, body{background-color:#e4e5e6;}    
  10. html {position:relativemin-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-seriffont-weight:400font-size:13px;}    
  18. body{padding-top:52px;}    
  19.     
  20. p{font-family:'Roboto'sans-serifcolor:#303030font-weight:400margin-bottom:1rem;}    
  21. input, textarea, select{font-family:'Roboto'sans-serif;}    
  22.     
  23. h1,h2,h3,h4,h5,h6{font-family:'Roboto'sans-seriffont-weight:700;}    
  24. h1{font-size:20pxcolor:#000000margin-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:-8pxmargin-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:8pxpadding-left:8px;}    
  33.     
  34. .card-header{background-color:#f0f3f5border-bottom:1px solid #c8ced3font-size:13pxfont-weight:600color:#464646text-transform:uppercasepadding:.75rem 8px;}    
  35.     
  36.     
  37. .cnstr-record th{white-space:nowrap;padding:.45rem .2rem; font-size:13pxborder-bottom-width:0px!important;}    
  38. .cnstr-record thead{background:#f0f3f5;}    
  39.     
  40. .cnstr-record .form-control{font-size:13pxpadding: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 #596269display:inline-block; border-radius:3pxwidth:16pxheight:16pxvertical-align:topbackground:#596269color:#ffffff!important;}    
  54. .expand-row img{vertical-align:topposition:relative; top:2px;}    
  55. .sub-table th{font-weight:400font-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-left0;  
  68.     border-top-left-radius: .25rem;  
  69.     border-bottom-left-radius: .25rem;  
  70. }  
  71.   
  72. .pageColor{  
  73.     positionrelative;  
  74.     displayblock;  
  75.     padding: .5rem .75rem;  
  76.     margin-left-1px;  
  77.     line-height1.25;  
  78.     colorwhite!important;  
  79.     background-colorblack!important;  
  80.     border1px solid #dee2e6;  
  81. }  
  82. .notAllowed{  
  83.     positionrelative;  
  84.     displayblock;  
  85.     padding: .5rem .75rem;  
  86.     margin-left-1px;  
  87.     line-height1.25;  
  88.     color#007bff;  
  89.     background-color#fff;  
  90.     border1px solid #dee2e6;  
  91.     cursor: not-allowed;  
  92. }  
  93. .page-link {  
  94.     positionrelative;  
  95.     displayblock;  
  96.     padding: .5rem .75rem;  
  97.     margin-left-1px;  
  98.     line-height1.25;  
  99.     color#007bff;  
  100.     background-color#fff;  
  101.     border1px solid #dee2e6;  
  102. }  
  103.   
  104. }     
Step 8
 
At last, open pagination.service.ts file and add services to call our API.
  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.   
Step 9
 
Let's add the following code in api.service.ts file:
  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
 
Next and last step is to add the app module file in your project.
  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 10
 
Now, its time to see the output. For that, just open your terminal and type "ng serve -o" to compile and open automatically in your browser.
 
After loading our page you can see the output like in the below images.
 
Here, the total number of records is 33 based on that our logic in frontend will calculate the number of pages i.e at one page we are displaying only 5 records(can be change) so until the 6th page, there are 5 records per page which means only 3 records are remaining now which will come in the last page, which is 7th one.
 
Server Side Pagination Using ASP.NET Core And Angular 8
Page 2: The total number of records to display is 5.
 
Server Side Pagination Using ASP.NET Core And Angular 8
On the last page, only 3 records will show. 
 
Server Side Pagination Using ASP.NET Core And Angular 8
With this step, we have successfully completed our frontend, web API and backend coding.
 

Conclusion

 
In this article, I tried to explain how you get the records and display it in paging using server-side pagination using angular 8 and ASP.NET.
 
This is part 1 of server-side pagination. 
 
In my next article or part 2 of this, we will learn how to add previous and next button. Plus, we are going to set the total number of records per page manually.
 
I am just a learner and eager to learn new things not just related to technology, but also in all aspects.
 
"Never stop learning, because life stops teaching" ..... by Gautam Buddha.
 
At last, please don't forget to give your valuable feedback/comments/questions about this article. Please let me know if you liked and understood this article and how I could improve it.