WizardWu 編程網

一位台灣的工程師,接觸 .NET 逾十年,近年研究 SQL Server、Performance Tuning、手機應用

博客园 首页 新随笔 联系 订阅 管理
  107 Posts :: 0 Stories :: 1097 Comments :: 35 Trackbacks

继上一篇帖子「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)

本帖的示例代码下载点:
http://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 控件里的数据,呈现方式预设是由大到小、从新到旧排序:

figure1
图 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 当机。

figure2
图 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。

Code



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

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 语句。

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

 

Code

 

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

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」即可。

Code


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

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

posted on 2008-08-06 03:28  WizardWu  阅读(...)  评论(... 编辑 收藏