ASP.NET Pagination

Introduction To Pagination

Pagination is the process of displaying a huge number of records by splitting them into multiple pages. This can be better explained by an example. Consider you have 10000 records in a table and you want to show them to the end user upon request. The very simple thing is to display all of them by fetching from the 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: Is the end user able to read such a long list?
Question-IV: How much time does it take to load all these records?
Question-V: Did you consider the usability of this application when implementing this feature?

Yes your manager is right. Just think from the end user's point of view. If you see such a long list, how can you use it. The user might need to scroll to the end to see the last user. It takes a very long time to load the list. The process of fetching such a long list from a database is very time consuming and sometimes your application might get a timeout exception.

Here I will explain from the very beginning how to fully optimize the pages.

We need the following software to start our tutorial.

Microsoft SQL Server Express Edition or a trial version as at here.

Microsoft Visual Studio 2012 Ultimate trial version for 90 days here.

Fine; install the above software according to the guidelines provided.

Create Database

Create a new database in SQL Server 2012 as shown below and name the database as pagination.

Now add a 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]

/****** Object: Table [dbo].[EmployeeList] Script Date: 10/1/2012 2:54:39 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

SET ANSI_PADDING ON

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]

SET ANSI_PADDING OFF

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

Create a procedure to get the unique department names from the EmployeeList Table, as in:

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

Yes you are now ready with the database and it is totally functional. We will now start with the ASP.Net side.

Creating New Application

Open Visual Studio 2012 and click on new project. It will open the already existing templates. Under Web templates select empty website for our case and name it Pagination.

Now add a 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>

</head>

<body>

<div>

<asp:ListView ID="lvEmployeeList" runat="server">

<tr>

<td style="width: 20%; text-align: center;">First 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>

</table>

</LayoutTemplate>

</tr>

</ItemTemplate>

</asp:ListView>

</div>

</form>

</body>

</html>

So we have made a 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 a second, it's great. Ok we will try with many more of these records. In my future parts I will explain each individual section with the time loading and performance optimization features.

We saw that with 1000 records the page is loading in less than a second; that works fine. Now I have added more than 2,00,000 records and when I ran the same page my system got hung; the reason is simple. The application memory is not sufficient to handle such a huge bunch of record sets. And the 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 Controls

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 the page loading time with the help of Jetbrain's Dotrace profiling tool. The dotrace tool makes profiling applications very easy with less overhead. I have been using this for the last 4 years. This gives each and every line of ____ and the method execution times perfectly. So according to the dotrace report you can tune, optimize or re-factor your code accordingly.

To load the 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 in the following:.

See, the difference is that as the record count grows, the loading time also increases. Here I am showing only the Page load method execution time. So the times seen here 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 the database query in a number of ways. The very first possibility is to check what columns are required.

Consider that 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 has been reduced in rendering the results to the page. So the content to be rendered onto a page and the set of results that should pass over the network, everything got reduced; that is why the change.

In the same way, we can reduce the number of rows for a page by applying a filter which will provide the best usability for the end user as well as provide the best performance for the applications. So let's take three columns to filter the records; Name, Department, and Experience.

Change the Stored Procedure code accordingly to use these parameters.

USE [Pagination]

/****** Object: StoredProcedure [dbo].[GetEmployeeList] Script Date: 10/13/2012 4:09:34 PM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[GetEmployeeList]

@name varchar(200)=null,

@departmanet varchar(200)=null,

@expstart int = null,

@expend int=null

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 provide you the exact results for your query with the best application performance. Modify your screen so that the end user can input the the parameter values as shown in the following figure:

And modify the Codebehind as in the following:

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 let's run your dotrace tool to measure the application performance now.

And here the performance overview:

Wow!! we got amazing performance just 416 milliseconds 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 the future more records are 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 the database which will reduce network latency and improve data transfer and rendering time. Even though it is fine we are returning more than 10000 records which cannot be reviewed by an end user in a single glance. Here our pagination plays the best role to show a set of records in the application at once and continue moving to another set by navigating between the pages.

Advantages:

1. It will improve the performance

2. The user will have time to review each record individually and does not need to scroll the pages for all the records

3. Reduces rendering time for the data to page

4. Provides the best application performance

We will examine the advantages 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 a DataPager object which will do the best for our practice now. So now add a DataPager to your code.

Modify your aspx code as in the following:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeList.aspx.cs" Inherits="Pagination.EmployeeList" %>

<!DOCTYPE html>

.auto-style1

{

width: 100%;

}

</style>

</head>

<body>

<div>

<tr>

<td colspan="2" style="text-align: left;"><strong>Employee List</strong></td>

</tr>

<tr>

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

<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search>>" />

</td>

</tr>

<tr>

<asp:Label ID="lblQuery" runat="server"></asp:Label>

</td>

</tr>

<tr>

</tr>

</table>

<br />

<asp:ListView ID="lvEmployeeList" runat="server">

<tr>

<%--<td style="width: 20%; text-align: center; font-weight: bolder;">First 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>

</table>

</LayoutTemplate>

<%--<td style="width: 20%; text-align: center;"><%#Eval("LastName") %></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">

<asp:NextPreviousPagerField ButtonType="Link"/>

</Fields>

</asp:DataPager>

</div>

</form>

</body>

</html>

Modify your codebehind as in the following:

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 run your code and see the results:

Wowwwwwww!!! It's amazing, you check the results and see the amazing performance by our client-side pagination. This is just ____ and our GetDetails() Method took just 259 Milliseconds to fetch the records from
database which is exactly half the time to fetch the records for the filters. The Databinding time is 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 the server controls removed version I hope there is no need to explain that again.

Now if you save these records, set the client side by using any State Management objects. The performance will increase even more than this. The State Management I will explain in my future articles.

The things so far we discussed are enough for more than 1 million records in 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 now has better performance than ever and is able to show records for more than one million records. When rendering them onto the page we also reduced by implementing various kinds of mechanisms.

Now we will discuss the Server Side Pagination which is highly efficient for showing the records in less than a second, when the database contains millions, billions and trillions of records.

How Server Side Pagination Works.

In my last 4 articles I explained everything on my laptop which has a configuration of 4GB RAM, 500GB Hard Disc and Intel I7 Processor. This is why we got such very good performance. What if the database is over my local network, office network and somewhere on the internet? For the amount of data we are returning at network speed and network latency, everything is relevant.

Here the Server Side Paging concept is to limit the number of records delivered on each request. Instead of sending 100000 records to the client and letting the client implement all the steps or practices we have done in the previous article is a waste. So the number of records we need and only that much, 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 a small modification 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.

The following is the modified procedure; just have a look at the changes:

USE [Pagination]

/****** Object: StoredProcedure [dbo].[GetEmployeeList] Script Date: 10/17/2012 11:29:34 AM ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[GetEmployeeList]

@pageIndex int,

@pageSize int,

@name varchar(200)=null,

@departmanet varchar(200)=null,

@expstart int = null,

@expend int=null

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 to change your client side code also, accordingly to accommodate the feature. Look at the final screen below:

And your ASPX Code will be:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="EmployeeList.aspx.cs" Inherits="Pagination.EmployeeList" %>

<!DOCTYPE html>

.auto-style1

{

width: 100%;

}

</style>

</head>

<body>

<div>

<tr>

<td colspan="2" style="text-align: left;"><strong>Employee List</strong></td>

</tr>

<tr>

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

<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search>>" />

</td>

</tr>

<tr>

<asp:Label ID="lblQuery" runat="server"></asp:Label>

</td>

</tr>

<tr>

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

<tr>

<%--<td style="width: 20%; text-align: center; font-weight: bolder;">First 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>

</table>

</LayoutTemplate>

<%--<td style="width: 20%; text-align: center;"><%#Eval("LastName") %></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 are:

It's amazing!!! Check the application performance; we are now seeing the best application performance. Just 177 ms to execute the GetDetails() method; that is the best performance. What a practice we have made so far. The pagination works very well this way.

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. In other words store the result sets in cache, sessions, viewstate and static objects.
Memory consumption is very high
Network speed & latency will be relevant
On the other hand we have the advantages of Client Side Pagination; you can fetch all the results once and you perform the operation on that data and need not to connect to the 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 record sets are needed at the application level

Serve Side Pagination

We can overcome the network issues
Very fast compared to all the methods
Requires a minimum of effort
Very much important when you are working on very high traffic applications
Cannot implement a cache or local storage for the application
Consider using when your application performance is very poor

Originally Posted At: http://www.srinetinfo.com

Introduction To Pagination

Create Database

Creating New Application

Points to Consider

Step-I: Optimize The Database Query

Advantages:

Client Side Pagination

How Server Side Pagination Works.

What do we ____ for Server Side Pagination

Client Side Pagination:

Serve Side Pagination

Originally Posted At: http://www.srinetinfo.com