Pagination is the process of displaying a huge 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.

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.

Create Database

create 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 show here

Script to create EmployeeList Table for pagination

USE [Pagination]
GO

/****** Object: Table [dbo].[EmployeeList] Script Date: 10/1/2012 2:54:39 PM ******/
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

Create a procedure to get the unique department names from EmployeeList Table

Collapse | Copy Code

<span style="color: #38761d;">USE [Pagination]
GO
/****** Object:  StoredProcedure [dbo].[GetDepartments]    Script Date: 11/6/2012 1:42:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetDepartments] 
-- 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 Distinct(Department) from EmployeeList order by department
END</span>

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.

We saw that with 1000 records the page is loading in less than a second works fine. 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
/ Object: StoredProcedure [dbo].[GetEmployeeList] Script Date: 10/13/2012 4:09:34 PM /
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.

Now 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:

1. It will improve the performance
2. User will have time to review each record individually and need not to scroll the page for all the records
3. Reduces rendering time for the data to page
4. gives the best application performance

We will examine the advantage list now.

Client Side Pagination

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.

We have optimized performance in our previous articles which is now having a better performance than ever and is able to so records for more than one million records. Rendering the same on to the page also we reduced by implementing different kinds of mechanism.

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.

My last 4 articles 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

What do we for Server Side Pagination

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

Below is the changed procedure just have a look at the changes

USE [Pagination]
GO
/ Object: StoredProcedure [dbo].[GetEmployeeList] Script Date: 10/17/2012 11:29:34 AM /
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">
<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 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 />

<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>

</div>
</form>
</body>
</html>

Codebehind

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

Its 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 & 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

aspnetplex

Nov 6, 2014

Asp.Net Pagination Example

Create Database

Creating New Application

Points to Consider

Step-I: Optimize The Database Query

Advantages:

1. It will improve the performance
2. User will have time to review each record individually and need not to scroll the page for all the records
3. Reduces rendering time for the data to page
4. gives the best application performance

We will examine the advantage list now.

Client Side Pagination

How does Server Side Pagination Works.

What do we for Server Side Pagination

Client Side Pagination:

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

1 Comment

Sample Text

About Me

Blog Archive

Like Us

Flicker Image

aspnetplex

Nov 6, 2014

Asp.Net Pagination Example

Create Database

Creating New Application

Points to Consider

Step-I: Optimize The Database Query

Advantages:

1. It will improve the performance 2. User will have time to review each record individually and need not to scroll the page for all the records 3. Reduces rendering time for the data to page 4. gives the best application performance We will examine the advantage list now.

Client Side Pagination

How does Server Side Pagination Works.

What do we for Server Side Pagination

Client Side Pagination:

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

1 Comment

Sample Text

About Me

Blog Archive

Like Us

Flicker Image

1. It will improve the performance
2. User will have time to review each record individually and need not to scroll the page for all the records
3. Reduces rendering time for the data to page
4. gives the best application performance

We will examine the advantage list now.

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