GridView control provides you with an easy way to display the number of items on the page without taking much space with the help of paging. You can enable the paging feature in the GridView control within seconds. The built in paging is pretty good if you are fetching less than 100 items as soon as the number of items increases the performance suffers. The main reason for the performance kill is that whenever you go to a new page of the GridView it fetches all the items from the database. In this article I will demonstrate how you can use custom paging to improve the performance of the GridView paging.
Introduction:
GridView control provides you with an easy way to display the number of items on the page without taking much space with the help of paging. You can enable the paging feature in the GridView control within seconds. The built in paging is pretty good if you are fetching less than 100 items as soon as the number of items increases the performance suffers. The main reason for the performance kill is that whenever you go to a new page of the GridView it fetches all the items from the database. In this article I will demonstrate how you can use custom paging to improve the performance of the GridView paging.
Database and Stored Procedure:
I will be using the good old Northwind database. The stored procedure is written by Greg Hamilton. You can view Greg Hamilton's article A More Efficient Method for Paging Through Large Result Sets on www.4guysfromrolla.com. Greg created a very efficient stored procedure that works without the use of temporary table or the TABLE variable.
I have modified the stored procedure by adding few fields. Here is the modified version of the stored procedure:
CREATE PROCEDURE [usp_GetProducts]
@startRowIndex int,
@maximumRows int,
@totalRows int OUTPUT
AS
DECLARE @first_id int, @startRow int
SET @startRowIndex = (@startRowIndex - 1) * @maximumRows
IF @startRowIndex = 0
SET @startRowIndex = 1
SET ROWCOUNT @startRowIndex
SELECT @first_id = ProductID FROM Products ORDER BY ProductID
PRINT @first_id
SET ROWCOUNT @maximumRows
SELECT ProductID, ProductName FROM Products WHERE
ProductID >= @first_id
ORDER BY ProductID
SET ROWCOUNT 0
-- GEt the total rows
SELECT @totalRows = COUNT(ProductID) FROM Products
GO
I highly recommend that you check out Greg’s article in which he explained in detail how the stored procedure works.
Displaying Paged Data on the Page:
I will be paging through the records using the “Next” and the “Previous” buttons. The Label control will display our current location in the paged GridView. Let’s first set up some of the variables.
protected int currentPageNumber = 1;
private const int PAGE_SIZE = 10;
The currentPageNumber represents the current page of the GridView and the PAGE_SIZE 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 need to bind the data source to the GridView. Let’s check out the BindData method as a whole and later I will dissect it so you will have a better idea.
private void BindData()
{
string connectionString = "Server=localhost;Database=Northwind;Trusted_Connection=true";
SqlConnection myConnection = new SqlConnection(connectionString);
SqlCommand myCommand = new SqlCommand("usp_GetProducts", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.AddWithValue("@startRowIndex", currentPageNumber);
myCommand.Parameters.AddWithValue("@maximumRows", PAGE_SIZE);
myCommand.Parameters.Add("@totalRows", SqlDbType.Int, 4);
myCommand.Parameters["@totalRows"].Direction = ParameterDirection.Output;
SqlDataAdapter ad = new SqlDataAdapter(myCommand);
DataSet ds = new DataSet();
ad.Fill(ds);
gvProducts.DataSource = ds;
gvProducts.DataBind();
// get the total rows
double totalRows = (int)myCommand.Parameters["@totalRows"].Value;
lblTotalPages.Text = CalculateTotalPages(totalRows).ToString();
lblCurrentPage.Text = currentPageNumber.ToString();
if (currentPageNumber == 1)
{
Btn_Previous.Enabled = false;
if (Int32.Parse(lblTotalPages.Text) > 0)
{
Btn_Next.Enabled = true;
}
else
Btn_Next.Enabled = false;
}
else
{
Btn_Previous.Enabled = true;
if (currentPageNumber == Int32.Parse(lblTotalPages.Text))
Btn_Next.Enabled = false;
else Btn_Next.Enabled = true;
}
}
Now, let’s take a look at the above code in more detail. I am sending the currentPageNumber and the PAGE_SIZE into the database so I can get the data for the current page. The totalRows variable returns the total number of rows in the table. Once, I got the totalRows I calculated the total number of pages that will be used for this GridView. The total number of pages are calculated by using a small helper function.
private int CalculateTotalPages(double totalRows)
{
int totalPages = (int) Math.Ceiling(totalRows / PAGE_SIZE);
return totalPages;
}
At the end of the BindData method there are some conditional checks which ensure that the next and previous buttons are only displayed when applicable.
Attaching the Events to the Buttons:
The final thing that is left is to attach the events to the Button controls. Check out the following code in which I created two button controls.
<asp:Button ID="Btn_Previous" CommandName="Previous" runat="server" OnCommand="ChangePage" Text="Previous" />
<asp:Button ID="Btn_Next" runat="server" CommandName="Next" OnCommand="ChangePage" Text="Next" />
Both the buttons calls the ChangePage event which is shown below:
// This method will handle the navigation/ paging index
protected void ChangePage(object sender, CommandEventArgs e)
{
switch (e.CommandName)
{
case "Previous":
currentPageNumber = Int32.Parse(lblCurrentPage.Text) - 1;
break;
case "Next":
currentPageNumber = Int32.Parse(lblCurrentPage.Text) + 1;
break;
}
BindData();
}
The ChangePage event is used to change the page number of the GridView and also to update the Label text by calling the BindData method.
Take a look at the following screen shot:
Conclusion:
In this article you learned how to use the custom paging in the GridView control. Custom paging let’s you pull out the subset of the information that is needed and not the complete information. This approach improves performance when you are retrieving large sets of data.
I hope you liked the article, happy coding!
