Pagination in Aurelia

Rajnilari2015
Posted by in Aurelia category on for Beginner level | Points: 250 | Views : 519 red flag

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.


 Download source code for 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.

Recommendation
Read Let's Explore Diagnostics in ASP.NET Core 1.0 after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,Microsoft_MVP,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)