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

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 article will be the final part of our three-part series. You can check out the first and second parts by clicking on the below links:

In the second part, we implemented a previous and next button. In this last part, we are sorting our data on click on headings in ascending and descending order.

So here, we are displaying the number of records. With that, our program will calculate the number of pages, but in one page, we can see only the selected records, so rather than fetching all the records at one time, we are going to fetch records based on pages. This will increase our performance.

How Will it Work?

In Part 2, we were working on the Prev and Next button and their logic. Here we are sorting the records based on ascending and descending order, so when we click on the header column name it will get the related number of records in order by column name.

Back End

We will do the back-end code using SQL server. In the first part, we already have created a database and necessary tables, so no need to create it again. However, we will make a small change in the stored procedure. So we are sorting the data ascending or descending after we click on table headings on the screen. For that, I am using case statements so that it will manipulate records according to column name.

SQL

xxxxxxxxxx

USE [Company]

GO

/****** Object:  StoredProcedure [dbo].[Usp_GetAllCompanies]    Script Date: 1/17/2020 11:05:09 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Proc [dbo].[Usp_GetAllCompanies]

 @PageNo INT ,

 @PageSize INT ,

 @SortOrder VARCHAR(200)

As

Begin

    Select * From   (Select ROW_NUMBER() Over (

    Order by

    CASE WHEN @SortOrder = 'CompanyName_ASC' THEN CompanyName

        END ASC,

    CASE WHEN @SortOrder = 'CompanyName_DESC' THEN CompanyName

        END DESC,

    CASE WHEN @SortOrder = 'City_ASC' THEN City

        END ASC,

    CASE WHEN @SortOrder = 'City_DESC' THEN City

        END DESC,

    CASE WHEN @SortOrder = 'State_ASC' THEN [State]

        END ASC,

    CASE WHEN @SortOrder = 'State_DESC' THEN [State]

        END DESC,

    CASE WHEN @SortOrder = 'Owner_ASC' THEN [Owner]

        END ASC,

    CASE WHEN @SortOrder = 'Owner_DESC' THEN [Owner]

        END DESC,

    CASE WHEN @SortOrder = 'Year_ASC' THEN PublishYear

        END ASC,

    CASE WHEN @SortOrder = 'Year_DESC' THEN PublishYear

        END DESC

    ) AS 'RowNum', *

         from   [CompanyDetails]

        )t  where t.RowNum Between ((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)

End

Front End

This is a stepwise process where we are going to change some of the code in some files without wasting any time adding components and services.

Step 1

Let’s copy this HTML code and replace it to the
pagination.html file. Some change compared to the previous part of the code. Here we are adding a new click event called “sortByHeading”. By default, it will fetch the records with company Name in ascending order. If we click that same heading, it will again hit the API and fetch the records in descending order. Also, I am applying CSS for the heading color, so if you click on a different header, then it will fetch the records based on that header name in ascending order and active color will apply to that particular header.

HTML

<div data-code="<div class="row">
<div class="col-12 col-md-12">
<div class="card">
<div class="card-header">
<div class="row">
<div>
Companies 1-{{pageSize}} (Total:{{totalCompaniesCount}})
</div>

<div style="margin: auto;" class="add-row add-row-rel top-paging">
<span class="cpp">Companies per page:</span>
<a (click)="setRecPerPage(small)" [ngClass]="smallPageRow? 'active':'mr-2'">{{small}}</a>
<a (click)="setRecPerPage(medium)" [ngClass]="mediumPageRow? 'active':'mr-2'">{{medium}}</a>
<a (click)="setRecPerPage(large)" [ngClass]="largePageRow? 'active':'mr-2'">{{large}}</a>
</div>
</div>

</div>
<div class="card-body position-relative">
<div class="tbl-note-dentist">
Sort list by select <span>table headers</span>, click again to reorder
ascending/descending
</div>
<div class="table-responsive cnstr-record companie-tbl">
<table class="table table-bordered heading-hvr">
<thead>
<tr>
<th style="cursor: pointer;" [ngClass]="order =='CompanyName'? 'headActive':''"
(click)="sortByHeading('CompanyName')" width="80">Company Name.</th>
<th style="cursor: pointer;" [ngClass]="order =='City'? 'headActive':''"
(click)="sortByHeading('City')" width="75">City</th>
<th [ngClass]="order =='State'? 'headActive':''" style="cursor: pointer;"
(click)="sortByHeading('State')">State
</th>
<th [ngClass]="order =='Owner'? 'headActive':''" style="cursor: pointer;" (click)="sortByHeading('Owner')"
width="75">CEO
</th>
<th [ngClass]="order =='Year'? 'headActive':''" style="cursor: pointer; width:250px"
(click)="sortByHeading('Year')">Publish Year</th>
</tr>
</thead>
<tbody>
<tr *ngFor="let item of companies">
<td>{{item.CompanyName}}</td>
<td>{{item.City}}</td>
<td>{{item.State}}</td>
<td>{{item.Owner}}</td>
<td>{{item.PublishYear}}</td>

</tr>
</tbody>
</table>

</div>

<div class="container mw-100">
<div class="row">
<div class="col-md-3"> </div>
<div *ngIf="companies !=0" class="col-md-6">
<ul class="pagination justify-content-center">
<li class="page-item">
<a (click)="showPrevCompanies()"
[ngClass]="(paginationService.showNoOfCurrentPage ==1)?’notAllowed’:’page-link’"
style="margin-top: 5px; margin-right: 20px;cursor: pointer;">Prev</a></li>
<li *ngFor="let page of pageField;let i=index" class="page-item">
<a (click)="showCompaniesByPageNumber(page,i)" [ngClass]="pageNumber[i] ? ‘pageColor’:’page-link’"
style=" margin-right: 5px;;margin-top: 5px;cursor: pointer;">{{page}}</a>

</li>
<li class="page-item"><a (click)="showNextCompanies()"
[ngClass]="(paginationService.disabledNextBtn)?’notAllowed’:’page-link’"
style="margin-top: 5px;margin-left: 20px; cursor: pointer;">Next</a> </li>
</ul>
<div style="text-align: center;">
Page {{currentPage}} of Total page {{paginationService.exactPageList}}
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div> ” data-lang=”text/html”>

xxxxxxxxxx

<div class="row">

  <div class="col-12 col-md-12">

    <div class="card">

      <div class="card-header">

        <div class="row">

          <div>

            Companies 1-{{pageSize}} (Total:{{totalCompaniesCount}})

          </div>

          <div style="margin: auto;" class="add-row add-row-rel top-paging">

            <span class="cpp">Companies per page:</span>

            <a (click)="setRecPerPage(small)" [ngClass]="smallPageRow? 'active':'mr-2'">{{small}}</a>

            <a (click)="setRecPerPage(medium)" [ngClass]="mediumPageRow? 'active':'mr-2'">{{medium}}</a>

            <a (click)="setRecPerPage(large)" [ngClass]="largePageRow? 'active':'mr-2'">{{large}}</a>

          </div>

        </div>

      </div>

      <div class="card-body position-relative">

        <div class="tbl-note-dentist">

          Sort list by select <span>table headers</span>, click again to reorder

          ascending/descending

        </div>

        <div class="table-responsive cnstr-record companie-tbl">

          <table class="table table-bordered heading-hvr">

            <thead>

              <tr>

                <th style="cursor: pointer;" [ngClass]="order =='CompanyName'? 'headActive':''"

                  (click)="sortByHeading('CompanyName')" width="80">Company Name.</th>

                <th style="cursor: pointer;" [ngClass]="order =='City'? 'headActive':''"

                  (click)="sortByHeading('City')" width="75">City</th>

                <th [ngClass]="order =='State'? 'headActive':''" style="cursor: pointer;"

                  (click)="sortByHeading('State')">State

                </th>

                <th [ngClass]="order =='Owner'? 'headActive':''" style="cursor: pointer;" (click)="sortByHeading('Owner')"

                  width="75">CEO

                </th>

                <th [ngClass]="order =='Year'? 'headActive':''" style="cursor: pointer; width:250px"

                  (click)="sortByHeading('Year')">Publish Year</th>

              </tr>

            </thead>

            <tbody>

              <tr *ngFor="let item of companies">

                <td>{{item.CompanyName}}</td>

                <td>{{item.City}}</td>

                <td>{{item.State}}</td>

                <td>{{item.Owner}}</td>

                <td>{{item.PublishYear}}</td>

              </tr>

            </tbody>

          </table>

        </div>

        <!-- Code by pagination -->

        <div class="container mw-100">

          <div class="row">

            <div class="col-md-3"> </div>

            <div *ngIf="companies !=0" class="col-md-6">

              <ul class="pagination justify-content-center">

                <li class="page-item">

                  <a (click)="showPrevCompanies()"

                    [ngClass]="(paginationService.showNoOfCurrentPage ==1)?'notAllowed':'page-link'"

                    style="margin-top: 5px; margin-right: 20px;cursor: pointer;">Prev</a></li>

                <li *ngFor="let page of pageField;let i=index" class="page-item">

                  <a (click)="showCompaniesByPageNumber(page,i)" [ngClass]="pageNumber[i] ? 'pageColor':'page-link'"

                    style=" margin-right: 5px;;margin-top: 5px;cursor: pointer;">{{page}}</a>

                </li>

                <li class="page-item"><a (click)="showNextCompanies()"

                    [ngClass]="(paginationService.disabledNextBtn)?'notAllowed':'page-link'"

                    style="margin-top: 5px;margin-left: 20px; cursor: pointer;">Next</a> </li>

              </ul>

              <div style="text-align: center;">

                Page {{currentPage}} of Total page {{paginationService.exactPageList}}

              </div>

            </div>

          </div>

        </div>

      </div>

    </div>

  </div>

</div>

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 article will be the final part of our three-part series. You can check out the first and second parts by clicking on the below links:

Server Side Pagination using ASP.NET Core and Angular 8-Part 1
Server Side Pagination using ASP.NET Core and Angular 8-Part 2

In the second part, we implemented a previous and next button. In this last part, we are sorting our data on click on headings in ascending and descending order.

How Will it Work?

Back End

SQL

xxxxxxxxxx

USE [Company]

/****** Object: StoredProcedure [dbo].[Usp_GetAllCompanies] Script Date: 1/17/2020 11:05:09 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER Proc [dbo].[Usp_GetAllCompanies]

@PageNo INT ,

@PageSize INT ,

@SortOrder VARCHAR(200)

Begin

Select * From (Select ROW_NUMBER() Over (

Order by

CASE WHEN @SortOrder = ‘CompanyName_ASC’ THEN CompanyName

END ASC,

CASE WHEN @SortOrder = ‘CompanyName_DESC’ THEN CompanyName

END DESC,

CASE WHEN @SortOrder = ‘City_ASC’ THEN City

END ASC,

CASE WHEN @SortOrder = ‘City_DESC’ THEN City

END DESC,

CASE WHEN @SortOrder = ‘State_ASC’ THEN [State]

END ASC,

CASE WHEN @SortOrder = ‘State_DESC’ THEN [State]

END DESC,

CASE WHEN @SortOrder = ‘Owner_ASC’ THEN [Owner]

END ASC,

CASE WHEN @SortOrder = ‘Owner_DESC’ THEN [Owner]

END DESC,

CASE WHEN @SortOrder = ‘Year_ASC’ THEN PublishYear

END ASC,

CASE WHEN @SortOrder = ‘Year_DESC’ THEN PublishYear

END DESC

) AS ‘RowNum’, *

from [CompanyDetails]

)t where t.RowNum Between ((@PageNo-1)*@PageSize +1) AND (@PageNo*@pageSize)

End

Front End

This is a stepwise process where we are going to change some of the code in some files without wasting any time adding components and services.

Step 1

Let’s copy this HTML code and replace it to the
pagination.html file. Some change compared to the previous part of the code. Here we are adding a new click event called “sortByHeading”. By default, it will fetch the records with company Name in ascending order. If we click that same heading, it will again hit the API and fetch the records in descending order. Also, I am applying CSS for the heading color, so if you click on a different header, then it will fetch the records based on that header name in ascending order and active color will apply to that particular header.

HTML

&nbs […]

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

Introduction

How Will it Work?

Back End

Front End

Step 1