千万级数据分页详细设计
为适应大数据量分页的需要，为以后千万级数据分页提供解决方法或者参考，节省开发时间，特制定本详细设计方案
脚本设计人员
2.1.1简介
2.1.2分页存储过程代码
以下代码是网上找的分页存储过程，我是在原存储过程的基础上加了一个@IsCount bit = 0, 主要是用来返回纪录总数，当为非0值时返回。下面注释部分是原作者的测试部分。我在本机sql server 2005上的测试是在10000011纪录中查询第100000页，每页10条纪录按升序和降序时间均为0.38秒，测试语法如下：exec GetRecordFromPage tbl_Briefness,I_BriefnessID,10,100000，其中在tbl_Briefness表I_BriefnessID字段上建立了索引。
/**//*
经测试，在14483461 条记录中查询第100000 页，每页10 条记录按升序和降序第一次时间均为0.47 秒，第二次时间均为0.43 秒，测试语法如下：
exec GetRecordFromPage news,newsid,10,100000
news 为表名, newsid 为关键字段, 使用时请先对newsid 建立索引。
函数名称: GetRecordFromPage
函数功能: 获取指定页的数据
参数说明: @tblName 包含数据的表名
@fldName 关键字段名
@PageSize 每页记录数
@PageIndex 要获取的页码
@OrderType 排序类型, 0 - 升序, 1 - 降序
@strWhere 查询条件(注意: 不要加where)
创建时间: 2004-07-04
修改时间: 2008-02-13
*/
ALTER PROCEDURE [dbo].[GetRecordFromPage]
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@OrderType bit = 0, -- 设置排序类型, 非0 值则降序
@IsCount bit = 0, -- 返回记录总数, 非0 值则返回
@strWhere varchar(2000) = '' -- 查询条件(注意: 不要加where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(1000) -- 临时变量
declare @strOrder varchar(500) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName + '] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp = ''
if @strWhere != ''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
if @IsCount != 0
set @strSQL = 'select count(' + @fldName + ') as Total from [' + @tblName + ']'
exec (@strSQL)
2.2.1分页控件的详细代码
using System;
using System.Collections.Generic;
using System.Text;
using System.ComponentModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace CustomControls
...{
[ToolboxData("<{0}:AspNetPager runat='server' PageSize='25' FirstPageText='首页' PrePageText='上一页' NextPageText='下一页' EndPageText='末页' ButtonText='GO'></{0}:AspNetPager>")]
public class AspNetPager : WebControl, INamingContainer
...{
属性块#region 属性块
private object baseState = null;
private object buttonStyleState = null;
private object textBoxStyleState = null;
private object labelStyleState = null;
private object linkButtonStyleState = null;
private LinkButton _lnkbtnFrist;
private LinkButton _lnkbtnPre;
private LinkButton _lnkbtnNext;
private LinkButton _lnkbtnLast;
private Label _lblCurrentPage;
private Label _lblRecodeCount;
private Label _lblPageCount;
private Label _lblPageSize;
private TextBox _txtPageIndex;
private Button _btnChangePage;
private static readonly object EventPageChange = new object();
[Category("Pagination"), Description("每页显示的纪录数"),
DefaultValue("25")]
public virtual int PageSize
...{
get
...{
EnsureChildControls();
return _lblPageSize.Text.Trim() != "" ? int.Parse(_lblPageSize.Text.Trim()) : 25;
}
set
...{
EnsureChildControls();
_lblPageSize.Text = value.ToString();
}
}
[Category("Pagination"), Description("总纪录数"),
DefaultValue("0"), Bindable(true)]
public virtual int RecordCount
...{
get
...{
EnsureChildControls();
return _lblRecodeCount.Text.Trim() != "" ? int.Parse(_lblRecodeCount.Text.Trim()) : 0;
}
set
...{
EnsureChildControls();
if (value > 0)
...{
int recodeCount = value;
_lblPageCount.Text = (value % PageSize == 0 ? value / PageSize : value / PageSize + 1).ToString();//计算总页数
}
_lblRecodeCount.Text = value.ToString();
}
}
[Category("Pagination"), Description("当前页码"),
DefaultValue("1"), Bindable(true)]
public virtual int PageIndex
...{
get
...{
EnsureChildControls();
return _lblCurrentPage.Text.Trim() != "" ? int.Parse(_lblCurrentPage.Text.Trim()) : 1;
}
set
...{
EnsureChildControls();
_lblCurrentPage.Text = value.ToString();
}
}
[Category("Appearance"), Description("设置第一页的文本"),
DefaultValue("首页"), Bindable(true)]
public virtual string FirstPageText
...{
get
...{
EnsureChildControls();
return _lnkbtnFrist.Text.Trim() != "" ? _lnkbtnFrist.Text.Trim() : "首页";
}
set
...{
EnsureChildControls();
_lnkbtnFrist.Text = value;
}
}
[Category("Appearance"), Description("设置上一页的文本"),
DefaultValue("上一页"), Bindable(true)]
public virtual string PrePageText
...{
get
...{
EnsureChildControls();
return _lnkbtnPre.Text.Trim() != "" ? _lnkbtnPre.Text.Trim() : "上一页";
}
set
...{
EnsureChildControls();
_lnkbtnPre.Text = value;
}
}
[Category("Appearance"), Description("设置下一页的文本"),
DefaultValue("下一页"), Bindable(true)]
public virtual string NextPageText
...{
get
...{
EnsureChildControls();
return _lnkbtnNext.Text.Trim() != "" ? _lnkbtnNext.Text.Trim() : "下一页";
}
set
...{
EnsureChildControls();
_lnkbtnNext.Text = value;
}
}
[Category("Appearance"), Description("设置末页的文本"),
DefaultValue("末页"), Bindable(true)]
public virtual string EndPageText
...{
get
...{
EnsureChildControls();
return _lnkbtnLast.Text.Trim() != "" ? _lnkbtnLast.Text.Trim() : "末页";
}
set
...{
EnsureChildControls();
_lnkbtnLast.Text = value;
}
}
[Category("Appearance"), Description("设置跳转按钮的文本"),
DefaultValue(":"), Bindable(true)]
public virtual string ButtonText
...{
get
...{
EnsureChildControls();
return _btnChangePage.Text.Trim() != "" ? _btnChangePage.Text.Trim() : "GO";
}
set