Filtration, Sorting, And Pagination In Angular 7 Using Web API And SQL Server

A Web API is used to provide data connectivity between the database and the front-end application. On the UI side, I will use the Angular Material theme to create a rich, interactive, and device-independent user experience.

I'm using Visual Studio Code as a tool to build my application. If you don't have Visual Studio Code in your system, then first, you have to download and install. Here is Visual Studio Code download link: Download Visual Studio Code Editor

You can read my previous articles related to Angular 7 from the following links.

Step 1. Create a database table

Create a database. Open SQL Server and create a new database table. As you can see from the following image, I create a database table called UserDetails with 7 columns.

Filtration, Shorting, Pagination In Angular 7 Using Web API And SQL Server

insert into UserDetails values('Mithilesh','[email protected]','0','Hyderabad','8227382372','123456')
insert into UserDetails values('Jon','[email protected]','0','Hyderabad','8227382372','123456')
insert into UserDetails values('jony','[email protected]','0','newyork','8293282272','187456')
insert into UserDetails values('Raju','[email protected]','0','tamilnadu','8227382372','123456')
insert into UserDetails values('Suresh','[email protected]','0','Delhi','8227382372','128956')
insert into UserDetails values('Mona','[email protected]','1','Hyderabad','8398732372','983456')
insert into UserDetails values('Ruma','[email protected]','1','Banglore','8227382372','123456')
insert into UserDetails values('Shaid','[email protected]','0','Patna','9823798323','123456')
insert into UserDetails values('Bharti','[email protected]','1','Jaypur','8227382372','654321')
insert into UserDetails values('Sohan','[email protected]','0','Srinagar','847623298','123456')
insert into UserDetails values('Sukesh','[email protected]','0','Hyderabad','8227382372','123456')
insert into UserDetails values('Kabir','[email protected]','0','Mumbai','982749233','123456')
insert into UserDetails values('Rmya','[email protected]','1','Vanarasi','8227382372','834846')

Note: If you already have an existing database and table, you can skip this step.

Step 2. Create a Web API Project

Now, we will create a Web API with the functionality of bind records from the database. Go to Visual Studio >> File >> New >> Project >> Web Application. After that, click OK and you will see the templates. Select the Web API template.

Filtration, Shorting, Pagination In Angular 7 Using Web API And SQL Server

Click OK.

Step 3. Add ADO.NET Entity Data Model

Now, select the Models folder. Right-click on Add >> New Item. Select Data in the left panel and choose ADO.NET Entity Data Model.

Now, click the "Add" button and select EF Designer from the database and click Next. After that, give your SQL credentials and select the database where you have your database table and data.

Click the "Add" button and select your table and click on the "Finish" button.

Step 4. Bind Records

Now, we will write code to perform the binding operation.

Go to the Controllers folder in our API Application and right-click >> Add >> Controller >> Select Web API 2 Controller-Empty.

Now, we will go to the controller class and set the routing to make it more user-friendly by writing the below code.

[RoutePrefix("Api/UserAPI")]
public class UserController : ApiController
{
[HttpGet]
[Route("AllUser")]
public IEnumerable<UserDetail> UserDetails()
{
IEnumerable<UserDetail> lstUser = new List<UserDetail>();
string response = "";
try
{
using (CRUDDBEntities objEntity = new CRUDDBEntities())
{
lstUser = objEntity.UserDetails.ToList();
}
}
catch (Exception ex)
{
response = ex.ToString();
}
return lstUser;
}
}

As you may see from the above code, it has functionality bindrecords to the table.

Step 5. Build UI Application

Now, we will create the Web application in Angular 7 that will consume Web API.

First, we have to make sure that we have Angular CLI installed.

Open the command prompt, and type the below code, and press ENTER:

npm install -g @angular/cli

Now, open Visual Studio Code and create a project.

Open TERMINAL in Visual Studio Code and type the following syntax to create a new project. We name it BindRecords.

ng new BindRecords

After that, hit ENTER. It will take a while to create the project.

Once created, the project should look like this.

Now, we will create components to provide the UI.

I'm going to create a new component, Employee.

Go to the TERMINAL and go our angular project location using the following command:

cd projectName

Now, write the following command that will create a component.

ng g c employee

Press ENTER.

Note
You can use see the component is created.

Step 6. Create an Interface

Now, we create an interface like model class.

Open TERMINAL and write the below command:

ng g interface model/IUser --spec=false

Now, write all properties of the Employee class related to an employee that matches with the database.

export interface IUser {
UserId: number,
UserName: string,
EmailId: string
Gender: string,
Address: string,
MobileNo: string,
PinCode: string
}

Step 7. Create a Service

Now, we will create a service.

Open the TERMINAL and write the below command:

ng g s employee --spec=false

Press ENTER and you will see service files.

Now, open employee.service.ts and first import necessary class and libraries and then make calls to the WebAPI methods.

import { Injectable } from '@angular/core';
import { IUser } from './model/iuser';
import { HttpClient } from '@angular/common/http';
import { Observable } from 'rxjs';
@Injectable({
providedIn: 'root'
})
export class EmployeeService {
url = "http://localhost:50468/";
constructor(private http: HttpClient) { }
AllUserDetails(): Observable<IUser[]> {
return this.http.get<IUser[]>(this.url + 'Api/UserAPI/AllUser')
}
}

Step 8. Install and Configure Angular Material Theme

As I said earlier, we will use Angular Material theme to create a rich, interactive and device-oriented UI for our Web app.

Let's install the Angular Material theme.

Open TERMINAL again and write the below command:

npm install --save @angular/material @angular/cdk @angular/animations

If you want to learn more about Angular Material, visit here: link.

After installed successfully, we can check in the package.json file.

Before the use of Angular Material, we need to add the necessary library. Here, we will create a separate module for adding the library.

Open TERMINAL again and write the below command.

ng generate module material/angularmaterial --spec=false

Open angularmaterial.ts class and write the below code.

import {A11yModule} from '@angular/cdk/a11y';
import {DragDropModule} from '@angular/cdk/drag-drop';
import {PortalModule} from '@angular/cdk/portal';
import {ScrollingModule} from '@angular/cdk/scrolling';
import {CdkStepperModule} from '@angular/cdk/stepper';
import {CdkTableModule} from '@angular/cdk/table';
import {CdkTreeModule} from '@angular/cdk/tree';
import {NgModule} from '@angular/core';
import {
MatAutocompleteModule,
MatBadgeModule,
MatBottomSheetModule,
MatButtonModule,
MatButtonToggleModule,
MatCardModule,
MatCheckboxModule,
MatChipsModule,
MatDatepickerModule,
MatDialogModule,
MatDividerModule,
MatExpansionModule,
MatGridListModule,
MatIconModule,
MatInputModule,
MatListModule,
MatMenuModule,
MatNativeDateModule,
MatPaginatorModule,
MatProgressBarModule,
MatProgressSpinnerModule,
MatRadioModule,
MatRippleModule,
MatSelectModule,
MatSidenavModule,
MatSliderModule,
MatSlideToggleModule,
MatSnackBarModule,
MatSortModule,
MatStepperModule,
MatTableModule,
MatTabsModule,
MatToolbarModule,
MatTooltipModule,
MatTreeModule,
} from '@angular/material';
@NgModule({
exports: [
A11yModule,
CdkStepperModule,
CdkTableModule,
CdkTreeModule,
DragDropModule,
MatAutocompleteModule,
MatBadgeModule,
MatBottomSheetModule,
MatButtonModule,
MatButtonToggleModule,
MatCardModule,
MatCheckboxModule,
MatChipsModule,
MatStepperModule,
MatDatepickerModule,
MatDialogModule,
MatDividerModule,
MatExpansionModule,
MatGridListModule,
MatIconModule,
MatInputModule,
MatListModule,
MatMenuModule,
MatNativeDateModule,
MatPaginatorModule,
MatProgressBarModule,
MatProgressSpinnerModule,
MatRadioModule,
MatRippleModule,
MatSelectModule,
MatSidenavModule,
MatSliderModule,
MatSlideToggleModule,
MatSnackBarModule,
MatSortModule,
MatTableModule,
MatTabsModule,
MatToolbarModule,
MatTooltipModule,
MatTreeModule,
PortalModule,
ScrollingModule,
]
})
export class AngularmaterialModule { }

Step 9. Add library in app.module

Now, open app.module.ts class and write the below code.

import { BrowserModule } from '@angular/platform-browser';
import { NgModule } from '@angular/core';
import { AppRoutingModule } from './app-routing.module';
import { AppComponent } from './app.component';
import { EmployeeComponent } from './employee/employee.component';
import { AngularmaterialModule } from './material/angularmaterial/angularmaterial.module';
import { EmployeeService } from './employee.service';
import { HttpClientModule } from '@angular/common/http';
import {BrowserAnimationsModule} from '@angular/platform-browser/animations';
@NgModule({
declarations: [
AppComponent,
EmployeeComponent
],
imports: [
BrowserModule,
HttpClientModule,
BrowserAnimationsModule,
AngularmaterialModule,
AppRoutingModule
],
providers: [EmployeeService],
bootstrap: [AppComponent]
})
export class AppModule { }

Step 10. Add Filtration, Pagination and Shorting functionality

We have added the necessary library . Now, we will design ui using angular material table. Let us first understand how it uses angular material line by line.

mat-table and provide data

Begin by adding the <table mat-table> component to your template and passing in data.

The simplest way to provide data to the table is by passing a data array to the table's dataSource input. The table will take the array and render a row for each object in the data array.

<table mat-table [dataSource]="dataSource"> ...</table>

Since the table optimizes for performance, it will not automatically check for changes to the data array. Instead, when objects are added, removed, or moved on the data array, you can trigger an update to the table's rendered rows by calling its renderRows()method.

Define the column templates

The first step to writing the data-table template is to define the columns. A column definition is specified via an <ng-container>with the matColumnDef directive, giving the column a name. Each column definition can contain a header-cell template (matHeaderCellDef) and data-cell template (matCellDef)

<ng-container matColumnDef="FirstName">
<th mat-header-cell *matHeaderCellDef mat-sort-header> FirstName </th>
<td mat-cell *matCellDef="let element"> {{element.FirstName}} </td>
</ng-container>

The set of columns defined represent the columns that are available to be rendered. The specific columns rendered in a given row, and their order, are specified on the row.

DataSource

The DataSource is meant to serve a place to encapsulate any sorting, filtering, pagination, and data retrieval logic specific to the application. More details go this link

Pagination

To paginate the table's data, add a <mat-paginator> after the table.

The MatPaginator is one provided solution to paginating your table's data,

Sorting

To add sorting behavior to the table, add the matSort directive to the table it will automatically listen for sorting changes and change the order of data rendered by the table and add mat-sort-header to each column header cell that should trigger sorting. Note that you have to import MatSortModule in order to initialize the matSort directive

Filtering

Angular Material does not provide a specific component to be used for filtering the MatTable since there is no single common approach to adding a filter UI to table data.

A general strategy is to add an input where users can type in a filter string and listen to this input to change what data is offered from the data source to the table.

Step 11. Implement component class

Now we will write code for perform the bind records, filtration, pagination and shorting functionality. Open employee.component.ts file and below code.

import { Component, OnInit, ViewChild } from '@angular/core';
import { IUser } from '../model/iuser';
import { MatTableDataSource, MatSort, MatPaginator } from '@angular/material';
import { EmployeeService } from '../employee.service';
@Component({
selector: 'app-employee',
templateUrl: './employee.component.html',
styleUrls: ['./employee.component.css']
})
export class EmployeeComponent implements OnInit {
allUser:IUser[];
dataSource: MatTableDataSource<IUser>;
displayedColumns: string[] = ['UserId', 'UserName', 'EmailId', 'Gender','Address','MobileNo','PinCode'];
@ViewChild(MatPaginator) paginator: MatPaginator;
@ViewChild(MatSort) sort: MatSort;
constructor(private service: EmployeeService,){
this.service.AllUserDetails().subscribe(data =>{
this.dataSource = new MatTableDataSource(data);
this.dataSource.paginator = this.paginator;
this.dataSource.sort = this.sort;
});
}
ngOnInit() {
this.dataSource.paginator = this.paginator;
this.dataSource.sort = this.sort;
}
applyFilter(filterValue: string) {
this.dataSource.filter = filterValue.trim().toLowerCase();
if (this.dataSource.paginator) {
this.dataSource.paginator.firstPage();
}
}
}

Open employee.component.html file and write below code.

<h2>User Details</h2>
<mat-form-field>
<input matInput (keyup)="applyFilter($event.target.value)" placeholder="Filter">
</mat-form-field>
<div class="mat-elevation-z8">
<table mat-table [dataSource]="dataSource" matSort>
<ng-container matColumnDef="UserId">
<th mat-header-cell *matHeaderCellDef mat-sort-header> UserId </th>
<td mat-cell *matCellDef="let user"> {{user.UserId}} </td>
</ng-container>
<ng-container matColumnDef="UserName">
<th mat-header-cell *matHeaderCellDef mat-sort-header> User Name </th>
<td mat-cell *matCellDef="let user"> {{user.UserName}} </td>
</ng-container>
<ng-container matColumnDef="EmailId">
<th mat-header-cell *matHeaderCellDef mat-sort-header> EmailId </th>
<td mat-cell *matCellDef="let user"> {{user.EmailId}} </td>
</ng-container>
<ng-container matColumnDef="Gender">
<th mat-header-cell *matHeaderCellDef mat-sort-header> Gender </th>
<td mat-cell *matCellDef="let user"> {{user.Gender ==0? 'Male' : 'Female'}} </td>
</ng-container>
<ng-container matColumnDef="Address">
<th mat-header-cell *matHeaderCellDef mat-sort-header> Address </th>
<td mat-cell *matCellDef="let user"> {{user.Address}} </td>
</ng-container>
<ng-container matColumnDef="MobileNo">
<th mat-header-cell *matHeaderCellDef mat-sort-header> Mobile No </th>
<td mat-cell *matCellDef="let user"> {{user.MobileNo}} </td>
</ng-container>
<ng-container matColumnDef="PinCode">
<th mat-header-cell *matHeaderCellDef mat-sort-header> PinCode </th>
<td mat-cell *matCellDef="let user"> {{user.PinCode}} </td>
</ng-container>
<tr mat-header-row *matHeaderRowDef="displayedColumns"></tr>
<tr mat-row *matRowDef="let row; columns: displayedColumns;"></tr>
</table>
<mat-paginator [pageSizeOptions]="[5, 10, 25, 100]"></mat-paginator>
</div>

Now we have completed all code functionality now we will run the out project but before that we set cors because If you consume the Web API, Angular blocks the URL and we called this issue CORS(Cross OriginResource Sharing).

First, let's resolve this problem.

Go to the Web API project.

Download a Nuget package for CORS. Go to NuGet Package Manager and download the following file.

After that, go to App_Start folder in Web API project and open WebApiConfig.cs class. Here, modify the Register method with the below code.

Add namespace

using System.Web.Http.Cors;

After that add below code inside Register method

var cors = new EnableCorsAttribute("*", "*", "*"); //origins,headers,methods
config.EnableCors(cors);

Step 12. Run

We have completed all needed code functionality for our functiolity. Before running the application, first make sure to save your work.

Now, let's run the app and see how it works.

Open TERMINAL and write the following command to run the program.

ng serve -o

The output looks like the following image. It's a stunning UI that's created.

Let's check full functionality,

We've finished performing bind records, pagination, sorting and filtration in an Angular 7 using Web API functionality. The app uses a Web API to provide data access from SQL Server.

Thank you for reading my article.

25 Comments

Join the conversation! Your thoughts help the community grow.

Hamid Khan6y ago
Thanks for sharing..............
- Mithilesh Kumar6y ago
  Thank you......................
shirel gabay6y ago
In step 4 which class is is:IEnumerable<UserDetail> lstUser i have to creat it? and what about that :CRUDDBEntities ?
- Mithilesh Kumar6y ago
  UserDetail is a model class when you add EDMX then it will automatically generate..
Rahil Hasnani6y ago
These 2 lines are showing me error: @ViewChild(MatPaginator) paginator: MatPaginator; @ViewChild(MatSort) sort: MatSort; So I googled and changed to @ViewChild(MatPaginator, {static:false}) paginator: MatPaginator; @ViewChild(MatSort, {static:false}) sort: MatSort; Now when i load my page, it shows me the following error : Cannot set property 'sort' of undefined at this line, this.dataSource.paginator = this.paginator; this.dataSource.sort = this.sort;
- Mithilesh Kumar6y ago
  Check your library I think Library is not working properly. So please check once..
- Rahil Hasnani6y ago
  I did, its same as yours. @ViewChild(MatPaginator) paginator: MatPaginator; @ViewChild(MatSort) sort: MatSort; these two lines states the error that "@ViewChild accepts 2 argumrnts but got 1"
- Mithilesh Kumar6y ago
  You use like that @ViewChild(MatSort, {static: false})
Rahil Hasnani6y ago
Hi, this is awesome tutorial, but I am facing 1 issue. I am getting this error "core.js:191 Could not find HammerJS. Certain Angular Material components may not work correctly." Because of which the sorting and pagination is not working
- Mithilesh Kumar6y ago
  Try to install npm install --save hammerjs
Haolun Wu6y ago
This tutorial did help a lot, very straightforward , I would say it is almost the best one for Web API and Angular beginning tutorial after look through a lot on the website. Thank you, Mithilesh.
- Mithilesh Kumar6y ago
  Thank you Haolun a valuable comment
First Last6y ago
Hi, I'm looking for an example of a 3-column grid(or table) that just displays images retrieved from a SQL server database. Note: each column of the grid is a row from the database. Can the angular material table render a row with 3 columns - 3 reads to the database? The image column on the table is in varbinary format. The data is coming from the database via the MVC web api.
- First Last6y ago
  Here is how I solved it. <mat-grid-tile *ngFor='let i of allImages | async'> <img src='data:image/jpeg;base64,{{ i.ImageData }}' class="tile-image"> </mat-grid-tile>
- First Last6y ago
  The image is actually stored in the database. A single table with just 1 column - imageData varbinary(max). So I want to retrieve it and display it in a grid.
- Mithilesh Kumar6y ago
  Yes, this is possible. You can store the image data in a folder in API and you send the image name in the database and after that, you can retrieve.
Venkata K7y ago
Explained in simple way.Now able to create small app using webapi,angular. Thanks
- Mithilesh Kumar7y ago
  Thank you............
Humayun Kabir Mamun7y ago
Very informative...
- Mithilesh Kumar7y ago
  Thank you.......
Diego Ramirez7y ago
Hi friend, firstly of all it is an excellent article about pagination and finally is it possible do it the same but with bootstrap 4 and stored procedures? thanks in advance.
- Diego Ramirez7y ago
  When will you do that?? Thanks
- Mithilesh Kumar7y ago
  Thank toy valuable feedback and Yes it is possible.......using bootstrap also...
Jose Pera7y ago
This is the best c-sharpcorner article I've read. I've been visiting since 2005.
- Mithilesh Kumar7y ago
  Thanks a lot for your valuable feedback...