Custom Paging in the GridView Control
I wanted to perform custom paging in the GridView control but was unable to find any articles on this topic. The article that came the closest was from Doug Stevens which talked about Custom Paging in the Datagrid control. That article formed the basis of this post and my upcoming article about "Custom Paging in the GridView Control" on GridViewGuy. In this post I will just walked through the process of creating custom paging and will explain in more detail in my article. The first thing that you need for custom paging is a stored procedure that can get the records based on the currentPage and the maximum rows. Greg Hamilton posted an awesome article about custom paging through a stored procedure without the use of temp tables. You can read the article at http://www.4guysfromrolla.com/webtech/042606-1.shtml. I used the same procedure in my in my demo and it works great. I had to make a very small change to accomodate my design. Check out the stored procedure below:
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
The lines that are BOLD are added by me. The @totalRows return the number of rows contained in the table.
Now, let's check out the ASP.NET and C# code:
I have used Button controls for "Next" and "Previous" navigation. These buttons are contained outside the GridView control.
Here is the complete C# code:
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected int currentPageNumber = 1;
private const int PAGE_SIZE = 10;
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
BindData();
}
}
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;
}
}
private int CalculateTotalPages(double totalRows)
{
int totalPages = (int) Math.Ceiling(totalRows / 10);
return totalPages;
}
// 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();
}
}
And here is the ASP.NET page code:
<asp:GridView ShowFooter="True" ID="gvProducts" AllowPaging="True" runat="server" CellPadding="4" Font-Names="Verdana" ForeColor="#333333" GridLines="None" >
<PagerTemplate>
</PagerTemplate>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
<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" /><br />
<br />
<asp:Button ID="Button1" runat="server" Text="Do PostBack" />
Page
<asp:Label ID="lblCurrentPage" runat="server" />
of
<asp:Label ID="lblTotalPages" runat="server" />
</div>
I will post an update to this post as soon as I am done with the article.
Thanks,
Azam