Implement Custom Paging in the ASP.Net Datagrid Control...

原创 2004年10月06日 21:23:00

By: John Kilgo spacer.gif Date: February 22, 2003spacer.gifdownload.gifspacer.gifDownload the code. spacer.gif <?XML:NAMESPACE PREFIX = ASP />spacer.gifPrinter Friendly Version

The inbuilt paging mechanism of the ASP.Net datagrid control is convenient, but can be very inefficient. The problem with the inbuilt system is that the entire resultset is gathered again and again with each page change. Assume you have a table with 200 rows in it and that you are displaying 10 rows at a time. Everytime you change pages, all 200 rows are being returned again. The datagrid then has to do the work of sorting out which 10 rows you want to see. As you deal with larger tables, the problem only gets worse.

With custom paging you can return only the 10 rows being requested each time the page is changed. This is much more efficient. You also have more options as to the style of the paging mechanism. There is nothing wrong with NumericPages or NextPrev, but sometimes I want to do something a little different. In this example, we will be accessing the Northwind Products table, and our paging mechanism will be implemented outside the datagrid rather than within it. I'm not particularly proud of the method I chose, but perhaps it will give you some ideas on other approaches. As usual we will employ an aspx page for the datagrid design and then do all the work in a .vb code-behind file. First the .aspx page.

As you can see we have a very simple datagrid design, setting only a few properties with most of them being cosmetic in nature. We do, however, set AllowCustomPaging to True. Since we will be handling paging ourselves in code we don't need to set PagerStyle attributes or even set up an event for paging. Below the datagrid we have added two label controls to hold the current page number and the count of total pages. This is so we can display something like "Page 3 of 8". Below the labels we have created our own paging "control" using four buttons to allow paging to the first page, previous page, next page, and last page. I have used the poor man's version of VCR buttons. You could get fancy and use image buttons, or you could just use text-based link buttons.

<%@ Page language="vb" Src="CustPageDataGrid.aspx.vb" Inherits="DotNetJohn.CustPageDataGrid" %>

<form Runat="Server" ID="Form1">
<asp:DataGrid ID="dtgProd"

  <AlternatingItemStyle BackColor="#DCDCDC" />
  <ItemStyle ForeColor="Black" BackColor="#EEEEEE" />
  <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084" />

<asp:Label id="lblCurPage" runat="server" />
<asp:Label id="lblTotPages" runat="server" />
<asp:Button id="btnFirst"
            Text=" |<< "
            CommandName="First" />
<asp:Button id="btnPrev"
            Text=" < "
            CommandName="Prev" />
<asp:Button id="btnNext"
            Text=" > "
            CommandName="Next" />
<asp:Button id="btnLast"
            Text=" >>| "
            CommandName="Last" />

And now for the code-behind page. In order to make it a little easier to see and discuss, we present the code-behind file in three sections. This first section is the top of the file down through the Page_Load sub routine. Before getting to the code I should point out that the techniques used in this example program only work when there is an identity column in the table. If there is no identity table, you could write a stored procedure which creates a temporary table that does include an identity column, copy the permanent table into the the temp table, and use the temp table to return results to the program.

The main purpose of the Page_Load routine is to determine the number of rows of data we will be dealing with. We do a SELECT Count(*) from the table and then store the result in the DataGrid's VirtualItemCount property. We then set our current page property (intCurPageNum) to 1 and call BindTheGrid() to do the databinding.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Namespace DotNetJohn

Public Class CustPageDataGrid : Inherits System.Web.UI.Page

  Protected dtgProd As System.Web.UI.WebControls.DataGrid
  Protected lblCurPage As System.Web.UI.WebControls.Label
  Protected lblTotPages As System.Web.UI.WebControls.Label
  Protected btnNext As System.Web.UI.WebControls.Button
  Protected btnPrev As System.Web.UI.WebControls.Button
  Protected intCurPageNum As Integer

  Dim objConn As SqlConnection
  Dim strSelect As String
  Dim intStartIndex As Integer
  Dim intEndIndex As Integer

  Sub Page_Load (sender As Object, e As EventArgs)
    Dim objCmd As SqlCommand

    objConn = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    If Not IsPostBack Then
      ' Get Total Rows
      strSelect = "Select Count(*) From Products"
      objCmd = New SqlCommand( strSelect, objConn )
      dtgProd.VirtualItemCount = objCmd.ExecuteScalar()
      intCurPageNum = 1
    End If
  End Sub

Now for the "BindTheGrid" sub-routine. In the upper part of the routine we use a paramaterized query to get the first 10 rows from the table. I say 10 rows because that is the value of dtgProd.PageSize. As you can see we are using a starting index (intStartIndex) and an ending index (intEndIndex) to specify the range of rows we want from the table using the ProductID (identity column). We then fill a DataSet with the results of the query, and set our current page label (lblCurPage.Text) to the current page number.

If this is the first time through the routine (Not Page.IsPostBack) we set a variable (intTotRecs) to hold the total number of rows we are dealing with in the table, and another variable (decTotPages) to the total number of pages (of 10 rows) we are dealing with. The problem with this variable (decTotPages) is that it is equal to 7.7. To fix this display problem we using the System.Math.Ceiling function to "round up" to the nearest integer. That is so we can display "Page 1 of 8" rather than "Page 1 of 7.7".

In the last section of BindTheGrid we test the value of intCurPageNum. If we are on the first page we want our previous page button to be disabled. If we are on the last page we want our next page button to be disabled. Otherwise, both buttons should be enabled.

  Sub BindTheGrid()
    Dim dataAdapter As SqlDataAdapter
    Dim dataSet As DataSet

    intEndIndex = intStartIndex + dtgProd.PageSize
    strSelect = "Select ProductID, ProductName, SupplierID, CategoryID, " _
              & "UnitPrice, UnitsInStock, Discontinued " _
              & "From Products Where ProductID > @startIndex " _
              & "And ProductID <= @endIndex Order By ProductID"
    dataAdapter = New SqlDataAdapter( strSelect, objConn )
    dataAdapter.SelectCommand.Parameters.Add( "@startIndex", intStartIndex )
    dataAdapter.SelectCommand.Parameters.Add( "@endIndex", intEndIndex )
    dataSet = New DataSet
    dataAdapter.Fill( dataSet )
    dtgProd.DataSource = dataSet
    lblCurPage.Text = intCurPageNum.ToString()

    If Not Page.IsPostBack Then
      Dim intTotRecs As Integer = CInt(dtgProd.VirtualItemCount)
      Dim decTotPages As Decimal = Decimal.Parse(intTotRecs.ToString()) / dtgProd.PageSize
      lblTotPages.Text = (System.Math.Ceiling(Double.Parse(decTotPages.ToString()))).ToString()
    End If

    Select Case intCurPageNum
      Case 1
        btnPrev.Enabled = False
        btnNext.Enabled = True
      Case Int32.Parse(lblTotPages.Text)
        btnNext.Enabled = False
        btnPrev.Enabled = True
      Case Else
        btnPrev.Enabled = True
        btnNext.Enabled = True
    End Select
  End Sub

And now to complete the code-behind page. You may recall that on the .aspx page when we defined our page navigation buttons we included an OnCommand method to raise the Command event (Nav_OnClick). It is here that we set the current page number (intCurPageNum) to be viewed. intCurPageNum is set as shown depending on which navigation button was clicked. We then set intStartIndex (one of our SELECT statement parameters) to the current page number minus 1 times the datagrid .PageSize propery. We then call BindTheGrid() and we are done.

  Sub Nav_OnClick(sender as Object, e As system.Web.UI.WebControls.CommandEventArgs)
    Select Case e.CommandName
      Case "First"
        intCurPageNum = 1
      Case "Last"
        intCurPageNum = Int32.Parse(lblTotPages.Text)
      Case "Next"
        intCurPageNum = Int32.Parse(lblCurPage.Text) + 1
      Case "Prev"
        intCurPageNum = Int32.Parse(lblCurPage.Text) - 1
    End Select
    intStartIndex = (intCurPageNum -1) * dtgProd.PageSize()
  End Sub

End Class

End Namespace

The code for custom paging can be a little tricky, but it does have efficiency advantages over the DataGrid's inbuilt paging mechanism. The larger the table you are dealing with the more you need paging. As the table grows larger you do not want to be returning the entire table in the resultset every time you change pages.

You may run the sample program here.
You may download the code here.

ASP.NET 2.0 DataGrid Custom Paging[by Ted Heng]

ASP.NET 2.0 DataGrid Custom PagingAuthor: Ted Heng, Senior Consultant, Premier Partner ISV, US Enter...
  • kielin
  • kielin
  • 2010年07月02日 15:45
  • 461

Custom Paging in ASP.NET 2.0 with SQL Server 2005

From: IntroductionA common pattern in web de...
  • simon69
  • simon69
  • 2008年10月24日 10:58
  • 353


ASP.NET中利用DataGrid的自定义分页功能 ASP.NET中利用DataGrid的自定义分页功能和存储过程结合实现高效分页 ASP.Net中的DataGrid有内置分页功能, 但是它...
  • u014739775
  • u014739775
  • 2014年05月12日 11:09
  • 1170

为 ASP.NET Datagrid 创建自定义列

Marcie 2003 年 9 月简介不得不承认,为 Microsoft® ASP.NET 编写 Datagrid 代码包括大量的重复工作。尽管我深...
  • 21aspnet
  • 21aspnet
  • 2004年10月24日 22:28
  • 1886

ASP.NET中Web DataGrid的使用指南

  • qdzx2008
  • qdzx2008
  • 2005年09月17日 23:30
  • 799 中一次性更新DATAGRID中所有记录

在asp.net中,如何一次性更新datagrid中的所有记录呢?可以用如下的方法,首先,要对DATAGRID中要更新的列建立模版列,比如:asp:datagrid id="dgPopularFAQs...
  • mdot
  • mdot
  • 2004年12月19日 10:00
  • 1426

*Using the ASP.NET Panel Control...

By: John Kilgo Date: March 6, 2003 Download the code. Printer Friendly Version The Panel Contro...
  • hiyaolee
  • hiyaolee
  • 2004年10月06日 21:10
  • 753


(ASP.NET)修改和删除DataGrid行——数据库访问        本程序涉及到数据库的添加,修改和删除操作。        懒得写了,把界面贴出来,照着界面画就可以了。本例数据库:SqlSe...
  • netboyfly
  • netboyfly
  • 2005年03月16日 17:09
  • 704

ASP.NET中Web DataGrid的使用指南(转自前沿设计网-找到这个真不容易啊!)

关键词:ASP.NET中Web DataGrid的使用指南 很久以前就想写一些关于DataGrid/DataList的东西,但是一直以来,一方面自感所学未深,另一方面,总觉无从下笔,一拖再拖,离刚开...
  • luqingshuai_eloong
  • luqingshuai_eloong
  • 2015年06月16日 17:31
  • 816


  • jly4758
  • jly4758
  • 2013年11月27日 16:32
  • 8526
您举报文章:Implement Custom Paging in the ASP.Net Datagrid Control...