ASP.NET技巧：一个通用的分页类

结合一个存储过程，将分页做成最简单，请看以下源码

此分页类所操作的存储过程#region 此分页类所操作的存储过程
/**//*********************************************************
*
* 功能强大，配合以下这个存储过程
*
* *******************************************************/
/**//*
-- Pager 1,10,0,0, 'EmployeeID>2 and EmployeeID<5 ' , 'Employees','*','LastName',0
CREATE PROCEDURE Pager
@PageIndex int,--索引页 1
@PageSize int,--每页数量2
@RecordCount int out,--总行数3
@PageCount int out,--总页数4
@WhereCondition Nvarchar(1000),--查询条件5
@TableName nvarchar(500),--查询表名6
@SelectStr nvarchar(500) = '*',--查询的列7
@Order nvarchar(500),--排序的列8
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 9
@Groupby NVarChar(100) = ''
AS

declare @strSQL nvarchar(2000) -- 主语句
declare @strTmp nvarchar(1000) -- 临时变量
declare @strOrder nvarchar(1000) -- 排序类型

if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by ' @Order ' desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by ' @Order ' asc'
end

set @strSQL = 'select top ' str(@PageSize) ' ' @SelectStr ' from '
@TableName ' where ' @Order '' @strTmp '(['
@Order ']) from (select top ' str((@PageIndex-1)*@PageSize) ' ['
@Order '] from ' @TableName '' @strOrder ') as tblTmp)'
@Groupby @strOrder

if @WhereCondition != ''
set @strSQL = 'select top ' str(@PageSize) ' ' @SelectStr ' from '
@TableName ' where ' @Order '' @strTmp '(['
@Order ']) from (select top ' str((@PageIndex-1)*@PageSize) ' ['
@Order '] from ' @TableName ' where (' @WhereCondition ') '
@strOrder ') as tblTmp) and (' @WhereCondition ') ' @Groupby @strOrder

if @PageIndex = 1
begin
set @strTmp = ''
if @WhereCondition != ''
set @strTmp = ' where (' @WhereCondition ')'

set @strSQL = 'select top ' str(@PageSize) ' ' @SelectStr ' from '
@TableName '' @strTmp ' ' @Groupby @strOrder
end
exec (@strSQL)
--print @strSQL

IF @WhereCondition <>''
Begin
SET @strTmp = 'SELECT -1 FROM ' @TableName ' Where ' (@WhereCondition)
End
ELSE
Begin
SET @strTmp = 'SELECT -1 FROM ' @TableName
End
EXEC SP_EXECUTESQL @strTmp
SET @RecordCount = @@RowCount
-- 获取总页数
-- "CEILING"函数:取得不小于某数的最小整数
共3页: 上一页 1 [2] [3] 下一页

评论加载中…