Best Practices for implementing paging in ASP.Net

Implementing paging in ASP.net with fast page loading has always been a heck of a task. If we use datagridview’s default paging it has so many drawbacks like it loads all the data into the data source and then select the required number of rows on required page number. In this case if we have say one million records you can imagine how much resources it would take first to load all the records and then filtering out required number of records from it.

Following is the step to step implementation of fast paging implementation using CTE.

Database Access

This paging implementation is basically on database access level i.e. in stored procedure. So let’s create a table and then a store procedure for the required query.

Making things simple I’ll create a simple table named phone_numbers to save name and phone number of a person.

Run the following script to create the table.

CREATE TABLE [dbo].[Phone_Numbers](

[intPhoneNumberId] [int] IDENTITY(1,1) NOT NULL,

[vchName] [varchar](50) NOT NULL,

[vchPhoneNumber] [varchar](50) NOT NULL,

[dtmDateCreated] [datetime] NOT NULL,

CONSTRAINT [PK_Phone_Numbers] PRIMARY KEY CLUSTERED

(

[intPhoneNumberId] 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

ALTER TABLE [dbo].[Phone_Numbers] ADD  CONSTRAINT [DF_Phone_Numbers_dtmDateCreated]  DEFAULT (getdate()) FOR [dtmDateCreated]

GO

Then create the following procedure.

– =============================================

– Author:        Mujtaba

– If you pass NULL to procedure in @vchName it will not filter the results or otherwise it will filter on the basis of criteria

– Similary if you pass NULL to @intPageId or @intPageSize it will return all the records within the search filter and otherwise it

– will return required number of rows

– To test the procedure you can run the statement below after inserting data into the table

– declare @count int exec up_GetPhoneNumbers Null,1,3, @count output select @count

– =============================================

Create PROCEDURE [dbo].[up_GetPhoneNumbers]

@vchName varchar(50) = null,

@intPageId int = null,

@intPageSize int = null,

@count int output

AS

BEGIN

– This sets the total number of recrods

select @count = count(*) from phone_numbers

where

(vchName = @vchName or @vchName is null )

– CTE for creating a temporary storage for the dataset

;with AllResults

as

(

select Row_number() over (order by intPhoneNumberId desc)  as RowNum,

vchName,

vchPhoneNumber,

dtmDateCreated,

intPhoneNumberId

from phone_numbers

where

(vchName = @vchName or @vchName is null )

)

–Selecting from CTE

select * from AllResults

where  (RowNum between ((@intPageId – 1) * @intPageSize + 1) and (@intPageId * @intPageSize)) or @intPageId is null or @intPageSize is null

END

If you pass NULL to procedure in @vchName it will not filter the results and return all the records within the pageid and pagenumber or otherwise it will filter on the basis of criteria. Similary if you pass NULL to @intPageId or @intPageSize it will return all the records within the search filter and otherwise it will return required number of rows.

Now insert some data into the table using the following script for multiple times with different parameters.

INSERT INTO [Phone_Numbers]

([vchName]

,[vchPhoneNumber]

,[dtmDateCreated])

VALUES

(‘Person1′

,’123456789′

,getdate()

Now the programming part:

Create a  datagridview, use the following code.

<asp:GridView ID="dgvPhoneNumbers" runat=""server"" AutoGenerateColumns="False">

<Columns>

<asp:TemplateField>

<HeaderTemplate>

Name

</HeaderTemplate>

<ItemTemplate>

<asp:Label ID="lblName" runat="server" Text=’<%#Eval("vchName") %>’></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Center"></ItemStyle>

</asp:TemplateField>

<asp:TemplateField>

<HeaderTemplate>

Phone Number

</HeaderTemplate>

<ItemTemplate>

<asp:Label ID="lblPhoneNumber" runat="server" Text=’<%#Eval("vchPhoneNumber") %>’></asp:Label>

</ItemTemplate>

<ItemStyle HorizontalAlign="Center"></ItemStyle>

</asp:TemplateField>

</Columns>

</asp:GridView>

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

Now go to the CS code of your page and paste the following function. Call this function on Page Load event.

private void LoadData()

{

int totalNumberOfRecords = 0;

//You should load connectionstring from web.config.
SqlConnection connection = new SqlConnection("Data Source=sqlserver;Integrated Security=SSPI;Initial Catalog=Powerbar;"); 

connection.Open();

SqlParameter[] prams = new SqlParameter[4];

prams[0] = new SqlParameter("@vchName", SqlDbType.VarChar, 50);

prams[0].Value = DBNull.Value; //You can give search criteria here.

prams[1] = new SqlParameter("@intPageId", SqlDbType.Int);

prams[1].Value = "1″; //Page number 1.

prams[2] = new SqlParameter("@intPageSize", SqlDbType.Int);

prams[2].Value = "5″; //Number of rows per page.

prams[3] = new SqlParameter("@count", SqlDbType.Int); //Output parameter to get total number records based on our criteria.

prams[3].Direction = ParameterDirection.Output; //Setting it an output parameter.

SqlCommand cmd = new SqlCommand("up_GetPhoneNumbers", connection);

cmd.CommandType = CommandType.StoredProcedure;

//Adding parameters to command.

foreach (SqlParameter parameter in prams)

{

cmd.Parameters.Add(parameter);

}

SqlDataAdapter adapter = new SqlDataAdapter(cmd);

DataSet dataset = new DataSet();

adapter.Fill(dataset);

//Retreive total number of records fromt the output paramenter.

totalNumberOfRecords = prams[3].Value == System.DBNull.Value ? 0 : Convert.ToInt32(prams[3].Value);

connection.Close();

dgvPhoneNumbers.DataSource = dataset;

dgvPhoneNumbers.DataBind();

}

Paging

Now comes the interesting part i.e. creating a paging string.

Declare two variables in your page.

private int pageId = 1;

private int pageSize = 5;

Now put the following code at the top of your  Page_load event handler.

if (!string.IsNullOrEmpty(Request.QueryString["p"]))

{

int.TryParse(Request.QueryString["p"], out pageId);

}

Then copy the following functions to some common accessible place (but I have pasted it in the same page’s code in provided source code).

public static string GetPagingString(int curPage, int pageSize, int rowCount, string url)

{

StringBuilder sb = new StringBuilder(500);

int startPage, endPage;

// for first

if (rowCount > 25)

{

LoadPagingPage(sb, curPage, 1, "|<", url);

}

else

{

LoadPagingPage(sb, curPage, 1, "|<", url);

}

// Previous

if (curPage > 1)

{

LoadPagingPage(sb, curPage, curPage – 1, "<", url);

}

else

{

// for displaying page 0

LoadPagingPage(sb, curPage, curPage, "<", url);

}

// Calculate page range

if (curPage < 6)

{

startPage = 1;

}

else

{

//startPage = curPage – 5;

startPage = curPage – 5;

}

endPage =

Convert.ToInt32(Math.Ceiling(Convert.ToDecimal(rowCount) / Convert.ToDecimal(pageSize)));

// Load pages

if (endPage > 10)

{

int end, last, b;

end = startPage + 5;

last = Convert.ToInt32(Convert.ToDecimal(end) * Convert.ToDecimal(pageSize));

b = rowCount – last;

if (b > 125)

{

if (curPage < 6)

{

end = startPage + 10;

}

else

{

end = startPage + 11;

}

for (int i = startPage; i < end; i++)

{

LoadPagingPage(sb, curPage, i,

"", url);

}

}

else

{

end = endPage – 5;

for (int i = startPage; i <= endPage; i++)

{

LoadPagingPage(sb, curPage, i,

"", url);

}

}

}

else

{

if (endPage > 1)

{

while (startPage <= endPage)

{

LoadPagingPage(sb, curPage, startPage,

"", url);

startPage++;

}

}

else

{

LoadPagingPage(sb, curPage, startPage, "1″, url);

}

}

// Next

if (endPage > curPage)

{

LoadPagingPage(sb, curPage, curPage + 1, ">", url);

}

else

{

LoadPagingPage(sb, curPage, curPage, ">", url);

}

//for last page

if (rowCount > 25)

{

LoadPagingPage(sb, curPage, endPage, ">|", url);

}

else

{

LoadPagingPage(sb, curPage, endPage, ">|", url);

}

return sb.ToString();

}

private static void LoadPagingPage(StringBuilder sb, int curPage, int PageNum, string Caption, string url)

{

sb.Append(

"  ");

if (PageNum == curPage && Caption != "|<" && Caption != "<" && Caption != ">" && Caption != ">|")

{

sb.Append("<b>");

sb.Append(PageNum.ToString());

sb.Append("</b>");

}

else

{

sb.Append(

@"<a href=""");

sb.Append(url);

if (url.IndexOf("?") > 0)

{

sb.Append("&p=");

}

else

{

sb.Append("?p=");

}

sb.Append(PageNum.ToString());

sb.Append(@""">

Create a label named lblPaging in you page at the desired location and

after databinding the grid in LoadData() function write the following code.

//Generate paging, first check if there are any querystring parameters or not.

if (Request.QueryString.Count > 0)

{

int loop1, loop2;

StringBuilder sb = new StringBuilder();

StringWriter writer = new StringWriter(sb);

// Load NameValueCollection object.

NameValueCollection coll = Request.QueryString;

// Get names of all keys into a string array.

String[] arr1 = coll.AllKeys;

for (loop1 = 0; loop1 < arr1.Length; loop1++)

{

if (Server.HtmlEncode(arr1[loop1]).ToLower() != "p")

{

writer.Write(Server.HtmlEncode(arr1[loop1]));

String[] arr2 = coll.GetValues(arr1[loop1]);

for (loop2 = 0; loop2 < arr2.Length; loop2++)

{

writer.Write("=" + Server.HtmlEncode(arr2[loop2]));

}

if (loop1 != arr1.Length – 1 && arr1[loop1 + 1].ToLower() != "p")

{

writer.Write("&");

}

}

}

if (!string.IsNullOrEmpty(sb.ToString()))

{

lblPaging.Text = GetPagingString(pageId, pageSize, totalNumberOfRecords, Request.Url.LocalPath + "?" + sb.ToString());

}

else

{

lblPaging.Text = GetPagingString(pageId, pageSize, totalNumberOfRecords, Request.Url.LocalPath);

}

}

else

{

lblPaging.Text = GetPagingString(1, 5, totalNumberOfRecords, Request.Url.LocalPath);

}

You can find the complete source code here

Enjoy coding…..!