欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2024-01-16 21:54:10
首先是存储过程,只取出我需要的那段数据,如果页数超过数据总数,自动返回最后一页的纪录: set ansi_nulls on set quoted_identif...

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

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;