【转】ASP.NET 数据分页第二篇 - 范例下载

继上一篇帖子「ASP.NET 数据分页第一篇 - 探讨分页原理及 SQL Server 2005 的 ROW_NUMBER 函数」后，版工已写了三个 ASP.NET 2.0「数据分页」的范例，提供网友们下载。这三个范例分别针对下列三种数据库，去做「数据分页」的处理：

1. SQL Server 2005 - 使用 ROW_NUMBER 函数 (执行 wizardPager / SqlServer2005.aspx)
2. SQL Server 2000 或任何支持 SELECT TOP n 语法的数据库 (执行 wizardPager / OtherDB.aspx)
3. Sybase ASE 或任何支持 SET ROWCOUNT 语法的数据库 (执行 wizardPagerSybase / SybaseASE.aspx)

本帖的示例代码下载点：
https://files.cnblogs.com/WizardWu/080807.zip

范例直接以 Visual Studio 2005 / 2008 或 IIS 开启即可执行。范例都已经过版工实际测试，可正确执行，若您下载后仍无法执行，请留言告知。


范例里有三支 Stored Procedure，以及两个 ASP.NET 2.0 网站。Stored Procedure 的部分，依您使用的数据库，建立其中一支即可；ASP.NET 的部分，若您使用的是 SQL Server 2000 / 2005 数据库，请用 VS 2005 或 VS 2008 或 IIS，开启「wizardPager」网站，默认会去撷取 Northwind 数据库的 Orders 数据表；若您使用的是 Sybase ASE 数据库，请开启「wizardPagerSybase」网站。

这几支 Stored Procedure 是可共享的，亦即不管您的网站中有多少支程序用到 GridView 控件，都可统一透过此一 Stored Procedure 去处理「分页」的工作，而不必针对每一支程序都去写一支对应的 Stored Procedure。

三个范例的执行画面，都如下图 1 所示，上方有两个 TextBox 让使用者输入搜寻关键词 (SELECT LIKE 模糊查询)，GridView 控件里的数据，呈现方式预设是由大到小、从新到旧排序：


图 1


三个范例是搭配 ObjectDataSource 控件，采用 4-Tier 架构，架构图如下所示：

使用者 (Browser + GridView)
　↓　↑
BLL (各支程序「专属」的商业逻辑层)
　↓　↑
DAL (各支程序「专属」的数据存取层)
　↓　↑
DAL2 (各支程序「共享」的数据存取层)
　↓　↑ 
DB server (Stored Procedure 或 RowNumber 函数)

之所以会多加一层 DAL2，是为了将 DAL 层中，每支程序中重复的代码，如：数据库的开启和关闭联机、Exception Handling 的代码抽出，避免同样的代码重复出现在每支程序里面。


参考下图 2，若您在 DAL2 层，例如：App_Code/DAL2/SqlServer2005_DAL2.cs 在 Visual Studio 里下断点，会看到经过「数据分页 (pager)」的处理后，实际从数据库撷取的数据，存储至 AP server 中的 DataTable / DataSet 中的记录笔数，就真的只有 10 笔，亦即 GridView 每一页所要显示的行数。若未经此分页处理，按照 GridView 和 SqlDataSource、ObjectDataSource 控件的默认行为，是对「整个」数据表的「所有」数据全部撷取出来，此时在图 2 中的整数 jj，即会如版工我上一篇帖子所提到的，会显示一百万笔，而非只有 10 笔，如此一来，会浪费极大量的网络频宽、server 的内存和 CPU 等系统资源，GridView 换页或排序时的性能会大幅下降，甚至因内存不足造成 server 当机。


图 2　实际存储至 IIS 内存里的 DataTable 的记录笔数，仅有 10 笔


以下再针对三种数据库的算法作简介，并贴出重点代码：

--------------------------------------------------
1. SQL Server 2005 - 使用 ROW_NUMBER 函数：

已于上一篇帖子「ASP.NET 数据分页第一篇 - 探讨分页原理及 SQL Server 2005 的 ROW_NUMBER 函数」的下半部提过，ROW_NUMBER 函数可对已撷取的数据，再提供「排序和自动给号」的功能，亦即再赋予一个「自动编号」的「字段；列 (column)」，且执行性能 (performance) 比传统的做法 - 重复建立、删除「临时数据表 (Temporary Table)」要来得好，处理「分页」时也比较节省 DB server 的资源；但缺点是此种做法仅限于 SQL Server 2005 数据库，网站将来会无法移植到其它厂牌的数据库，或旧版的 SQL Server。

 

-- 計算要顯示的資料列的起、迄資料列索引
-- 計算 @LowerCount (起始撈取的第一筆的資料列索引)、@UpperCount (要撈取的最後一筆的資料列索引)
-- @StartRowIndex 資料列索引
    if (@StartRowIndex = 0)
    begin
        SET @LowerBand  = 0
        SET @UpperBand  = @PageSize 
    end
    else
    begin
        SET @LowerBand  = @StartRowIndex + 1
        SET @UpperBand  = @StartRowIndex + @PageSize 
    end

/* *** 以下為實際要傳回前端 GridView 的 DataTable 內容 *** */
SET @sqlDataTable = 'WITH tempTable AS
(SELECT ' + @columnName + ', ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') AS tempRowNum
FROM ' + @tableName + ' WHERE ' + @sqlWhere + ') 
SELECT * FROM tempTable WHERE tempRowNum BETWEEN ' + cast(@LowerBand as nvarchar) + ' AND ' 
+ cast(@UpperBand as nvarchar) + ';'

exec (@sqlDataTable)

 

 

--------------------------------------------------

2. SQL Server 2000 或任何支持 SELECT TOP n 语法的数据库：

使用两组 SELECT TOP n 语法，亦即所谓的「子查询 (Subquery)」，原理如下图 3 所示。假设数据库里有一百万笔记录，当使用者单击了 GridView 的页码后，希望撷取第 4090 笔到第 5000 笔之间的这 10 笔记录，就先用「子查询」中，最内层的 SELECT TOP n 语法，先撷取出 DESC 反向排序后的 5000 笔记录，再从这 5000 笔记录中，用 ASC 顺向排序的方式撷取出所要的那 10 笔记录。若 GridView 显示时，希望数据能从大到小、从新到旧排序，可对这 10 笔记录再做 DESC 反向排序，亦即在「子查询」的最外圈，再加上一层 SELECT TOP n 语句。


图 3　传统做法，夹挤式的 SELECT TOP 语句

 

-- 計算要顯示的資料列的起、迄資料列索引
-- 計算 @LowerCount (起始撈取的第一筆的資料列索引)、@UpperCount (要撈取的最後一筆的資料列索引)
-- @StartRowIndex 資料列索引
    if (@StartRowIndex = 0)
    begin
        SET @LowerBand  = 0
        SET @UpperBand  = @PageSize 
    end
    else
    begin
        SET @LowerBand  = @StartRowIndex + 1
        SET @UpperBand  = @StartRowIndex + @PageSize 
    end


-- 用來辨識是否在最後一頁
SET @RemainRowCount = @rowCount - @StartRowIndex

/* *** 以下為實際要傳回前端 GridView 的 DataTable 內容 *** */    
-- 子查詢 (SELECT TOP n 夾擠式查詢)
if (@RemainRowCount > @PageSize)    -- 若還未到最後一頁
    begin
    SET @sqlDataTable = 'SELECT TOP ' + cast(@PageSize as nvarchar) +  ' ' + @columnName + ' FROM (
    SELECT TOP ' + cast(@PageSize as nvarchar) + ' ' + @columnName + ' FROM 
        (SELECT TOP ' + cast(@UpperBand as nvarchar) + ' ' + @columnName + ' FROM ' + @tableName + 
        ' WHERE ' + @sqlWhere + ' ORDER BY ' + @orderBy1 + 
        ') AS tempTable Order By ' + @orderBy2 + ') as tempTable2 
    Order By ' + @orderBy1
    end
else        -- 若已經到最後一頁，且剩下不足一頁 @PageSize 數量的零星資料列
    begin
    SET @sqlDataTable = 'SELECT TOP ' + cast(@RemainRowCount as nvarchar) +  ' ' + @columnName + ' FROM (
    SELECT TOP ' + cast(@RemainRowCount as nvarchar) + ' ' + @columnName + ' FROM 
        (SELECT TOP ' + cast(@UpperBand as nvarchar) + ' ' + @columnName + ' FROM ' + @tableName + 
        ' WHERE ' + @sqlWhere + ' ORDER BY ' + @orderBy1 + 
        ') AS tempTable Order By ' + @orderBy2 + ') as tempTable2 
    Order By ' + @orderBy1
    end

exec (@sqlDataTable)

 

--------------------------------------------------

3. Sybase ASE 或任何支持 SET ROWCOUNT 语法的数据库

因为 Sybase ASE 数据库在 12.5.3 以前的版本，不支持「SELECT TOP n」语法，因此必须使用「SET ROWCOUNT」语法来撰写「分页」。其原理为，假设数据表有一百万笔记录，使用者现在单击了 GridView 某一页的页码，欲选取第 5001 ~ 5010 的这 10 笔记录，我们要先从暂存数据表 #tmp2 中，SELECT 出 5010 笔记录 (@UpperBand 变量所存储的即为该数值的索引 5010)，再从暂存数据表 #tmp2 中，DELETE 掉不需要的 5000 笔记录 (@LowerBand 变量所存储的即为该数值的索引 5000)，最后暂存数据表 #tmp2 中，只剩下我们想要的那 10 笔记录，再执行「SELECT * FROM #tmp2」即可。

-- 計算要顯示的資料列的起、迄資料列索引
    -- 計算 @LowerCount (起始撈取的第一筆的資料列索引)、@UpperCount (要撈取的最後一筆的資料列索引)
    -- @PageIndex : 要顯示的這一頁的頁面索引。配合 .NET 定義 : 第一頁的索引編號為 0，依序加 1，依此類推
    if (@PageIndex = 0)     --若為第一頁
    begin
        SET @LowerBand  = 0
        SET @UpperBand  = @PageSize 
    end
    else
    begin
        SET @LowerBand  = @PageIndex * @PageSize 
        SET @UpperBand  = (@PageIndex + 1) * @PageSize 
    end

    -- 一組動態 sql (@tmp_sql)
    -- @sql 為實際從 DAL2 丟進來的完整 SELECT SQL 陳述式
    -- SET ROWCOUNT 功能，類似於 SELECT TOP n
    SELECT @tmp_sql = ' SET ROWCOUNT ' + rtrim(ltrim(convert(char, @UpperBand))) + ' '
    SELECT @tmp_sql = @tmp_sql + ltrim(@sql) 

    -- 若目前所在頁數不是第一頁，則再刪除不需要的資料列(@LowerBand)
    If @LowerBand > 0  
    Begin 
        SELECT @tmp_sql = @tmp_sql + ' SET ROWCOUNT ' + rtrim(ltrim(convert(char, @LowerBand))) + ' DELETE FROM #tmp2 '
    End 
    
    -- SET ROWCOUNT 0 表示資料表「全選」
    SELECT @tmp_sql = @tmp_sql + ' SET ROWCOUNT 0 ' 
    SELECT @tmp_sql = @tmp_sql + ' SELECT * FROM #tmp2'

    EXEC(@tmp_sql)

 

--------------------------------------------------

版工我相信，本帖提供下载的范例，一定还有许多改进空间，但至少可提供某些程序员救急之用。以台湾的软件业来讲，一些大主管、大老板、PM，在乎的只是 project 的速成、能否赶快赚到钱，有很高比率的企业的很高很高的高高层，他们根本不在乎软件质量，甚至可能找一些以前写 PHP、C/C++、Java/JSP 的人，临时捉来写 ASP.NET。若不幸遇到此种情形，便可直接引用本帖所提供的，有如即食面般、可快速套用的「数据分页」解决方案，甚至达到零代码套用，而不用再花时间学习怎么写一堆分页用的 .NET 自订 Class。

 

                                                                                --------------转自 WizardWu：探讨分页原理及 SQL Server 2005 的 ROW_NUMBER 函数（二）