Asp.net中DataGrid控件的自定义分页

郑 佐 2004-10-28

         使用DataGrid时自带的分页实现起来虽然比较方便，但是效率不高，每次都需要读取所有页（整个记录集），而加载的只是其中一页，造成了资源的浪费，记录多又会使效率变得很低。下面通过DataGrid的自定义分页功能来减少资源使用和提高效率。

         实现的关键是设置AllowCustomPaging属性位True,并把VirtualItemCount属性设置位总的记录数，给分页提供依据，前台的主要代码如下：

<form id="Form1" method="post" runat="server">

                            <TABLE id="Table1" style="FONT-SIZE: 9pt" cellSpacing="1" cellPadding="1" width="450" align="center"

                                     border="1">

                                     <TR>

                                               <TD>

                                                        <asp:datagrid id="DataGrid1" runat="server" Width="100%" AllowPaging="True" AllowCustomPaging="True">

                                                                 <PagerStyle Font-Size="9pt" Mode="NumericPages"></PagerStyle>

                                                        </asp:datagrid></TD>

                                     </TR>

                            </TABLE>

                   </form>

这里使用的数据源还是假设为Northwind的Customers表。

下面是访问单页的存储过程，实现方式很多，不过这个是最普通的，

CREATE PROCEDURE [GetCustomersDataPage]

         @PageIndex INT,

         @PageSize  INT,

         @RecordCount INT OUT,

         @PageCount INT OUT

AS

SELECT @RecordCount = COUNT(*)  FROM   Customers

SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)

DECLARE @SQLSTR NVARCHAR(1000)

IF @PageIndex = 0 OR @PageCount <= 1

         SET @SQLSTR =N''''SELECT TOP ''''+STR( @PageSize )+

''''  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID DESC''''

ELSE IF     @PageIndex = @PageCount - 1            

         SET @SQLSTR =N'''' SELECT * FROM ( SELECT TOP ''''+STR( @RecordCount - @PageSize * @PageIndex )+

''''  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID ASC ) TempTable  ORDER BY CustomerID DESC''''

ELSE         

        SET @SQLSTR =N'''' SELECT TOP  ''''+STR( @PageSize )+'''' * FROM ( SELECT TOP ''''+STR( @RecordCount - @PageSize * @PageIndex )+

''''  CustomerID, CompanyName,Address,Phone  FROM   Customers ORDER BY CustomerID ASC ) TempTable ORDER BY CustomerID DESC''''

EXEC (@SQLSTR)

GO

获取记录数和页数都采用存储过程的输出参数。

获取数据源，这里返回一个DataSet。

先定义了连个数据成员，

private int pageCount;//页数

private int recordCount;//记录数

//获取单页数据

private static DataSet GetCustomersData(int pageIndex,int pageSize,ref int recordCount,ref int pageCount)

{

     string connString = ConfigurationSettings.AppSettings["ConnString"];

     SqlConnection conn = new SqlConnection(connString);

     SqlCommand comm = new SqlCommand("GetCustomersDataPage",conn);

     comm.Parameters.Add(new SqlParameter("@PageIndex",SqlDbType.Int));

     comm.Parameters[0].Value = pageIndex;

[1] [2] [3]  下一页