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.

• Cascading Dropdown List Using MVC, Web API And Angular7
• CRUD operations in angular7 using Web API

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.

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.

 

 

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.