一、 工作的层次
UI层和逻辑层。
UI层:显示首页、末页、上一页、下一页、页号导航、文本框输入页号;共计多少条记录、多少页、当前页号等信息。
逻辑层:提供分页算法(SQL语句),根据分页控件的属性,在运行的时候生成分页用的SQL语句。
二、 流程
l 设置分页控件的属性
l 根据算法和属性生成SQL语句,通过“我的数据访问层”访问数据库
l 得到记录集(比如DataTable)
l 把记录集绑定到指定的控件(比如DataGrid)
三、 分页算法
1、 数据库因素
因为不同的数据库对于T_SQL都有不同的标准,在分页的地方差别就更大了。比如MSSQL2000可以使用嵌套的top ,而其它的数据库就不可以。它们的差别是很大的。
理想的情况下是不同的数据库生成相对应的分页算法,但是由于我目前只使用MSSQL,所以其他数据库的算法还没有研究,不过听说都挺方便的。
2、 分页要求
第二个要考虑的就是分页的要求。好多人都在寻求一个通用的算法,通用的算法可以找到,但是要牺牲一些效率。
A 简单分页
顾名思义,就是最简单的分页情况,按照一个字段来排序,而且排序字段的值没有重复(或者很少有重复)的情况。比如新闻列表,帖子列表。
B 复杂分页
上面的情况确实是很简单的,我们来看一下复杂一点的情况:按照多个字段来排序,最后一个排序字段没有重复的值;按照多个字段(或者一个字段)排序,最后一个字段有很多的重复值。
好像是两种情况,但是后者可以转换为前者,再加一个没有重复值的字段最为最后一个排序字段,这样后一种情况就变成了前一种情况了。
C 主键
我的看法是每一个表都要有一个主键,而且是单一字段的主键(不是复合主键)。复合主键会带来很多的麻烦,应该尽量避免,方法也很简单,在原来的设计上加一个自增的int字段就可以了,把这个自增的字段最为主键即可。
为什么提倡单一主键呢?因为这样可以提高效率,不仅在分页的时候,其他的地方也会很方便。
3、 实际情况
对于一个列表页面来说,哪个页面访问率最高呢?毫无疑问是第一页。大多数情况都是先看第一页的,所以我感觉有必要为第一页单独写一个分页算法,任意页再写一个算法,如果有必要的话最后一页也要再写一个算法。
第一页单独写分页算法的另一个原因是
——好写(对于MSSQL来说)select top 20 * from table where … order by …
。如果排序字段有索引的话,那么这样的语句的效率是最高的,而且where 和order by 可以随意添加。
4、 具体算法(MSSQL数据库)
A 高效算法
这是一个非常追求效率的算法,依据MSSQL的特性,为简单分页的情况量身定做的。
第一个特性:select top 11 @id = ID from Table
Top 和 给变量赋值都是很常用的方法,但是这种组合不太常见吧。这是我在一个偶然的情况下发现的,这么写有什么作用呢?先来看看@id会得到什么值。@id 得到的是最后一条记录的ID字段的值,前面的记录的值会被覆盖。
假设分页要求是:每页显示10条记录,按照ID字段升序显示。那么这时候 select top 10 * from Table where ID >= @ID 得到的记录集就是第二页所需要的数据。第三页的数据只要把第一个语句 top 后面的 “11”改成“21”就可以了。公式:PageSize * (PageIndex - 1) + 1
要想使用这个特性必须满足几个条件:排序字段只能有一个,排序字段的值不能有太多重复的,有重复值会造成分页不准,甚至无法翻到下一页的情况。所以这个算法只适用于“简单分页”的情况。不过好在一个网站里面有很多情况都是“简单分页”的情况,随意这个算法还是有价值的。
Ps:这个特性好像只有MSSQL才有,SQLAnywhere是不容许这样写的,除非记录集只有一条记录,oracle 根本就没有top,其它的数据库没有研究过。
思路:先定位(数数),后取记录集(ID >= 的方法)。
优点:第一个语句只取一个字段,即使是top 10000也可以把占用的资源降到最低。如果排序字段有索引的话效果更佳。
B 一般算法
针对上面的算法的不足,可以采用这个算法,颠颠倒倒法,就是top嵌套。还有一个大名,忘记了(研究出算法后才发现的)。
select [*] from [Table] where [ID] in (
select top 10 [ID] from
(
select top 20 [ID] ,AddedDate from [Table]
order by [AddedDate] desc,[ID]
) as aa order by [AddedDate] ,[ID] desc
)order by [AddedDate] desc,[ID]
应该有似曾相识的感觉吧,这里呢也是随求了一下效率,并不是最最通用的算法。
思路:先定位(数数),再取主键值,最后取记录集(ID in 的方法)。
优点:中间“运算”部分只提取主键和排序字段,其他的字段一律不取,这样可以节省点内存。(缺点:只能是单一主键,不能是复合主键!)
C 通用算法
select top 10 [*] from
select top 10 [*] from
(
select top 20 [*] from [Table]
order by [AddedDate] desc,[ID]
) as aa order by [AddedDate] ,[ID] desc
) as aa order by [AddedDate] desc ,[ID]
复合主键的情况也可以了,不过最内部的select提取的是所有需要显示的字段,在翻到后面的页的时候效率就慢了。
四、 多种数据库
上面只是考虑了一种数据库(MSSQL)的情况,那么其他的数据库呢?我的解决方法是——更换分页算法。不同的数据库使用不同的分页算法。保证属性不变的情况下根据数据库来组合成不同的SQL语句。实在不行的话再写一个分页控件。
五、 分页方式
PostBack分页。这个和DataGrid自带的那个分页很像。每次分页都是一个回发事件,可以利用ViewState来保存状态,最佳使用环境:后台管理。
URL分页。分页信息通过URL的方式来传递。每次分页相当于重新访问一遍页面,无法使用ViewState来保存状态,最佳使用环境:网站页面。
后台管理往往要保存很多的状态(比如查询条件、文本框里的数据之类的),这时候使用PostBack可以使编程简化不少。
网站的网页一般是不需要使用
ViewState来保存信息的,使用URL分页也可以方便的让访问者直接进入指定页号的页面。另外一个好处就是可以使用“后退”的功能来访问以前访问的
页面。使用PostBack分页的话,在按“后退”的时候会出现“警告: 网页已经过期”的错误页面。
补充:
A
并不是说使用PostBack的方式就不能直接访问指定页号的页面(比如直接访问第五页),也是可以实现的而且很方便,只要在第一次访问的时候看一下
URL里面有没有指定页号,有的话直接跳到指定的页号就可以了。DataGrid自带的分页功能也是可以实现的,一样的道理。
B PostBack分页方式是一个分页控件,URL分页方式是另一个页面。也许您会说这么做太不方便了,我想从一个方式切换到另一种方式还得换一个控件?!
其实这么做有很多的原因。最主要的一个原因是,一开始的分页控件只有PostBack的方式,后来想写URL的时候发现代码已经很混乱了,自己都看不懂了。与其在原有控件上修改还不如重新写一个;
另一个原因呢就是PostBack可以利用ViewState来保存信息,而URL就不可以了,在这方面有比较大的差别,其实URL的要简单得多,因为他不用考虑回发的情况;
再有就是“使用环境”,一个用在网页里面,一个用在后台管理。也就减少了相互转换的可能性。
六、 使用方法
使用起来就非常的简单了,只需要给几个属性赋值就可以了。
七、 优点
1、 不需要存储过程
不知道为什么一提到分页(尤其是高效率的分页)往往就要想到存储过程。不用存储过程就不能分页了吗?想想存储过程里面放的是什么呢?还不是SQL语句嘛。那么为什么不能在程序(分页控件)里面组合SQL语句不呢?
使用存储过程分页有两种方式:一是有一个要分
页的页面就写一个存储过程(有100个几乎就要写100个了)。这样效率是很高也很灵活(可以针对不同的情况使用不同的分页算法),但是也有两个不方便的
地方:增加了存储过程的数量(无论什么东东,一多就不好管理了);查询条件的地方不好处理,要想增加查询字段就得修改存储过程,查询字段越多存储过程也就
越长越不好读懂。
另一种就是写一个通用的存储过程,再存储过程里面组合SQL语句。这样呢效果正好和上面的方式相反(有点变缺点,缺点便有点)。
存储过程的优势之一是“预编译”,请问在存储过程里面组合的SQL能不能预编译?如果不能的话这个优势就没有了,这和在程序里面提交一条SQL语句也就没有什么区别了。不能够针对不同的情况使用适合的分页算法,只能一刀切了。
方便的地方就是可以随意的设置查询条件了,因为都是在存储过程里面组合SQL语句的。
所以我决定放弃存储过程,使用在控件里面组合SQL语句的方式来分页。
2、 减少代码
由于分页控件不仅承担了页面上的工作(上一页、下一页等),还负责分页算法,而且连回发事件都代为处理了,有加之不使用存储过程,所以减少了n多的代码量。存储过程叶酸代码吧。
3、 便于升级
属性都是比较稳定的,升级内部代码、更换数据库、.net framework升级都不会有带大的变动的。另外控件已经使用三年多了,也比较稳定和成熟了。
4、 便于使用
只要知道从哪个表里提取数据,显示哪些字段,一页的记录数,排序字段,查询条件等信息就可以了。其他的都可以忽略。
八、 缺点
1、 对表的设计有一点要求
由于我比较追求效率,而且又是从我自己的习惯出发的,所以呢会有一点限制,比如表要有主键,而且不能是联合主键。当然并不是说结对不可以,只要在放弃一点点效率也是可以支持复合主键的,也许以后我会增加第三个分页算法呢。
2、 适用范围不广
XML分页?XML我还不会呢,所以不能给XML分页。
AJAX的支持?Ajax也不会,所以还不支持。
其它的数据库(Access、Excel除外)的支持还没有实现,只是有了一个思路。
3、 不符合“标准”
也许您会说我的这个分页控件不符合MVC、不符合三层架构等等。我的原则是:好用就行,其他的不管。
4、 需要视图的配合
不知道这个算不算缺点。我发现好多人都不爱使用视图,而我却很喜欢使用,对于大多数的分页情况我都使用了视图来简化SQL语句。当然并不是说不用视图就不能使用我的分页控件了。只不过在多表查询的时候属性值会比较长。
九、 在项目中的作用
1、 网站
由于网站没有太复杂的业务逻辑(电子商务的除外),一般来说呢分页显示数据可以占到网站的一半以上,对于网站的后台管理更是这样,会占到60%以上吧。所以呢把分页处理好了可以大大缩减开发时间,减少出错的概率,方便网站的维护和升级。
2、 软件
虽然没有写过太大的软件,但是对于b/s结构的软件来说分页是一个基本的常用的功能。几乎每个模块都缺少不了。统计报表的地方好像不需要了。处理好这个基本问题会让您的开打轻松不少吧。
3、 个人感觉
对于我个人来说,这个分页控件就是“核心”了。我在写网站的时候一大半的时间都是在围绕分页控件来做。
建立视图——给分页控件的属性赋值——得到记录集——在.aspx页面里面显示“格式化”数据。写代码变成了给分页控件赋值,赋值之后后台也就不需要在写什么代码了。
一、分页控件的工作层次
如果按照三层的划分方式来说,应该算作工作在 UI层 和 逻辑层。
在分页控件内部会调用“数据访问函数库”来访问数据库,得到记录集之后再绑定到指定的显示数据的控件。
当然这里只是打个比方,我并没有按照三层的规范来写这个分页控件,我的目的只是想少写点代码。
二、适用范围
目前适用于 vs2003 和 SQL Server 2000
因为是在这两个环境下开发的,尤其是对于 SQL Server 2000 进行了一些优化。
当然也是可以在 vs2005 和 SQL Server 2005 下使用,只是没有针对 05系列 进行优化。
可以在vs2005的项目里引用 分页控件的dll文件,但是可能需要在电脑上安装 .net1.1 的框架。
三、优点
1、不必使用存储过程就可以达到高效率的分页效果。
2、使用两种(或者多种)分页算法,来达到效率和通用的完美统一。当然也可以使用不同的算法应对不同的数据库。
3、按需所取。如果一页显示20条记录,那么分页控件只会从数据库里提取20条数据。
4、支持查询条件,您可以很方便的添加查询条件,实现复杂的检索功能。
5、利用ViewState 来保存一些信息,节省服务器的资源。
比如在第一次显示数据的时候会统计总记录数,然后把总记录数保存到ViewState里面,当点击下一页的时候不用重新统计。
还有其他的信息也会保存到 ViewState 里面。
6、在百万级数据下也有很好的表现,下面有测试数据,不信的话,可以下载demo亲自测试。
7、使用方便,只需要设置几个属性就可以,不必处理分页时产生的事件。
8、支持多种显示数据的控件,比如DataGrid、DataList、Reapeter、DropDownList等。只要是能够使用DataTable绑定的控件都支持。
9、
可以使用键盘快速翻页。
“左方向”键:向前翻页;
“右方向”键:向后翻页;
PageUp键:上一页;
PageDown键:下一页;
Home:首页;
End:末页;
数字键:1到10页,0表示第十页
四、缺点
1、多表联合查询的时候需要使用视图。就是要先建立一个视图。
2、第一种分页算法不要求数据表一定要有主键,但是第二种分页算法要求表必须有主键,而且不能使联合主键。
3、不能很灵活的应对多种数据库。
4、内部代码比较混乱,05年底写的,一直想整理,但是都没有开始整理,只是做了小的升级和修改bug。
五、使用方法
先在
Page_Load 设置显示数据的控件 比如 DataGrid,
private void Page_Load(object sender, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//设置显示数据的控件,注意,不是ID而是实例
myPage.PubShowDataObject = this.DG ;
if (!Page.IsPostBack)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
SetPage();
}
}
然后根据情况设置分页控件的其它属性
第一种分页算法的属性设置。单字段排序,且排序字段没有重复记录
private void SetPage()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//简单的分页方式
//只能有一个排序字段,且排序字段的值没有重复的。
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
myPage.SqlTableNames = "Products"; //要显示数据的表名或者视图名
myPage.SqlColumns = "*"; //要显示字段
myPage.SqlOrderColumn = "ProductID"; //排序字段
myPage.SqlOrderColumnKind = "int"; //排序字段的类型。可选项:"int"、"string"、"datetime"、"float"
myPage.SqlPageSize = 5; //一页显示的记录数
myPage.IsOrderDesc = true; //倒序显示记录
//查询条件
myPage.SqlQuery = "";
//查询条件,回发后再次执行 myPage.CreateQuery() 的时候,会把 SetQuery 添加到 SqlQuery 里。
myPage.SetQuery = "";
myPage.CreateQuery(); //生成查询语句 回发后生成的查询语句可以保存。
myPage.BindFirstPage(); //显示第一页的数据
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
第二种分页算法的属性设置。多排序字段,或者是单排序字段且排序字段有重复记录(其实是转换成了多排序字段的情况)。
private void SetPage2()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//多排序字段的分页方式
//支持多字段排序。
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
myPage.SetSQLKind = "2"; //设置分页算法
myPage.SqlTableNames = "Products"; //要显示数据的表名或者视图名
myPage.SqlColumns = "*"; //要显示字段
myPage.SqlPowerIDColumn = "ProductID"; //主键字段名称
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//一个排序字段,且有重复值的情况,不能把主键字段放在下面的两个属性里面
myPage.SqlPowerOrderColumnA = "UnitPrice ,ReorderLevel desc"; //排序字段 按开始日期正序
myPage.SqlPowerOrderColumnB = "UnitPrice desc,ReorderLevel "; //这里要设置为上面的字段的相反的排序方式。
//多个排序字段的情况
myPage.SqlPowerOrderColumnA = "UnitPrice"; //排序字段 按开始日期正序
myPage.SqlPowerOrderColumnB = "UnitPrice desc"; //这里要设置为上面的字段的相反的排序方式。
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
myPage.SqlPowerHasMoreValue = true; //最后一个排序字段是否有重复值
myPage.SqlPageSize = 5; //一页显示的记录数
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
myPage.SqlQuery = ""; //查询条件,回发后该属性失效
myPage.SetQuery = ""; //查询条件,回发后该属性可以保存
myPage.CreateQuery(); //生成查询语句 回发后生成的查询语句可以保存。
myPage.BindFirstPage(); //显示第一页的数据
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
查询情况,点击查询按钮后需要做的事情。这里只是作了一个演示,可以增加更多的查询条件
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
实现查询功能#region 实现查询功能
private void Btn_Search_Click(object sender, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//Response.Write(myPage.SqlQuery); 输出查询条件
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//查询数据
string query = ""; //保存查询条件 where 后面的sql语句
string tmp = ""; //保存查询关键字
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//第一个查询条件
tmp = this.Txt_ProductName.TextTrimNone ;
if (tmp.Length > 0)
query = "ProductName like '%" + tmp + "%'";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
//其他的查询条件
tmp = Txt_UnitPrice1.TextTrimNone ;
string tmp2 = Txt_UnitPrice2.TextTrimNone ;
if (tmp.Length > 0)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
判断是否是数字#region 判断是否是数字
if (!Functions.IsInt(tmp))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
Functions.PageRegisterAlert(Page,("请输入数字"));
return;
}
if (!Functions.IsInt(tmp2))
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
tmp2 = tmp;
}
#endregion
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (query.Length ==0)
query = "UnitPrice between " + tmp + " and " + tmp2;
else
query += " and UnitPrice between " + tmp + " and " + tmp2;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
myPage.SqlQuery = query; //查询条件,回发后该属性失效
myPage.CreateQuery(); //生成查询语句 回发后生成的查询语句可以保存。
myPage.BindFirstPage(); //显示第一页的数据
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
#endregion
还有两个事件,一般情况下不用处理,这里只是记录使用的时间。
private void myPage_DataBindBefore(object s, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//获取记录前的事件
dt1 = DateTime.Now;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
private void myPage_DataBindAfter(object s, System.EventArgs e)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
//绑定控件后的事件
DateTime dt2 = DateTime.Now;
TimeSpan ts = dt2 - dt1;
Response.Write(ts.Minutes + "分");
Response.Write(ts.Seconds + "秒");
Response.Write(ts.Milliseconds + "毫秒");
}
六、分页控件源代码和演示代码下载
http://www.cnblogs.com/jyk/archive/2008/04/25/1170979.html
需要修改 web.config 里面的连接字符串。
<add key="ConnStr" value="data source=.\tt;initial catalog=NorthWind;persist security info=False;user id=sa;pwd=admin;" />
七、核心代码
因为是分页控件,所以呢, 核心代码就是如何分页,也就是分页的算法,使用哪个SQL语句既可以达到很高的效率,又可以满足排序、查询的需求。
这里针对sql Server 2000 进行了优化,采用两种分页算法。
第一种算法针对的是一个排序字段,且排序字段没有重复值的情况。
第二种算法针对的是多排序字段的情况。
第一种算法的SQL语句
declare @col int
set @col =1
select top {PageSize * (PageIndex - 1) + 1} @col = [排序字段] from TableName
select top PageSize * from TableName where [排序字段] >= @col
我知道排序字段不一定都是 int类型的,所以在 第一种算法的时候需要设置一个属性
myPage.SqlOrderColumnKind = "int"; 通过这个属性来修改上面的SQL语句。
第二种算法的SQL语句
对于这种算法你可能会说,在显示最后一页的时候有问题,这个我也发现了,并且在分页控件里面对最后一页作了修改,已经修证了这个bug。
select [*] from [Table] where [ID] in (
select top 10 [ID] from
(
select top 20 [ID] ,AddedDate from [Table]
order by [AddedDate] desc,[ID]
) as aa order by [AddedDate] ,[ID] desc
)order by [AddedDate] desc,[ID]
八、海量数据测试结果
cpu:xp3000+ 单核
内存:DDR2 1G
硬盘:串口
测试用数据库:SQL Server2000 里的 Northwind 数据库里的 Products 表,就是自带的那个。
显示数据的控件:DataGrid 自动填充字段的方式。
记录数:2523136条。
一页显示5条记录。
//分页算法1 单字段排序,且排序字段是聚集索引。
//1000 页以内 15毫秒
//10000页以内 30毫秒
//50000页以内 100多毫秒
//100000页以内 200多毫秒
//最后几页 第一次跳转到 4秒多
//最后几页 连续向前翻页 1秒156毫秒
//页号大范围跳转的时候需要的时间比较长,但是也小于1秒,同时SQL Server 占用的内存有所增加 120M。最后几页时达到320M
===================================================================
以下是多排序字段的分页情况,排序字段是 UnitPrice,ProductID
//分页算法2 无索引 首页 8秒187毫秒 。
//10 页以内 2秒812毫秒
//速度太慢下面的就不测试了
//分页2 非聚集索引 UnitPrice 首页 468毫秒
//10 页以内 2秒671毫秒
//速度太慢下面的就不测试了
//分页算法2 非聚集索引 UnitPrice,ProductID 首页 500毫秒
//10 页以内 2秒796毫秒
//100页以内 4秒796毫秒
//速度太慢下面的就不测试了
//分页算法2 非聚集索引 UnitPrice,ProductID desc 首页 500毫秒
//10 页以内 0-15毫秒
//100页以内 15-46毫秒
//1000页以内 31-62毫秒
//10000页以内 100毫秒左右
//50000页以内 400-500毫秒
//100000页以内 900毫秒左右
//最后几页 第一次跳转到 4秒421毫秒
//最后几页 连续向前翻页 4秒375毫秒
//页号大范围跳转的时候需要的时间比较长,但是也小于1秒,
//这回SQL Server 占用的内存增加幅度不大 120M左右
可见设置好索引对于海量数据的分页的重要性