--/*-----存储过程 分页处理 孙伟 2005-03-28创建 -------*/
--/*----- 对数据进行了2分处理使查询前半部分数据与查询后半部分数据性能相同 -------*/
--/*-----存储过程 分页处理 孙伟 2005-04-21修改 添加distinct查询功能-------*/
--/*-----存储过程 分页处理 孙伟 2005-05-18修改 多字段排序规则问题-------*/
--/*-----存储过程 分页处理 孙伟 2005-06-15修改 多字段排序修改-------*/
--/*-----存储过程 分页处理 孙伟 2005-12-13修改 修改数据分页方式为top max模式 性能有极大提高-------*/
--/*-----缺点：相对之前的not in版本主键只能是整型字段,如主键为guid类型请使用not in 模式的版本-------*/
create procedure dbo.proc_listpageint
(
@tblname nvarchar(200), ----要显示的表或多个表的连接
@fldname nvarchar(500) = '*', ----要显示的字段列表
@pagesize int = 10, ----每页显示的记录个数
@page int = 1, ----要显示那一页的记录
@pagecount int = 1 output, ----查询结果分页后的总页数
@counts int = 1 output, ----查询到的记录数
@fldsort nvarchar(200) = null, ----排序字段列表或条件
@sort bit = 0, ----排序方法，0为升序，1为降序(如果是多字段排列sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' sorta asc,sortb desc,sortc ')
@strcondition nvarchar(1000) = null, ----查询条件,不需where
@id nvarchar(150), ----主表的主键
@dist bit = 0 ----是否添加查询字段的 distinct 默认0不添加/1添加
)
as
set nocount on
declare @sqltmp nvarchar(1000) ----存放动态生成的sql语句
declare @strtmp nvarchar(1000) ----存放取得查询结果总数的查询语句
declare @strid nvarchar(1000) ----存放取得查询开头或结尾id的查询语句
declare @strsorttype nvarchar(10) ----数据排序规则a
declare @strfsorttype nvarchar(10) ----数据排序规则b
declare @sqlselect nvarchar(50) ----对含有distinct的查询进行sql构造
declare @sqlcounts nvarchar(50) ----对含有distinct的总数查询进行sql构造
if @dist = 0
begin
set @sqlselect = 'select '
set @sqlcounts = 'count(*)'
end
else
begin
set @sqlselect = 'select distinct '
set @sqlcounts = 'count(distinct ' @id ')'
end
if @sort=0
begin
set @strfsorttype=' asc '
set @strsorttype=' desc '
end
else
begin
set @strfsorttype=' desc '
set @strsorttype=' asc '
end
--------生成查询语句--------
--此处@strtmp为取得查询结果数量的语句
if @strcondition is null or @strcondition='' --没有设置显示条件
begin
set @sqltmp = @fldname ' from ' @tblname
set @strtmp = @sqlselect ' @counts=' @sqlcounts ' from ' @tblname
set @strid = ' from ' @tblname
end
else
begin
set @sqltmp = @fldname 'from ' @tblname ' where (1>0) ' @strcondition
set @strtmp = @sqlselect ' @counts=' @sqlcounts ' from ' @tblname ' where (1>0) ' @strcondition
set @strid = ' from ' @tblname ' where (1>0) ' @strcondition
end
----取得查询结果总数量-----
exec sp_executesql @strtmp,n'@counts int out ',@counts out
declare @tmpcounts int
if @counts = 0
set @tmpcounts = 1
else
set @tmpcounts = @counts
--取得分页总数
set @pagecount=(@tmpcounts @pagesize-1)/@pagesize
/**//**当前页大于总页数 取最后一页**/
if @page>@pagecount
set @page=@pagecount
--/*-----数据分页2分处理-------*/
declare @pageindex int --总数/页大小
declare @lastcount int --总数%页大小
set @pageindex = @tmpcounts/@pagesize
set @lastcount = @tmpcounts%@pagesize
if @lastcount > 0
set @pageindex = @pageindex 1
else
set @lastcount = @pagesize
--//***显示分页
if @strcondition is null or @strcondition='' --没有设置显示条件
begin
if @pageindex begin
if @page=1
set @strtmp=@sqlselect ' top ' cast(@pagesize as varchar(4)) ' ' @fldname ' from ' @tblname
' order by ' @fldsort ' ' @strfsorttype
else
begin
set @strtmp=@sqlselect ' top ' cast(@pagesize as varchar(4)) ' ' @fldname ' from ' @tblname
' where ' @id ' ' order by ' @fldsort ' ' @strfsorttype ') as tbminid)'
' order by ' @fldsort ' ' @strfsorttype
end
end
else
begin
set @page = @pageindex-@page 1 --后半部分数据处理
if @page set @strtmp=@sqlselect ' * from (' @sqlselect ' top ' cast(@lastcount as varchar(4)) ' ' @fldname ' from ' @tblname
' order by ' @fldsort ' ' @strsorttype ') as temptb' ' order by ' @fldsort ' ' @strfsorttype
else
set @strtmp=@sqlselect ' * from (' @sqlselect ' top ' cast(@pagesize as varchar(4)) ' ' @fldname ' from ' @tblname
' where ' @id ' >(select max(' @id ') from(' @sqlselect ' top ' cast(@pagesize*(@page-2) @lastcount as varchar(20)) ' ' @id ' from ' @tblname
' order by ' @fldsort ' ' @strsorttype ') as tbmaxid)'
' order by ' @fldsort ' ' @strsorttype ') as temptb' ' order by ' @fldsort ' ' @strfsorttype
end
end
else --有查询条件
begin
if @pageindex begin
if @page=1
set @strtmp=@sqlselect ' top ' cast(@pagesize as varchar(4)) ' ' @fldname ' from ' @tblname
' where 1=1 ' @strcondition ' order by ' @fldsort ' ' @strfsorttype
else
begin
set @strtmp=@sqlselect ' top ' cast(@pagesize as varchar(4)) ' ' @fldname ' from ' @tblname
' where ' @id ' ' where (1=1) ' @strcondition ' order by ' @fldsort ' ' @strfsorttype ') as tbminid)'
' ' @strcondition ' order by ' @fldsort ' ' @strfsorttype
end
end
else
begin
set @page = @pageindex-@page 1 --后半部分数据处理
if @page set @strtmp=@sqlselect ' * from (' @sqlselect ' top ' cast(@lastcount as varchar(4)) ' ' @fldname ' from ' @tblname
' where (1=1) ' @strcondition ' order by ' @fldsort ' ' @strsorttype ') as temptb' ' order by ' @fldsort ' ' @strfsorttype
else
set @strtmp=@sqlselect ' * from (' @sqlselect ' top ' cast(@pagesize as varchar(4)) ' ' @fldname ' from ' @tblname
' where ' @id ' >(select max(' @id ') from(' @sqlselect ' top ' cast(@pagesize*(@page-2) @lastcount as varchar(20)) ' ' @id ' from ' @tblname
' where (1=1) ' @strcondition ' order by ' @fldsort ' ' @strsorttype ') as tbmaxid)'
' ' @strcondition ' order by ' @fldsort ' ' @strsorttype ') as temptb' ' order by ' @fldsort ' ' @strfsorttype
end
end
------返回查询结果-----
exec sp_executesql @strtmp
--print @strtmp
set nocount off
go
调用方法列子：
/**////
/// 通用分页数据读取函数
/// 注意：在函数调用外部打开和关闭连接，以及关闭数据读取器
///
/// sqlcommand对象
/// 查询的表/表联合
/// 要查询的字段名
/// 每页数据大小
/// 当前第几页
/// 排序字段
/// 排序顺序0降序1升序
/// 过滤条件
/// 主表主键
/// 返回的sqldatareader ref
public static void cutpagedata(sqlconnection conn, ref sqlcommand comm, string _tblname, string _fldname, int _pagesize, int _page, string _fldsort, int _sort, string _strcondition, string _id, ref sqldatareader _dr)
{
//注意：在函数调用外部打开和关闭连接，以及关闭数据读取器
//comm = new sqlcommand(proc_listpage,conn);
//comm.commandtype = commandtype.storedprocedure;
comm.parameters.add(@tblname, sqldbtype.nvarchar, 200);
comm.parameters[@tblname].value = _tblname;
comm.parameters.add(@fldname, sqldbtype.nvarchar, 500);
comm.parameters[@fldname].value = _fldname;
comm.parameters.add(@pagesize, sqldbtype.int);
comm.parameters[@pagesize].value = _pagesize;
comm.parameters.add(@page, sqldbtype.int);
comm.parameters[@page].value = _page;
comm.parameters.add(@fldsort, sqldbtype.nvarchar, 200);
comm.parameters[@fldsort].value = _fldsort;
comm.parameters.add(@sort, sqldbtype.bit);
comm.parameters[@sort].value = _sort;
comm.parameters.add(@strcondition, sqldbtype.nvarchar, 1000);
comm.parameters[@strcondition].value = _strcondition;
comm.parameters.add(@id, sqldbtype.nvarchar, 150);
comm.parameters[@id].value = _id;
comm.parameters.add(@counts, sqldbtype.int, 0);
comm.parameters[@counts].direction = parameterdirection.output;
comm.parameters.add(@pagecount, sqldbtype.int, 0);
comm.parameters[@pagecount].direction = parameterdirection.output;
_dr = comm.executereader();
}
调用例如：
cutpagedata(conn, ref comm, vox_cdsinger, id, cdsinger, cdsingertype, area, cdsingerreadme, 15, page, id, 1, strfilter, id, ref dr);
对应说明：
cutpagedata(数据连接对象, ref sqlcommand对象, 需要表或视图名称, 要查询的字段, 每页读取数据条数, 当前页, 排序字段可多字段如(addtime desc, visitcounts注意这里最后一个字段不加desc或asc 最后一个字段对应于后面的排序规则), 排序方式(1 desc 0 asc), where条件（这里不再添加where条件添加如：' and visitcounts>100'）, 表主键, ref 返回的sqldatareader对象);
这里的调用同样适用于之前的not in版本.
呵呵 这里献给大家 圣诞快乐 ！