Click here to Skip to main content
Click here to Skip to main content

ASP.NET Pagination

, 31 Dec 2012
Rate this:
Please Sign up or sign in to vote.
This is most common but very important part of every developer's life, Taken from my technical blog http://www.srinetinfo.com

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.

pagination database

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:

EmployeeList table for Pagination

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

The final stored procedure to add here is GetDepartments, which will return unique department names from the EmployeeList Table 

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:<span class="Apple-tab-span" style="white-space: pre; ">		</span><Author,,Name>
-- Create date: <Create Date,,>
-- Description:<span class="Apple-tab-span" style="white-space: pre; ">	</span><Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetDepartments] 
<span class="Apple-tab-span" style="white-space: pre; ">	</span>-- Add the parameters for the stored procedure here
<span class="Apple-tab-span" style="white-space: pre; ">	
</span>AS
BEGIN
<span class="Apple-tab-span" style="white-space: pre; ">	</span>-- SET NOCOUNT ON added to prevent extra result sets from
<span class="Apple-tab-span" style="white-space: pre; ">	</span>-- interfering with SELECT statements.
<span class="Apple-tab-span" style="white-space: pre; ">	</span>SET NOCOUNT ON;
    -- Insert statements for procedure here
<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.

empty web application

Now add new aspx page to the application as show in the picture below and name it employeelist.

employee list page

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.

Pagination Result - I

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

pagination application performance for 10000 records

Now we will try to run with the 100000 records to check the performance. the performance is recorded as below.

Pagination application with 100000 records

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

Pagination with selected columns per 100000 records

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

Pagination design with filters

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.

Pagination with filters result view

And here the performance overview

Pagination performance overview with fitlers

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

  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.

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;">&nbsp;</td>
                    <td>
                        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search&gt;&gt;" />
                    </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;">&nbsp;</td>
                    <td>&nbsp;</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

Resulted Performance from Client Side Pagination

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

Server Side Pagination UI

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;">&nbsp;</td>
                    <td>
                        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Search&gt;&gt;" />
                    </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;">&nbsp;</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>
                        &nbsp;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

Server Side Pagination Performance

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

  1. Consider when there is no possibility for server side pagination
  2. Requires State Management to reduce the DB calls. Means store the result sets in cache, sessions, viewstate and static objects.
  3. Memory consumption is very high
  4. Network speed and latency will be mattered
  5. 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
  6. Everything will be available to your application so will be very useful to compare and match against the records with new ones
  7. Consider using only when the records sets are needed for application level

Serve side pagination

  1. We can overcome the network issues
  2. Very fast compared to all the methods
  3. Requires minimum effort
  4. Very much important when you are working on very high traffic applications
  5. Can not implement cache or local storage for application
  6. 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Srinivasu Pemma
Technical Lead
India India
Working as Technical Lead for last few years and has very good exposure to ms.net technologies
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinprofessionalSrinivas Kalabarigi24-Jul-13 17:38 
QuestionAbout paging... PinmemberObiWan_MCC2-Jan-13 2:57 
GeneralMy vote of 5 PinmemberSavalia Manoj M30-Dec-12 17:17 
GeneralRe: My vote of 5 PinmemberSrinivasu Pemma30-Dec-12 18:08 
GeneralMy vote of 5 PinmemberAhmed Ibrahim Assaf29-Dec-12 21:10 
Well Done Smile | :)
GeneralRe: My vote of 5 PinmemberSrinivasu Pemma29-Dec-12 21:31 
SuggestionDoesn't support order by in With Pinmemberwanghuajie198210-Nov-12 21:34 
GeneralRe: Doesn't support order by in With PinmemberSrinivasu Pemma12-Nov-12 16:06 
QuestionYour sample is wrong Pinmemberreesek95-Nov-12 18:17 
AnswerRe: Your sample is wrong PinmemberSrinivasu Pemma5-Nov-12 21:17 
GeneralMy vote of 5 PinmemberAbey Thomas2-Nov-12 17:48 
QuestionSoftware Compatibility PinmemberAbhishek Pant31-Oct-12 18:11 
AnswerRe: Software Compatibility PinmemberSrinivasu Pemma31-Oct-12 20:47 
GeneralGetting Exceptional error-Any Suggestion. [modified] PinmemberAbhishek Pant1-Nov-12 4:10 
GeneralRe: Getting Exceptional error-Any Suggestion. PinmemberSrinivasu Pemma1-Nov-12 18:51 
GeneralRe: Getting Exceptional error-Any Suggestion. PinmemberAbhishek Pant2-Nov-12 1:47 
GeneralMy vote of 5 PinmemberAbhishek Pant31-Oct-12 18:03 
SuggestionVery good Pinmemberlrwilson30-Oct-12 22:47 
GeneralRe: Very good PinmemberSrinivasu Pemma30-Oct-12 22:56 
GeneralRe: Very good Pinmemberlrwilson30-Oct-12 23:32 
GeneralMy vote of 5 PinmemberMember 293710329-Oct-12 20:25 
SuggestionImages PinmentorDaveAuld29-Oct-12 4:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140827.1 | Last Updated 31 Dec 2012
Article Copyright 2012 by Srinivasu Pemma
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid