The GridView is a flexible control that’s quickly becoming my favorite. It makes so many things a breeze. One of these things happens to be pagination. All you need to do to get the data in pages is to add the following 2 lines (the second one being optional):

  1. <asp:GridView
  2. ID="PagedGridView"
  3. AllowPaging=true
  4. PageSize=25
  5. EnableViewState=false
  6. runat="server">
  7. </asp:GridView>

Looks too good to be true? Unfortunately, it is. The way that GridView does pagination is horribly inefficient: it gets all the records from the database, then goes on to discard all the records that it doesn’t need. And it does this every time the page loads. Which doesn’t matter much if your table contains only 100 records, but if it contains 10,000 records or 50,000 records, then it starts to become a problem.

Fortunately, creating a more efficient paging for the GridView is easy, though we need to write more than two lines to make it work.####Getting Back Only the Records That We Want

To make the pagination process more efficient, we want to get from the database only those records that we actually need. Duh! We’ll do this using a nice SQL function called ROWNUMBER(). Unfortunately, this function is available only in SQL Server 2005, which not everyone has. We can’t simply substitute a increment-by-one primary key column for ROWNUMBER() because we’ll probably be deleting rows in the middle, which will screw up anything. If you don’t have SQL Server 2005, here’s a link to a tutorial that shows how to get the same result using a temporary table.

The stored procedure that does use ROW_NUMBER():

  1. ALTER PROCEDURE dbo.EfficientGridViewPaging
  2.  
  3. @pageIndex int,
  4. @pageSize int
  5.  
  6. AS
  7.  
  8. BEGIN
  9.  
  10. WITH Entries AS (
  11.  
  12. SELECT ROW_NUMBER() OVER (ORDER BY ID DESC)
  13. AS RowNumber, random_data1, random_data2, random_data_etc
  14. FROM the_table
  15. )
  16.  
  17. SELECT random_data1, random_data2, random_data_etc
  18.  
  19. FROM Entries
  20.  
  21. WHERE RowNumber BETWEEN (@pageIndex - 1) * @pageSize + 1 AND @pageIndex * @pageSize
  22.  
  23. END

I first came across ROWNUMBER() in article while browsing the Internet as a means of procrastination learning more on SQL Server 2005. (Actually, to be technically accurate, I first learned about ROWNUMBER() while studying for Oracle certification, but I forgot about it completely until the article refreshed my memory. Hehe.) I created a stored procedure from scratch, feeling rather awesome. But as often happens, I found out that I wasn’t the only person to have this problem. ;-)

The stored procedure takes in two parameters (the current page and the maximum number of records to return) and returns the appropriate records.

Total Number of Pages

One rather inconvenient fact is that GridView’s PageCount property is read-only. This means that we need to figure out a way to get the total number of pages. Most implementations I saw have the total number of records as the output parameter of our afore-mentioned stored procedure. But as it’s unlikely that the number of records changes frequently, I chose to create a stored procedure that gets the number of records in the table and store it in the Session variable.

Creating the Links

With that out of the way, creating the links to different pages is fairly straight-forward. Here’s a somewhat sloppy implementation that allows you to go to the first page, the previous page, the next page, or the last page:

  1. using System;
  2. using System.Data;
  3. using System.Configuration;
  4. using System.Web;
  5. using System.Web.Security;
  6. using System.Web.UI;
  7. using System.Web.UI.WebControls;
  8. using System.Web.UI.WebControls.WebParts;
  9. using System.Web.UI.HtmlControls;
  10.  
  11. using System.Data.Sql;
  12. using System.Data.SqlClient;
  13.  
  14. public partial class _Default : System.Web.UI.Page
  15. {
  16. private int pageCount = 0;
  17. private int pageIndex = 0;
  18. private int pageSize = 25;
  19.  
  20. private string dbConn = "Your Connection String — should be in web.config";
  21.  
  22. protected void Page_Load(object sender, EventArgs e)
  23. {
  24. if (Request["page"] == null)
  25. pageIndex = 1;
  26.  
  27. else
  28. pageIndex = Int16.Parse(Request["page"]);
  29.  
  30. if (Session["pageCount"] == null)
  31. {
  32. //Get the total number of pages in the table
  33. using (SqlConnection cn = new SqlConnection(dbConn))
  34. {
  35.     cn.Open();
  36.  
  37.     SqlCommand cmd = cn.CreateCommand();
  38.  
  39.     cmd.CommandText = "GetTotalPages";
  40.     cmd.CommandType = CommandType.StoredProcedure;
  41.  
  42.     //The stored procedure returns total number of records
  43.     pageCount = Convert.ToInt16(cmd.ExecuteScalar());
  44. }
  45.  
  46. pageCount = pageCount / pageSize;
  47.  
  48. Session["pageCount"] = pageCount;
  49. }
  50.  
  51. else
  52. pageCount = (int)Session["pageCount"];
  53.  
  54. GetCurrentPageData();
  55. }
  56.  
  57. private void GetCurrentPageData()
  58. {
  59. //Do error processing here
  60. if (pageIndex > pageCount || pageIndex < 1)
  61. return;
  62.  
  63. SqlDataSource sds = new SqlDataSource();
  64.  
  65. sds.ConnectionString = dbConn;
  66.  
  67. sds.SelectCommand = "EfficientGridViewPaging";
  68. sds.SelectCommandType = SqlDataSourceCommandType.StoredProcedure;
  69.  
  70. sds.SelectParameters.Add("pageIndex", pageIndex.ToString());
  71. sds.SelectParameters.Add("pageSize", pageSize.ToString());
  72.  
  73. PagedGridView.DataSource = sds;
  74. PagedGridView.DataBind();
  75.  
  76. UpdatePagination();
  77. }
  78.  
  79. private void UpdatePagination()
  80. {
  81. //Update the hrefs to point to the right page
  82. FirstPageLink.HRef = "Default.aspx?page=1";
  83. PrevPageLink.HRef = "Default.aspx?page=" + (pageIndex - 1);
  84. NextPageLink.HRef = "Default.aspx?page=" + (pageIndex + 1);
  85. LastPageLink.HRef = "Default.aspx?page=" + pageCount;
  86.  
  87. //We’re on first page
  88. if (pageIndex <= 1)
  89. {
  90. FirstPageLink.HRef = String.Empty;
  91. PrevPageLink.HRef = String.Empty;
  92. }
  93.  
  94. //We’re on last page
  95. else if (pageIndex >= pageCount)
  96. {
  97. NextPageLink.HRef = String.Empty;
  98. LastPageLink.HRef = String.Empty;
  99. }
  100.  
  101. CurrentPage.Text = "Page " + pageIndex.ToString() + " of " + pageCount.ToString();
  102. }
  103. }
  104.