ASP.NET和MSSQL高性能分页实例代码

首先是存储过程,只取出我需要的那段数据,如果页数超过数据总数,自动返回最后一页的纪录:

set ansi_nulls on
set quoted_identifier on
go
-- =============================================
-- author: clear
-- description: 高性能分页
-- http://www.cnblogs.com/roucheng/
-- =============================================
alter procedure [dbo].[tag_page_name_select]
-- 传入最大显示纪录数和当前页码
@maxpagesize int,
@pagenum int,
-- 设置一个输出参数返回总纪录数供分页列表使用
@count int output
as
begin
set nocount on;
declare
-- 定义排序名称参数
@name nvarchar(50),
-- 定义游标位置
@cursor int
-- 首先得到纪录总数
select @count = count(tag_name)
from [viewdatabase0716].[dbo].[view_tag];
-- 定义游标需要开始的位置
set @cursor = @maxpagesize*(@pagenum-1)+1
-- 如果游标大于纪录总数将游标放到最后一页开始的位置
if @cursor > @count
begin
-- 如果最后一页与最大每次纪录数相等，返回最后整页
if @count % @maxpagesize = 0
begin
if @cursor > @maxpagesize
set @cursor = @count - @maxpagesize + 1
else
set @cursor = 1
end
-- 否则返回最后一页剩下的纪录
else
set @cursor = @count - (@count % @maxpagesize) + 1
end
-- 将指针指到该页开始
set rowcount @cursor
-- 得到纪录开始的位置
select @name = tag_name
from [viewdatabase0716].[dbo].[view_tag]
order by tag_name;
-- 设置开始位置
set rowcount @maxpagesize
-- 得到该页纪录
select * 
from [viewdatabase0716].[dbo].[view_tag]
where tag_name >= @name
order by tag_name
set rowcount 0
end 

然后是分页控件(... 为省略的生成html代码方法):

using system.data;
using system.configuration;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.web.ui.htmlcontrols;
using system.text;
/// <summary>
/// 扩展连接字符串
/// </summary>
public class exstringbuilder
{
private stringbuilder insertstring;
private stringbuilder pagestring;
private int privatepagenum = 1;
private int privatemaxpagesize = 25;
private int privatemaxpages = 10;
private int privatecount;
private int privateallpage;
public exstringbuilder()
{
insertstring = new stringbuilder("");
}
/// <summary>
/// 得到生成的html
/// </summary>
public string gethtml
{
get
{
return insertstring.tostring();
}
}
/// <summary>
/// 得到生成的分页html
/// </summary>
public string getpagehtml
{
get
{
return pagestring.tostring();
}
}
/// <summary>
/// 设置或获取目前页数
/// </summary>
public int pagenum
{
get
{
return privatepagenum;
}
set
{
if (value >= 1)
{
privatepagenum = value;
}
}
}
/// <summary>
/// 设置或获取最大分页数
/// </summary>
public int maxpagesize
{
get
{
return privatemaxpagesize;
}
set
{
if (value >= 1)
{
privatemaxpagesize = value;
}
}
}
/// <summary>
/// 设置或获取每次显示最大页数
/// </summary>
public int maxpages
{
get
{
return privatemaxpages;
}
set
{
privatemaxpages = value;
}
}
/// <summary>
/// 设置或获取数据总数
/// </summary>
public int datecount
{
get
{
return privatecount;
}
set
{
privatecount = value;
}
}
/// <summary>
/// 获取数据总页数
/// </summary>
public int allpage
{
get
{
return privateallpage;
}
}
/// <summary>
/// 初始化分页
/// </summary>
public void pagination()
{
pagestring = new stringbuilder("");
//得到总页数
privateallpage = (int)math.ceiling((decimal)privatecount / (decimal)privatemaxpagesize);
//防止上标或下标越界
if (privatepagenum > privateallpage)
{
privatepagenum = privateallpage;
}
//滚动游标分页方式
int leftrange, rightrange, leftstart, rightend;
leftrange = (privatemaxpages + 1) / 2-1;
rightrange = (privatemaxpages + 1) / 2;
if (privatemaxpages >= privateallpage)
{
leftstart = 1;
rightend = privateallpage;
}
else
{
if (privatepagenum <= leftrange)
{
leftstart = 1;
rightend = leftstart + privatemaxpages - 1;
}
else if (privateallpage - privatepagenum < rightrange)
{
rightend = privateallpage;
leftstart = rightend - privatemaxpages + 1;
}
else
{
leftstart = privatepagenum - leftrange;
rightend = privatepagenum + rightrange;
}
}
//生成页码列表统计
pagestring.append(...);
stringbuilder previousstring = new stringbuilder("");
//如果在第一页
if (privatepagenum > 1)
{
...
}
else
{
...
}
//如果在第一组分页
if (privatepagenum > privatemaxpages)
{
...
}
else
{
...
}
pagestring.append(previousstring);
//生成中间页 http://www.cnblogs.com/roucheng/
for (int i = leftstart; i <= rightend; i++)
{
//为当前页时
if (i == privatepagenum)
{
...
}
else
{
...
}
}
stringbuilder laststring = new stringbuilder("");
//如果在最后一页
if (privatepagenum < privateallpage)
{
...
}
else
{
...
}
//如果在最后一组
if ((privatepagenum + privatemaxpages) < privateallpage)
{
...
}
else
{
...
}
pagestring.append(laststring);
}
/// <summary>
/// 生成tag分类表格
/// </summary>
public void tagtable(exdatarow myexdatarow)
{
insertstring.append(...);
} 

调用方法:

//得到分页设置并放入session
exrequest myexrequest = new exrequest();
myexrequest.pagesession("tag_", new string[] { "page", "size" });
//生成tag分页
exstringbuilder tag = new exstringbuilder();
//设置每次显示多少条纪录
tag.maxpagesize = convert.toint32(session["tag_size"]);
//设置最多显示多少页码
tag.maxpages = 9;
//设置当前为第几页
tag.pagenum = convert.toint32(session["tag_page"]);
string[][] mynamenvalue = new string[2][]{
new string[]{"maxpagesize","pagenum","count"},
new string[]{tag.maxpagesize.tostring(),tag.pagenum.tostring()}
};
//调用存储过程
datatable mydatatable = mysql.batchgetdb("tag_page_name_select", mynamenvalue, "count");
tag.datecount = (int)mysql.outputcommand.parameters["@count"].value;
tag.pagination();
headpage.innerhtml = footpage.innerhtml = tag.getpagehtml;
for (int i = 0, j = mydatatable.rows.count; i < j; i++)
{
tag.tagtable(new exdatarow(mydatatable.rows));
}
tagbox.innerhtml = tag.gethtml;