Introduction
If you are experiencing performance bottleneck on your web applications running DataGrid or GridView paging, then this article can help you to improve the performance of your website. You probably know that implement paging in DataGrid or GridView can be done in very few lines of code. But this comes with the price. As if you deploy the site into production server with thousands of data, then you can start feel that instantly. You probably ask the reason why?.
The reason is because everytime when you navigate from page to page, the DataGrid actually rebind to the DataSet or DataTable and the DataTable will load the entire record into it and then display a small subset of the data into your grid. So regardless of whether you are navigating page 1 or page 2, you can see the amount of time it take will be the same.
Building Custom Paging with Numbering for Your DataGrid or GridView
The solution to overcome this issue, is to write your own custom paging method into your DataGrid or GridView. Since we are writing custom Paging, we will be including the numbering features for the Paging. So instead of navigating through the Next and Previous button, the user can jump to any page that he like.This is a nice features just like what you can see in Google or Yahoo.
For this article, we will be using the Sample Northwind Database. If you don't have the database, you can do a search from Google to download it.
Create New Aspx Page and named it GridViewPaging.aspx
Copy and Paste the code below.
<TABLE BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="100%" CLASS="TablePager">
<TR>
<TD COLSPAN="3">
<ASP:GRIDVIEW ID="GridView1" RUNAT="server">
</ASP:GRIDVIEW>
</TD>
</TR>
<TR>
<TD COLSPAN="3">
<HR />
</TD>
</TR>
<TR>
<TD ALIGN="left" WIDTH="100px">
<ASP:LABEL ID="lblPrev" RUNAT="server"></ASP:LABEL></TD>
<TD ALIGN="center">
<ASP:LABEL ID="lblPageNoFooter" RUNAT="server"></ASP:LABEL>
</TD>
<TD ALIGN="right" WIDTH="100px">
<ASP:LABEL ID="lblNext" RUNAT="server"></ASP:LABEL>
</TD>
</TR>
</TABLE>
From the code above, you can see that we just drag the GridView control and we put few label for navigating between the next, previous and the page counter to show the current page.
Next, we will be building the Stored Procedures that will only retrieve portion of the data that we need.
I basically grab this stored procedures from this article here. If you need further explanation about the stored procedures, then please refer to this article.
CREATE PROCEDURE [dbo].[usp_PageResults_Products]
(
@startRowIndex int,
@maximumRows int,
@totalRows int
)
AS
DECLARE @first_id int, @startRow int
-- A check can be added to make sure @startRowIndex isn't > count(1)
-- from employees before doing any actual work unless it is guaranteed
-- the caller won't do that
-- Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = productid FROM products where discontinued = 0 ORDER BY productid
-- Now, set the row count to MaximumRows and get
-- all records >= @first_id
SET ROWCOUNT @maximumRows
SELECT productid,productname,discontinued from products
WHERE discontinued = 0 and productid >= @first_id
ORDER BY productname
SET ROWCOUNT 0
Select @totalRows = COUNT(ProductID) FROM Products where discontinued = 0
For the code behind of the GridViewPaging.aspx, we need to declare this two variables on top of the page
protected int iCurrentPage;
protected const int iPageSize = 10;
The iCurrentPage represents the current page of the GridView, and the iPageSize is the total number of records displayed on each page. You can also allow the user to adjust the page size using a DropDownList, but that is not covered in this article
Next, we will be writing code for binding the GridView
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindData();
}
}
private void BindData()
{
if (Request.QueryString["Page"] != null)
{
iCurrentPage = Convert.ToInt32(Request.QueryString["Page"]);
}
else
{
iCurrentPage = 1;
}
int iStartRowIndex = ((iCurrentPage -1 ) * iPageSize) + 1;
if (iStartRowIndex == 0)
{
iStartRowIndex = 1;
}
SqlConnection oConn = new SqlConnection(ConfigurationManager.AppSettings["ConnString"].Trim());
SqlCommand oCmd = oConn.CreateCommand();
oCmd.CommandText = "usp_PageResults_Products";
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.Parameters.Add("@startRowIndex", SqlDbType.Int, 4).Value = iStartRowIndex;
oCmd.Parameters.Add("@maximumRows", SqlDbType.Int, 4).Value = iPageSize;
oCmd.Parameters.Add("@totalRows", SqlDbType.Int, 4).Direction = ParameterDirection.Output;
SqlDataAdapter oDa = new SqlDataAdapter(oCmd);
DataSet ds = new DataSet();
oDa.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
iTotalRows = (int)oCmd.Parameters["@totalRows"].Value;
BindPaging();
}
If you see the code above, you can see that we are binding the DataGrid to the stored procedures that we created before. The important parameter that we need to pass to the stored procedures is the @StartRowIndex and the logic for the stored proc is quite simple.
If the CurrentPage is still Page 1, then we will be passing the StartRowIndex = 1
If the CurrentPage is still Page 2, then we will be passing the StartRowIndex = 11
If the CurrentPage is still Page 3, then we will be passing the StartRowIndex = 22
Now, the last part for this entire DataGrid and GridView Project, will be writing the code for displaying the Page Numbering and Next and Previous Link Button.
private void BindPaging()
{
double iPageCount = Math.Ceiling(Convert.ToDouble(iTotalRows / iPageSize));
if (lblPageNoFooter.Text.Length <= 0)
{
//Binding Page Footer
for (int i = 1; i <= iPageCount; i++)
{
if (i == iCurrentPage)
{
lblPageNoFooter.Text += " <B>" + i + " </B> ";
}
else
{
if (i == 1)
{
lblPageNoFooter.Text += " <A HREF='Default.aspx'>" + i + " </A>";
}
else
{
lblPageNoFooter.Text += " <A HREF='Default.aspx?Page=" + i + "'>" + i + " </A>";
}
}
}
}
//Binding Prev and Next
if (iPageCount > 1)
{
if (iCurrentPage != 1)
{
lblPrev.Text = " <A HREF='Default.aspx?Page=" + (iCurrentPage - 1) + "'><< Previous </A>";
}
if (iCurrentPage != iPageCount)
{
lblNext.Text = " <A HREF='Default.aspx?Page=" + (iCurrentPage + 1) + "'>Next >></A>";
}
}
else
{
lblPrev.Visible = false;
lblNext.Visible = false;
}
}
Conclusion
As you can see from the sample code above, we are building our own custom paging solutions and you can actually compare the performance between default paging provided by the .NET framework and this one. You will notice huge performance difference especially if you are retrieving thousands of data from the database.
Download Source Code