Aurelia is a modern, open source client side JavaScript framework for web and mobile application development. It emphasis on simple conventions and ES6/ES7 support. It is written using ECMAScript 2016. Using Aurelia, we can build applications using modules, classes, decorators etc. In this article we will look into how to do pagination in Aurelia.
Introduction
Aurelia is a modern, open source client side JavaScript framework for web and mobile application development. It emphasis on simple conventions and ES6/ES7 support. It is written using ECMAScript 2016. Using Aurelia, we can build applications using modules, classes, decorators etc. In this article we will look into how to do pagination in Aurelia.
Creating the WebAPI 2.0 project
Fire up Visual Studio and then File -> New -> Project -> Web -> Asp.Net Web Application.
The next screen that pops up display's a list of templates and we need to choose Web API template.
Also set the authentication mode to No Authentication
Clicking on the OK button creates the Web API project whose structure is as under
Now let us first create the Employee model as under
namespace WebApplication1.Models
{
public class Employee
{
public int EmployeeID { get; set; }
public string EmployeeName { get; set; }
public string EmailAddress { get; set; }
public string PhoneNumber { get; set; }
}
}
Our Employee model is a simple Employee Class which has some scalar properties like EmployeeID,EmployeeName,EmailAddress,PhoneNumber.
Now let us create the DBOperations class that will perform the pagination operation by using ADO.NET and Stored Procedures.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace WebApplication1.Models
{
public class DBOperations
{
/// <summary>
/// Function: GetEmployees
/// Get the Employee Records
/// </summary>
/// <returns></returns>
public static List<Employee> GetEmployees()
{
List<Employee> lstEmployees = new List<Employee>();
try
{
string ConnectionPath = ConfigurationManager.ConnectionStrings["DbConnection"].ToString();
using (var sqlCon = new SqlConnection(ConnectionPath))
{
using (SqlCommand cmd = new SqlCommand("usp_GetEmployees", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
sqlCon.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
lstEmployees
.Add
(
new Employee
{
EmployeeID = Convert.ToInt32(dr["EmployeeID"].ToString())
,
EmployeeName = dr["EmployeeName"].ToString()
,
EmailAddress = dr["EmailAddress"].ToString()
,
PhoneNumber = dr["PhoneNumber"].ToString()
}
);
}
dr.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return lstEmployees;
}
/// <summary>
/// Function: GetEmployeesForPagination
/// Get the Employee Records for the range
/// </summary>
/// <param name="startIdx"></param>
/// <param name="endIdx"></param>
/// <returns></returns>
public static List<Employee> GetEmployeesForPagination(int startIdx, int endIdx)
{
List<Employee> lstEmployees = new List<Employee>();
try
{
string ConnectionPath = ConfigurationManager.ConnectionStrings["DbConnection"].ToString();
using (var sqlCon = new SqlConnection(ConnectionPath))
{
using (SqlCommand cmd = new SqlCommand("usp_GetEmployeesForPagination", sqlCon))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@StartIndex", SqlDbType.Int).Value = startIdx;
cmd.Parameters.Add("@EndIndex", SqlDbType.Int).Value = endIdx;
sqlCon.Open();
SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (dr.Read())
{
lstEmployees
.Add
(
new Employee
{
EmployeeID = Convert.ToInt32(dr["EmployeeID"].ToString())
,
EmployeeName = dr["EmployeeName"].ToString()
,
EmailAddress = dr["EmailAddress"].ToString()
,
PhoneNumber = dr["PhoneNumber"].ToString()
}
);
}
dr.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
return lstEmployees;
}
}
}
Now expand the Controllers folder and create EmployeeController.cs and add the following code
using System.Collections.Generic;
using System.Web.Http;
using WebApplication1.Models;
namespace WebApplication1.Controllers
{
[RoutePrefix("Employee")]
public class EmployeeController : ApiController
{
[Route("EmployeeRecords")]
[HttpGet]
public List<Employee> GetEmployeeRecords()
{
return DBOperations.GetEmployees();
}
[Route("EmployeeRecordsForPagination")]
[HttpGet]
public EmployeeRelatedInformation GetEmployeeRecordsForPagination(int startIdx,int endIdx)
{
EmployeeRelatedInformation eri = new EmployeeRelatedInformation();
eri.EmployeeRecordCount = DBOperations.GetEmployees().Count;
eri.Employees = DBOperations.GetEmployeesForPagination(startIdx, endIdx);
return eri;
}
}
public class EmployeeRelatedInformation
{
public int EmployeeRecordCount { get; set; }
public List<Employee> Employees { get; set; }
}
}
Basically, the GetEmployeeRecordsForPagination method accepts two parameters viz.startIdx and endIdx. These are the Start and End Index respectively. Suppose we have 20 records and we want to obtain records from 10 to 15. So, the value of startIdx will be 10 and endIdx will be 15. The EmployeeRelatedInformation class exposes two properties viz. a)EmployeeRecordCount - Total Records Count of teh Employees and b)List<Employee> Employees - The collection of Emloyee records within the specified range.
Now from the Nu-get package manager issue
Install-Package Microsoft.AspNet.WebApi.Cors
Once done, then open WebApiConfig.cs file and enable the CORS as under
The complete code is as under
using System.Web.Http;
using System.Web.Http.Cors;
namespace WebApplication1
{
public static class WebApiConfig
{
public static void Register(HttpConfiguration config)
{
// Web API configuration and services
config.EnableCors();
config.EnableCors(new EnableCorsAttribute("*", "*", "*"));
// Web API routes
config.MapHttpAttributeRoutes();
config.Routes.MapHttpRoute(
name: "DefaultApi",
routeTemplate: "api/{controller}/{id}",
defaults: new { id = RouteParameter.Optional }
);
}
}
}
Run the application and click the API link in the browser to receive the below image
Now we can test the API Services of the EmployeeController.cs through PostMan .
Design the Database Table and Stored Procedures
Next execute the below database script that will craete both the table (say tblEmployees) and teh insert/update/delete/fetch stored procedures.
USE [YOURDATABASE]
GO
/****** Object: Table [dbo].[tblEmployees] Script Date: 8/24/2016 4:08:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblEmployees](
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](50) NOT NULL,
[EmailAddress] [varchar](50) NOT NULL,
[PhoneNumber] [varchar](50) NOT NULL,
CONSTRAINT [PK_tblEmployee] PRIMARY KEY CLUSTERED
(
[EmployeeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: StoredProcedure [dbo].[usp_GetEmployees] Script Date: 8/24/2016 4:08:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: RNA Team
-- Description: Get employee records
-- exec [dbo].[usp_GetEmployees]
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetEmployees]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
EmployeeID
,EmployeeName
,EmailAddress
,PhoneNumber
FROM [dbo].[tblEmployees]
END
GO
/****** Object: StoredProcedure [dbo].[usp_GetEmployeesForPagination] Script Date: 8/24/2016 4:08:08 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: RNA Team
-- Description: Select Employee Records within the Range
-- exec [dbo].[usp_GetEmployeesForPagination] 19,20
-- =============================================
CREATE PROCEDURE [dbo].[usp_GetEmployeesForPagination]
-- Add the parameters for the stored procedure here
(
@StartIndex INT
,@EndIndex INT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT
EmployeeID
,EmployeeName
,EmailAddress
,PhoneNumber
FROM [dbo].[tblEmployees]
WHERE EmployeeID BETWEEN @StartIndex AND @EndIndex
END
GO
As can be figure out that, in the usp_GetEmployeesForPagination we are selecting the records within the range
......
......
WHERE EmployeeID BETWEEN @StartIndex AND @EndIndex
Consuming services through Aurelia-fetch-client
Create a folder say AureliaExperiment at your favourite location.
Then download the basic Aurelia project setup zipped file from here.
Extract the Zipped file and copy it under AureliaExperiment folder. It looks as under
Let us first look into the app.js file.
app.js
---------
import 'fetch';
import {HttpClient} from 'aurelia-fetch-client';
let httpClient = new HttpClient();
export class App {
constructor() {
//record setting properties
this.pageSize = 5;
this.startIndex=1;
this.endIndex = this.pageSize;
//these properties control the previous and next buttons enabling behaviour
this.disableStart=true;
this.disableEnd=false;
//this property will store the total employee records count
this.employeeRecordCount = 0;
//this property will store the employee records
this.empRecords = null;
//invoke this function when page loads. So it is kept inside the constructor
this.currentEmployeeRecords();
}
//this method will be invoked on page load
currentEmployeeRecords()
{
this.getEmployeeRecords(this.startIndex,this.endIndex);
}
//this method will be called when Previous button is clicked
prevEmployeeRecords()
{
this.startIndex=this.startIndex - this.pageSize ;
this.endIndex=this.endIndex - this.pageSize ;
if(this.startIndex==1){
this.disableStart=true;
this.getEmployeeRecords(this.startIndex,this.endIndex);
} else {
this.disableEnd =false;
this.getEmployeeRecords(this.startIndex,this.endIndex);
}
}
//this method will be called when Next button is clicked
nextEmployeeRecords()
{
this.startIndex=this.startIndex + this.pageSize ;
this.endIndex=this.endIndex + this.pageSize ;
if(this.endIndex === this.employeeRecordCount){
this.disableEnd =true;
this.getEmployeeRecords(this.startIndex,this.endIndex);
} else {
this.disableStart=false;
this.getEmployeeRecords(this.startIndex,this.endIndex);
}
}
//this method fetches the employee records for a particular range specified.It uses http-fetch client
getEmployeeRecords(startIDX,endIDX)
{
var uRL = 'http://localhost:22900/Employee/EmployeeRecordsForPagination?startIdx='+startIDX+'&endIdx='+endIDX;
httpClient.fetch(uRL)
.then(response => response.json())
.then(data => {
console.log(data);
this.employeeRecordCount = data.EmployeeRecordCount;
this.empRecords = data.Employees;
});
}
}
getEmployeeRecords method
By default, fetch makes GET requests. All calls to fetch return a Promise object which is used for asynchronous computations. This Promise object will resolve to a Response object. Using this Response object, we parse content and assign it to the empRecords and to employeeRecordCount property for getting the employee records and their count respectively.
currentEmployeeRecords method
This method will be invoked on page load. It sends the intial start and end index value as 1 and 5 since we want to display 5 records at a time. So at the initial page load the screen will be
The property this.disableStart=true; is making the button disable on page load since there is no record available proior to that one. This property is bind to the view in the below way
<input type="button" id="btnPrev" disabled.bind=disableStart value="<" click.delegate = "prevEmployeeRecords()"/>
This is the two way binding technique in Aurelia.
prevEmployeeRecords method
This method will be triggered while the previous button is clicked. Here the start and end index is calculated based on the below formula.
this.startIndex=this.startIndex - this.pageSize ;
this.endIndex=this.endIndex - this.pageSize ;
That means, whatever is the current page start and end index, we need to subtract the pagesize from that.
nextEmployeeRecords method
This method will be triggered while the next button is clicked. Here the start and end index is calculated based on the below formula.
this.startIndex=this.startIndex + this.pageSize ;
this.endIndex=this.endIndex + this.pageSize ;
That means, whatever is the current page start and end index, we need to add the pagesize to that.
Now let's open the app.html file and write the below template
app.html
---------
<template>
<h2><font color="green">Pagination by RNA Team - Example</font></h2>
<h2><font color="blue"><u>Display Employee Records</u></font></h2>
<table border="1">
<thead>
<tr>
<td><b>EmployeeID</b></td>
<td><b>EmployeeName</b></td>
<td><b>EmailAddress</b></td>
<td><b>PhoneNumber</b></td>
</tr>
</thead>
<tbody>
<tr repeat.for="emp of empRecords">
<td>${emp.EmployeeID}</td>
<td>${emp.EmployeeName}</td>
<td>${emp.EmailAddress}</td>
<td>${emp.PhoneNumber}</td>
</tr>
</tbody>
</table>
<br />
<table border="1">
<tr>
<td><input type="button" id="btnPrev" disabled.bind=disableStart value="<" click.delegate = "prevEmployeeRecords()"/></td>
<td>
<input type="button" id="btnNext" disabled.bind=disableEnd value=">" click.delegate = "nextEmployeeRecords()"/>
</td>
</tr>
</table>
</template>
Now run the application. Click on the ">" button
Visit the last record
Reference
Binding: Basics
Conclusion
In this article we have learnt how to perform pagination in Aurelia and WebAPI. Hope this will be useful. Thanks for reading. Zipped file attached.