Introduction
Pagination is the process of displaying a huge number of records by splitting them into different pages. This can be better explained by a example. Consider you have 10000 records in a table you want to show them to the end user when he requests. The very simple thing is to display all of them by fetching from database with a simple select * from <Table> query. OK fine you are done and your job is over. You have checked in the code and your Lead or some testing team person is verifying your features implementation. Suppose your Manager saw the application and the particular feature. Can you imagine what might be his reaction to that.
I can explain the situation how it will be, as I have faced this situation in earlier days of coding.
- Question-I: What is this?
- Question-II: What have you done?
- Question-III: Does end user is able to read such a long list?
- Question-IV: How much time it is taking to load all these records?
- Question-V: Did you mind the usability of this application while implementing this feature?
Yes your manager is right. Just think from end user point of view. If you see such a long list how can you make it out. The page you need drag till end to see the last user. It takes very long time to load the list. Process and fetching such a long list from database is very time consuming and some times you application may through timeout exception.
Here I will explain from the very beginning to the fully optimized page.
Background
This simple and fetching the data what we need from the database at the end. The considered points are reducing the page rendering times, memory management, network traffic benifits and fully optimized stored procedures
We need the following software to start our tutorial.
MS SQL Server express edition or trial version here
MS Visual Studio 2012 Ultimate trial version for 90 days here
Fine install the above software according to the guide lines provided.
Using the Code
Create Database
Create a new database in SQL Server 2012 as shown below and name the database as pagination.
Now add new table to the database and name it as employeelist with the following columns:
EmployeeID,
FirstName,
LastName,
Department,
Location,
Experience,
Salary, as shown here:
Script to create EmployeeList Table for pagination
USE [Pagination]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EmployeeList](
[EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](200) NOT NULL,
[LastName] [varchar](200) NOT NULL,
[Department] [varchar](200) NOT NULL,
[Experience] [decimal](18, 0) NOT NULL,
[Salary] [decimal](18, 0) NOT NULL,
CONSTRAINT [PK_EmployeeList] 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
Now add 1000 rows with the simple following pl-sql program
DECLARE @intFlag INT
SET @intFlag = 1
DECLARE @Random INT
DECLARE @Upper INT
DECLARE @Lower INT
DECLARE @Exp INT
DECLARE @Sal INT
DECLARE @DEP INT
WHILE (@intFlag <=1000)
BEGIN
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 10 ---- The highest random number
SELECT @Exp = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
---- This will create a random number between 1 and 999
SET @Lower = 10000 ---- The lowest random number
SET @Upper = 100000 ---- The highest random number
SELECT @Sal = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
---- This will create a random number between 1 and 999
SET @Lower = 1 ---- The lowest random number
SET @Upper = 10 ---- The highest random number
SELECT @DEP = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random
INSERT INTO EmployeeList(FirstName,LastName,Department,Experience,Salary)
VALUES ('first name'+cast(@intFlag as varchar(5)),'last name'+cast(@intFlag as varchar(5)),'department'+cast(@DEP as varchar(2)),cast(@Exp as varchar(2)) ,cast(@Sal as varchar(7)))
SET @intFlag = @intFlag + 1
--IF @intFlag = 4
--BREAK;
END
GO
The final stored procedure to add here is GetDepartments, which will return unique department names from the EmployeeList Table
USE [Pagination]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetDepartments]
<span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; ">
</span>AS
BEGIN
<span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span><span class="Apple-tab-span" style="white-space: pre; "> </span>SET NOCOUNT ON;
<span class="Apple-tab-span" style="white-space: pre; "> </span>SELECT Distinct(Department) from EmployeeList order by department
END
Yes you are now ready with the database and it much functional we will now start with asp.net side.
Creating New Application
Open visual studio 2012 and click on new project. It will open already existing templates. Under Web templates select empty website for our case and name it Pagination.
Now add new aspx page to the application as show in the picture below and name it employeelist.
Add quickly a ListView and name it lvEmployeeList and design the template for showing the employee list the page will look like this
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeList.aspx.cs" Inherits="Pagination.EmployeeList" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ListView ID="lvEmployeeList" runat="server">
<LayoutTemplate>
<table style="width: 100%; text-align: left;">
<tr>
<td style="width: 20%; text-align: center;">First Name</td>
<td style="width: 20%; text-align: center;">Last Name</td>
<td style="width: 20%; text-align: center;">Department</td>
<td style="width: 20%; text-align: center;">Experience</td>
<td style="width: 20%; text-align: center;">Salary</td>
</tr>
<tr runat="server" id="itemPlaceHolder"></tr>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr id="itemPlaceHolder">
<td style="width: 20%; text-align: center;"><%#Eval("FirstName") %></td>
<td style="width: 20%; text-align: center;"><%#Eval("LastName") %></td>
<td style="width: 20%; text-align: center;"><%#Eval("Department") %></td>
<td style="width: 20%; text-align: center;"><%#Eval("Experience") %></td>
<td style="width: 20%; text-align: center;"><%#Eval("Salary") %></td>
</tr>
</ItemTemplate>
</asp:ListView>
</div>
</form>
</body>
</html>
So we have made front end to render and show the employee details. now we need to write a method to get rows from sql server.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
namespace Pagination
{
public partial class EmployeeList : System.Web.UI.Page
{
string connectionstring;
protected void Page_Load(object sender, EventArgs e)
{
connectionstring = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["PaginationConnectionString"].ConnectionString;
if (!IsPostBack)
GetDetails();
}
private void GetDetails()
{
DataTable dtEmployeeList = new DataTable("EmployeeList");
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand cmd = new SqlCommand("select * from employeelist");
cmd.Connection = con;
con.Open();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dtEmployeeList);
}
}
lvEmployeeList.DataSource = dtEmployeeList;
lvEmployeeList.DataBind();
}
}
}
Ok everything is ready and we have 1000 records in our database. We have code to fetch and show them. We quickly run and see the result.
Wow! Awesome we got the page loaded in less than second its great. Ok we will try with more these many records. In my coming parts I will explain each individual section with the time loading and performance optimization things.
Now I have added more than 2,00,000 records and when I ran the same page my system got hanged the reason is simple. The application memory is not sufficient to handle such a bunch of huge record sets. And processing fetching time for those records from SQL Server is pathetic.
So what is the solution?
Points to Consider
Optimize the database query
Optimize indexing
Implement client side pagination
Less Use of Server Control
I will explain each and every step here with the time taken to process records sets.
Step-I calculate the current performance of the page by means of page loading time with the help of Jetbrain's Dotrace profiling tool. The dotrace tool is very much easy to profile application with less overhead. I have been using this for last 4 years. This gives each every line of and method execution times perfectly. So according to the dotrace report you can tune, optimize or re factor your code accordingly.
To load initial 10000 records the time taken is 765 ms. Check the figure below
Now we will try to run with the 100000 records to check the performance. the performance is recorded as below.
see the difference as the records count grows the loading time grows. He I am showing only the Page load method execution time. So the times here seen are very less like less than half of the full page rendering and method execution. Suppose the time taken to load and render 100000 records for me is 3 minutes but the dotrace report I am showing here is only the execution time of the method.
Step-I: Optimize The Database Query
We can optimize database query in number of ways. The very first check what columns we required?
Consider we need only Name, Department and Experience. change the query and see the result here
SELECT [FirstName]+' '+[LastName] Name,[Department],[Experience] FROM EmployeeList
You can see the change here 3000 milliseconds reduced in rendering the results to the page. so the content to be rendered on page, and set of results that should pass over the network everything got reduced that is why the change.
The same way if we can reduce the number of rows means reducing the number of rows for a page by applying filter which will provide the best usability for end use as well as provides best performance for the applications. So lets take three columns to filter the records sets Name, Department, and Experience.
Change the stored procedure code accordingly to use these parameters.
USE [Pagination]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetEmployeeList]
@name varchar(200)=null,
@departmanet varchar(200)=null,
@expstart int = null,
@expend int=null
AS
BEGIN
SET NOCOUNT ON;
SELECT top 100000 [FirstName]+' '+[LastName] Name,[Department],[Experience] FROM EmployeeList
where
(FirstName like '%'+@name+'%' or @name is null)
and (LastName like '%'+@name+'%' or @name is null)
and (Department =@departmanet or @departmanet is null)
and ((Experience >@expstart or @expstart is null) and (Experience<@expend or @expend is null ) )
END
This will give you the exact results for your query with the best application performance. Modify your screen so that end user can input the the parameter values as shown in below figure
and modify the Codebehind as below
private void GetDetails()
{
DataTable dtEmployeeList = new DataTable("EmployeeList");
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand cmd = new SqlCommand("GetEmployeeList");
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.Add(new SqlParameter("@name", txtName.Text.Trim()));
if (ddlDepartment.SelectedIndex > 0)
cmd.Parameters.Add(new SqlParameter("@departmanet", ddlDepartment.SelectedValue));
if (ddlStart.SelectedIndex > 0 && ddlStart0.SelectedIndex > 0)
{
cmd.Parameters.Add(new SqlParameter("@expstart", ddlStart.SelectedValue));
cmd.Parameters.Add(new SqlParameter("@expend", ddlStart0.SelectedValue));
}
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dtEmployeeList);
}
}
lvEmployeeList.DataSource = dtEmployeeList;
lvEmployeeList.DataBind();
}
Fine now run your application and provide the details as in the figure and lets run your dotrace tool to measure the application performance now.
And here the performance overview
Wow!! we got amazing performance just 416 milli seconds to fetch the records from sql server, with the filters though they are filtered from 1 lac records. Ok still we have a problem with this approach because today they are just 13000+ records what if in future more records added to the same cirteria? We need to optimize more to get same performance. ok we will see them in my next article in the series.
Client Side Pagination
We are done with filtering the data so that we can reduce the set of records from database which will reduce network latency and improve data transfer and render time. Even though it is fine we are returning more than 10000 records which is can not be reviewed by end user in a single glance. Here our pagination plays a the best role to show a set records in the application at once and keep on moving to another set by navigating between the pages.
Advantages:
Client Side Pagination
- It will improve the performance
- User will have time to review each record individually and need not to scroll the page for all the records
- Reduces rendering time for the data to page
- gives the best application performance
We will examine the advantage list now.
Here we need to do a simple thing to enable pagination for listview, gridview, datalist or any data control by using DataPager object which will do the best for our practice now. So now add DataPager to your code now.
Modify your aspx code as below
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeList.aspx.cs" Inherits="Pagination.EmployeeList" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
.auto-style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<table class="auto-style1">
<tr>
<td colspan="2" style="text-align: left;"><strong>Employee List</strong></td>
</tr>
<tr>
<td style="width: 25%; text-align: left;">Name:</td>
<td>
<asp:TextBox ID="txtName" runat="server" Width="40%"></asp:TextBox>
</td>
</tr>
<tr>
<td style="width: 25%; text-align: left;">Department:</td>
<td>
<asp:DropDownList ID="ddlDepartment" runat="server" Width="41%">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width: 25%; text-align: left;">Experience Start</td>
<td>
<asp:DropDownList ID="ddlStart" runat="server" Width="20%">
<asp:ListItem Value="0">--Start--</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlStart0" runat="server" Width="20%">
<asp:ListItem Value="0">--End--</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width: 25%; text-align: left;"> </td>
<td>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search>>" />
</td>
</tr>
<tr>
<td colspan="2" style="text-align: left;">
<asp:Label ID="lblQuery" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td style="width: 25%; text-align: left;"> </td>
<td> </td>
</tr>
</table>
<br />
<asp:ListView ID="lvEmployeeList" runat="server">
<LayoutTemplate>
<table style="width: 100%; text-align: left;">
<tr>
<%--<td style="width: 20%; text-align: center; font-weight: bolder;">First Name</td>
<td style="width: 20%; text-align: center; font-weight: bolder;">Last Name</td>--%>
<td style="width: 20%; text-align: center; font-weight: bolder;">Name</td>
<td style="width: 20%; text-align: center; font-weight: bolder;">Department</td>
<td style="width: 20%; text-align: center; font-weight: bolder;">Experience</td>
<%--<td style="width: 20%; text-align: center; font-weight: bolder;">Salary</td>--%>
</tr>
<tr runat="server" id="itemPlaceHolder"></tr>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr id="itemPlaceHolder">
<td style="width: 20%; text-align: center;"><%#Eval("Name") %></td>
<%--<td style="width: 20%; text-align: center;"><%#Eval("LastName") %></td>--%>
<td style="width: 20%; text-align: center;"><%#Eval("Department") %></td>
<td style="width: 20%; text-align: center;"><%#Eval("Experience") %></td>
<%--<td style="width: 20%; text-align: center;"><%#Eval("Salary") %></td>--%>
</tr>
</ItemTemplate>
</asp:ListView>
<asp:DataPager ID="dpEmployees" PageSize="100" PagedControlID="lvEmployeeList" OnPreRender="dpEmployees_PreRender" runat="server">
<Fields>
<asp:NextPreviousPagerField ButtonType="Link"/>
</Fields>
</asp:DataPager>
</div>
</form>
</body>
</html>
modify your codebhind as below
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
namespace Pagination
{
public partial class EmployeeList : System.Web.UI.Page
{
string connectionstring;
protected void Page_Load(object sender, EventArgs e)
{
lblQuery.Text = string.Empty;
connectionstring = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["PaginationConnectionString"].ConnectionString;
if (!IsPostBack)
{
LoadDepartment();
}
}
private void LoadDepartment()
{
DataTable dtDeps = new DataTable("Deps");
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand cmd = new SqlCommand("GetDepartments");
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dtDeps);
}
}
ddlDepartment.DataSource = dtDeps;
ddlDepartment.DataTextField = "Department";
ddlDepartment.DataValueField = "Department";
ddlDepartment.DataBind();
ddlDepartment.Items.Insert(0, new ListItem("--Select--", ""));
}
private void GetDetails()
{
DataTable dtEmployeeList = new DataTable("EmployeeList");
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand cmd = new SqlCommand("GetEmployeeList");
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.Add(new SqlParameter("@name", txtName.Text.Trim()));
if (ddlDepartment.SelectedIndex > 0)
cmd.Parameters.Add(new SqlParameter("@departmanet", ddlDepartment.SelectedValue));
if (ddlStart.SelectedIndex > 0 && ddlStart0.SelectedIndex > 0)
{
cmd.Parameters.Add(new SqlParameter("@expstart", ddlStart.SelectedValue));
cmd.Parameters.Add(new SqlParameter("@expend", ddlStart0.SelectedValue));
}
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dtEmployeeList);
}
}
lblQuery.Text += "<br/><br/>Number Of Records " + dtEmployeeList.Rows.Count;
lvEmployeeList.DataSource = dtEmployeeList;
lvEmployeeList.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
}
protected void dpEmployees_PreRender(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(txtName.Text.Trim()) || ddlDepartment.SelectedIndex > 0 || (ddlStart.SelectedIndex > 0 && ddlStart0.SelectedIndex > 0))
{
string str = "Select criteria";
str += " Name is like '%" + txtName.Text.Trim() + "%'";
if (ddlDepartment.SelectedIndex > 0)
str += " Department='" + ddlDepartment.SelectedValue + "'";
if (ddlStart.SelectedIndex > 0 && ddlStart0.SelectedIndex > 0)
str += " Experience range ' FROM " + ddlStart.SelectedValue + " TO " + ddlStart0.SelectedIndex + "'";
lblQuery.Text = str;
GetDetails();
}
}
}
}
now check run your code and see the results
Wowwwwwww!!! its amzing you check the results and see the amazing performance by our client side pagination. this is just and our GetDetails() Method took just 259 Millie seconds to fetch the records from database which exactly half the time to fetch the records for the filters. The Databinding time even reduced to 75 ms that is why our page rendered within a second which is very much required for a page to load.
And this is already with server controls removed version I hope need not to explain the same again.
Now if you save these records sets client side by using any State Management objects. The performance will increase even more than this. State Management I will explain in my upcoming articles.
The things so far we discussed are enough for more than 1 million records in a normal machines which will give you the best application performance ever. In my next article I will explain the server side pagination.
Server Side Pagination
Now we will discuss about the Server Side Pagination which is very much efficient to show the records in less than a second, when the database contains millions, billions and trillions of records.
How does Server Side Pagination Works.
What do we for Server Side Pagination
Below is the changed procedure just have a look at the changes
I was explaining everything on my laptop which is having configuration of 4GB RAM, 500GM HARD DISC AND INTEL I7 PROCESSOR. This is why we got very good performance. What if the database is over my local network, office network and some where on the internet. The amount of data we are returning the network speed and network latency everything will be mattered.
Here Server Side Paging concept is to limit the number of records delivery on each request. Instead sending 100000 records to client and letting the client to implement all the steps or practices we have done in previous article is waste. So the number of records what we need that much only we will transfer to the client . By this way we can overcome client network headaches
The normal stored procedure with small modification by using CTE (Common Table Expression)
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement
USE [Pagination]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetEmployeeList]
@pageIndex int,
@pageSize int,
@name varchar(200)=null,
@departmanet varchar(200)=null,
@expstart int = null,
@expend int=null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @start int, @end int;
Declare @result table(RowNum int, Name varchar(500),Department varchar(200),Experience int)
SELECT @start = (@pageIndex - 1) * @pageSize + 1,
@end = @start + @pageSize - 1;
WITH ABC AS
(
SELECT ROW_NUMBER() OVER (ORDER BY EmployeeID) RowNum,[FirstName]+' '+[LastName] Name,[Department],[Experience]
FROM EmployeeList
where
(FirstName like '%'+@name+'%' or @name is null)
and (LastName like '%'+@name+'%' or @name is null)
and (Department =@departmanet or @departmanet is null)
and ((Experience >@expstart or @expstart is null) and (Experience<@expend or @expend is null ) )
)
insert into @result select count(*),null,null,null from abc union all
SELECT * FROM ABC WHERE RowNum BETWEEN @start and @end
select * from @result
END
You need change your client side code also accordingly to accommodate the feature. Look the final screen below
And your ASPX code will be
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeList.aspx.cs" Inherits="Pagination.EmployeeList" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<table class="auto-style1">
<asp:ListView ID="lvEmployeeList" runat="server">
</ItemTemplate>
</div>
<head runat="server">
<title></title>
<style type="text/css">
.auto-style1
{
width: 100%;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<tr>
<td colspan="2" style="text-align: left;"><strong>Employee List</strong></td>
</tr>
<tr>
<td style="width: 25%; text-align: left;">Name:</td>
<td>
<asp:TextBox ID="txtName" runat="server" Width="40%"></asp:TextBox>
</td>
</tr>
<tr>
<td style="width: 25%; text-align: left;">Department:</td>
<td>
<asp:DropDownList ID="ddlDepartment" runat="server" Width="41%">
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width: 25%; text-align: left;">Experience Start</td>
<td>
<asp:DropDownList ID="ddlStart" runat="server" Width="20%">
<asp:ListItem Value="0">--Start--</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="ddlStart0" runat="server" Width="20%">
<asp:ListItem Value="0">--End--</asp:ListItem>
<asp:ListItem>1</asp:ListItem>
<asp:ListItem>2</asp:ListItem>
<asp:ListItem>3</asp:ListItem>
<asp:ListItem>4</asp:ListItem>
<asp:ListItem>5</asp:ListItem>
<asp:ListItem>6</asp:ListItem>
<asp:ListItem>7</asp:ListItem>
<asp:ListItem>8</asp:ListItem>
<asp:ListItem>9</asp:ListItem>
<asp:ListItem>10</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td style="width: 25%; text-align: left;"> </td>
<td>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search>>" />
</td>
</tr>
<tr>
<td colspan="2" style="text-align: left;">
<asp:Label ID="lblQuery" runat="server"></asp:Label>
</td>
</tr>
<tr>
<td style="width: 25%; text-align: left;"> </td>
<td style="text-align: right">
Page Size :
<asp:DropDownList ID="ddlPageSize" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlPageSize_SelectedIndexChanged">
<asp:ListItem>50</asp:ListItem>
<asp:ListItem>100</asp:ListItem>
<asp:ListItem>200</asp:ListItem>
</asp:DropDownList>
Page Index :
<asp:DropDownList ID="ddlPageIndex" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlPageIndex_SelectedIndexChanged">
<asp:ListItem>1</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
</table>
<br />
<LayoutTemplate>
<table style="width: 100%; text-align: left;">
<tr>
<%--<td style="width: 20%; text-align: center; font-weight: bolder;">First Name</td>
<td style="width: 20%; text-align: center; font-weight: bolder;">Last Name</td>--%>
<td style="width: 20%; text-align: center; font-weight: bolder;">Name</td>
<td style="width: 20%; text-align: center; font-weight: bolder;">Department</td>
<td style="width: 20%; text-align: center; font-weight: bolder;">Experience</td>
<%--<td style="width: 20%; text-align: center; font-weight: bolder;">Salary</td>--%>
</tr>
<tr runat="server" id="itemPlaceHolder"></tr>
</table>
</LayoutTemplate>
<ItemTemplate>
<tr id="itemPlaceHolder">
<td style="width: 20%; text-align: center;"><%#Eval("Name") %></td>
<%--<td style="width: 20%; text-align: center;"><%#Eval("LastName") %></td>--%>
<td style="width: 20%; text-align: center;"><%#Eval("Department") %></td>
<td style="width: 20%; text-align: center;"><%#Eval("Experience") %></td>
<%--<td style="width: 20%; text-align: center;"><%#Eval("Salary") %></td>--%>
</tr>
</asp:ListView>
</form>
</body>
</html>
Code-behind
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
namespace Pagination
{
public partial class EmployeeList : System.Web.UI.Page
{
string connectionstring;
protected void Page_Load(object sender, EventArgs e)
{
lblQuery.Text = string.Empty;
connectionstring = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["PaginationConnectionString"].ConnectionString;
if (!IsPostBack)
{
LoadDepartment();
}
}
private void LoadDepartment()
{
DataTable dtDeps = new DataTable("Deps");
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand cmd = new SqlCommand("GetDepartments");
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dtDeps);
}
}
ddlDepartment.DataSource = dtDeps;
ddlDepartment.DataTextField = "Department";
ddlDepartment.DataValueField = "Department";
ddlDepartment.DataBind();
ddlDepartment.Items.Insert(0, new ListItem("--Select--", ""));
}
private void GetDetails()
{
DataTable dtEmployeeList = new DataTable("EmployeeList");
using (SqlConnection con = new SqlConnection(connectionstring))
{
SqlCommand cmd = new SqlCommand("GetEmployeeList");
cmd.Connection = con;
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
cmd.Parameters.Add(new SqlParameter("@name", txtName.Text.Trim()));
if (ddlDepartment.SelectedIndex > 0)
cmd.Parameters.Add(new SqlParameter("@departmanet", ddlDepartment.SelectedValue));
cmd.Parameters.Add(new SqlParameter("@pageIndex", ddlPageIndex.SelectedValue));
cmd.Parameters.Add(new SqlParameter("@pageSize", ddlPageSize.SelectedValue));
if (ddlStart.SelectedIndex > 0 && ddlStart0.SelectedIndex > 0)
{
cmd.Parameters.Add(new SqlParameter("@expstart", ddlStart.SelectedValue));
cmd.Parameters.Add(new SqlParameter("@expend", ddlStart0.SelectedValue));
}
using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
{
sda.Fill(dtEmployeeList);
}
}
int records = int.Parse(dtEmployeeList.Rows[0][0].ToString());
lblQuery.Text += "<br/><br/>Number Of Records " + records;
int possibleindexes = records / int.Parse(ddlPageSize.SelectedValue);
ddlPageIndex.Items.Clear();
for (int i = 1; i <= possibleindexes; i++)
ddlPageIndex.Items.Add(i.ToString());
dtEmployeeList.Rows.Remove(dtEmployeeList.Rows[0]);
lvEmployeeList.DataSource = dtEmployeeList;
lvEmployeeList.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
string str = "Select criteria";
str += " Name is like '%" + txtName.Text.Trim() + "%'";
if (ddlDepartment.SelectedIndex > 0)
str += " Department='" + ddlDepartment.SelectedValue + "'";
if (ddlStart.SelectedIndex > 0 && ddlStart0.SelectedIndex > 0)
str += " Experience range ' FROM " + ddlStart.SelectedValue + " TO " + ddlStart0.SelectedIndex + "'";
lblQuery.Text = str;
GetDetails();
}
protected void ddlPageIndex_SelectedIndexChanged(object sender, EventArgs e)
{
GetDetails();
}
protected void ddlPageSize_SelectedIndexChanged(object sender, EventArgs e)
{
GetDetails();
}
}
}
Now the results we will see
It's amazing!!! Can you check the application performance now the best application performance now we are seeing here. Just 177 ms to execute the
GetDetails() method and is now performing the best. what a practice we have made so far. The pagination works very well in these scenarios.
Client Side vs Server Side Pagination
This is the time to compare both the scenarios and implementations.
Client side pagination
- Consider when there is no possibility for server side pagination
- Requires State Management to reduce the DB calls. Means store the result sets in cache, sessions, viewstate and static objects.
- Memory consumption is very high
- Network speed and latency will be mattered
- On other hand we have advantages of Client Side Pagination is you can fetch all the results once and you perform operation on that data and need not to connect to DB again and again
- Everything will be available to your application so will be very useful to compare and match against the records with new ones
- Consider using only when the records sets are needed for application level
Serve side pagination
- We can overcome the network issues
- Very fast compared to all the methods
- Requires minimum effort
- Very much important when you are working on very high traffic applications
- Can not implement cache or local storage for application
- Consider using when your application performance is very poor
Points of Interest
This is regular practice for MS .NET developers but it is much useful for beginners and seniors as the performance always matter and will face many time in interviews the questions about loading records
