通用分页存储过程

/*
功能： 通用分页存储过程
参数：
@PK varchar(50), 
主键，用来排序的单一字段，空的话，表示没有主键，存储过程将自动创建标识列主键
@Fields varchar(500), 要显示的字段列表(格式如：ID,Code,Name)
@Tables varchar(1000), 要使用的表集合(Org)
@Where varchar(500), 查询条件(Code like '100')
@OrderBy varchar(100), 排序条件(支持多个排序字段，如：ID,Code desc,Name desc)
@PageIndex int, 当前要显示的页的页索引，索引从1开始，无记录时为0。
@PageSize int, 页大小
创建者：Hollis Yao
创建日期：2006-08-06
备注：
*/
CREATE PROCEDURE [dbo].[listpage]
@PK varchar(50)='',
@Fields varchar(500),
@Tables varchar(1000),
@Where varchar(500)='',
@OrderBy varchar(100),
@PageIndex int,
@PageSize int
AS
--替换单引号，避免构造SQL出错
set @Fields = replace(@Fields, '''', '''''')
--要执行的SQL，切分为几个字符串，避免出现长度超过4k时的问题
declare @SQL1 varchar(4000)
declare @SQL2 varchar(4000) 
if @PageIndex=0
set @PageIndex = 1
set @SQL1 = ''
set @SQL2 = ''
if @Where is not null and len(ltrim(rtrim(@Where))) > 0
set @Where = ' where ' + @Where
else
set @Where = ' where 1=1'
set @SQL1 = @SQL1 + ' declare @TotalCount int' --声明一个变量，总记录数
set @SQL1 = @SQL1 + ' declare @PageCount int' --声明一个变量，总页数
set @SQL1 = @SQL1 + ' declare @PageIndex int' --声明一个变量，页索引
set @SQL1 = @SQL1 + ' declare @StartRow int' --声明一个变量，当前页第一条记录的索引
set @SQL1 = @SQL1 + ' select @TotalCount=count(*) from ' + @Tables + @Where --获取总记录数
set @SQL1 = @SQL1 + ' if @PageCount <= 0 begin' --如果记录数为0，直接输出空的结果集
set @SQL1 = @SQL1 + ' select ' + @Fields + ' from ' + @Tables + ' where 1<>1'
set @SQL1 = @SQL1 + ' select 0 as PageIndex,0 as PageCount,' 
+ convert(varchar, @PageSize) + ' as PageSize,0 as TotalCount'
set @SQL1 = @SQL1 + ' return end'
set @SQL1 = @SQL1 + ' set @PageCount=(@TotalCount+' + convert(varchar, @PageSize) 
+ '-1)/' + convert(varchar, @PageSize) --获取总页数
set @SQL1 = @SQL1 + ' set @PageIndex=' + convert(varchar, @PageIndex) 
--设置正确的页索引
set @SQL1 = @SQL1 + ' if @PageIndex<0 set @PageIndex=1'
set @SQL1 = @SQL1 + ' if @PageIndex>@PageCount and @PageCount>0 
set @PageIndex=@PageCount'
set @SQL1 = @SQL1 + ' set @StartRow=(@PageIndex-1)*' + convert(varchar, @PageSize)
+ '+1'
if (charindex(',', @OrderBy)=0 and charindex(@PK, @OrderBy)>0)
begin
--****************************************************************************
--****************不需要创建主键********************************************
--****************************************************************************
declare @SortDirection varchar(10) --排序方向，>=：升序，<=：倒序
set @SortDirection = '>='
if charindex('desc', @OrderBy) > 0
set @SortDirection = '<='
set @SQL2 = @SQL2 + ' declare @Sort varchar(100)' 
--声明一个变量，用来记录当前页第一条记录的排序字段值
set @SQL2 = @SQL2 + ' set rowcount @StartRow' 
--设置返回记录数截止到当前页的第一条
set @SQL2 = @SQL2 + ' select @Sort=' + @PK + ' from ' 
+ @Tables + @Where + ' order by ' + @OrderBy --获取当前页第一个排序字段值
set @SQL2 = @SQL2 + ' set rowcount ' + convert(varchar, @PageSize) 
--设置返回记录数为页大小
set @Where = @Where + ' and ' + @PK + @SortDirection + '@Sort'
set @SQL2 = @SQL2 + ' select ' + @Fields + ' from ' + @Tables 
+ @Where + ' order by ' + @OrderBy 
--输出最终显示结果
end
else
begin
--需要创建自增长主键
set @SQL2 = @SQL2 + ' declare @EndRow int'
set @SQL2 = @SQL2 + ' set @EndRow=@PageIndex*' + convert(varchar, @PageSize)
set @SQL2 = @SQL2 + ' set rowcount @EndRow'
set @SQL2 = @SQL2 + ' declare @PKBegin int' --声明一个变量，开始索引
set @SQL2 = @SQL2 + ' declare @PKEnd int' --声明一个变量，结束索引
set @SQL2 = @SQL2 + ' set @PKBegin=@StartRow'
set @SQL2 = @SQL2 + ' set @PKEnd=@EndRow'
--****************************************************************************
--************对特殊字段进行转换，以便可以插入到临时表******************
--****************************************************************************
declare @TempFields varchar(500)
set @TempFields=@Fields
set @TempFields = replace(@TempFields, ''''' as CheckBox', '')
set @TempFields = replace(@TempFields, ''''' as DetailButton', '')
set @TempFields = replace(@TempFields, ''''' as Radio', '')
set @TempFields = LTRIM(RTRIM(@TempFields))
if left(@TempFields,1)=',' --去除最左边的逗号
set @TempFields = substring(@TempFields, 2, len(@TempFields))
if right(@TempFields,1)=',' --去除最右边的逗号
set @TempFields = substring(@TempFields, 1, len(@TempFields)-1)
set @SQL2 = @SQL2 + ' select identity(int,1,1) as PK,' + @TempFields 
+ ' into #tb from ' + @Tables + @Where + ' order by ' + @OrderBy
--****************************************************************************
--********去除字段的表名前缀，当有字段有别名时，只保留字段别名*********
--****************************************************************************
declare @TotalFields varchar(500)
declare @tmp varchar(50)
declare @i int
declare @j int
declare @iLeft int --左括号的个数
declare @iRight int --右括号的个数
set @i = 0
set @j = 0
set @iLeft = 0
set @iRight = 0
set @tmp = ''
set @TotalFields = '' 
while (len(@Fields)>0)
begin
set @i = charindex(',', @Fields)
--去除字段的表名前缀 本篇文章发表于www.xker.com(小新技术网) 
if (@i=0)
begin
--找不到逗号分割，即表示只剩下最后一个字段
set @tmp = @Fields
end
else
begin
set @tmp = substring(@Fields, 1, @i)
end
set @j = charindex('.', @tmp)
if (@j>0)
set @tmp = substring(@tmp, @j+1, len(@tmp))
--*******当有字段有别名时，只保留字段别名********* 
--带括号的情况要单独处理，如Convert(varchar(10), B.EndDate, 120) as EndDate
while (charindex('(', @tmp) > 0)
begin
set @iLeft = @iLeft + 1
set @tmp = substring(@tmp, charindex('(', @tmp)+1, Len(@tmp))
end
while (charindex(')', @tmp) > 0)
begin
set @iRight = @iRight + 1
set @tmp = substring(@tmp, charindex(')', @tmp)+1, Len(@tmp))
end
--当括号恰好组队的时候，才能进行字段别名的处理
if (@iLeft = @iRight)
begin
set @iLeft = 0
set @iRight = 0
--不对这几个特殊字段作处理:CheckBox、DetailButton、Radio
if (charindex('CheckBox', @tmp) = 0 and charindex
('DetailButton', @tmp) = 0 and charindex('Radio', @tmp) = 0)
begin
--判断是否有别名
if (charindex('as', @tmp) > 0)--别名的第一种写法，带'as'的格式
begin
set @tmp = substring(@tmp, charindex('as', @tmp)+2, len(@tmp))
end
else
begin
if (charindex(' ', @tmp) > 0)--别名的第二种写法，带空格(" ")的格式
begin
while(charindex(' ', @tmp) > 0)
begin
set @tmp = substring(@tmp, charindex(' ', @tmp)+1, len(@tmp))
end
end
end
end
set @TotalFields = @TotalFields + @tmp
end
if (@i=0)
set @Fields = ''
else
set @Fields = substring(@Fields, @i+1, len(@Fields))
end
--print @TotalFields
set @SQL2 = @SQL2 + ' select ' + @TotalFields + ' 
from #tb where PK between @PKBegin and @PKEnd order by PK' 
--输出最终显示结果
set @SQL2 = @SQL2 + ' drop table #tb'
end 
--输出“PageIndex(页索引)、PageCount(页数)、PageSize(页大小)、TotalCount(总记录数)”
set @SQL2 = @SQL2 + ' select @PageIndex as PageIndex,@PageCount as PageCount,' 
+ convert(varchar, @PageSize) + ' as PageSize,@TotalCount as TotalCount'
--print @SQL1 + @SQL2
--return
exec(@SQL1 + @SQL2)
GO

最终这个存储过程将生成二张表，表1显示记录集，表2显示当前页码、总页数、每页记录数、总记录数。

使用方法：

SqlCommand comm = new SqlCommand(); 
                comm.CommandText = "listpage"; 
                comm.CommandType = System.Data.CommandType.StoredProcedure; 
                comm.Connection = conn; 
                /* 
                    @PK varchar(50), 
                    主键，用来排序的单一字段，空的话，表示没有主键，存储过程将自动创建标识列主键 
                    @Fields varchar(500), 要显示的字段列表(格式如：ID,Code,Name) 
                    @Tables varchar(1000), 要使用的表集合(Org) 
                    @Where varchar(500), 查询条件(Code like '100') 
                    @OrderBy varchar(100), 排序条件(支持多个排序字段，如：ID,Code desc,Name desc) 
                    @PageIndex int, 当前要显示的页的页索引，索引从1开始，无记录时为0。 
                    @PageSize int, 页大小 
                  
                 */ 
                //增加参数及值 
                comm.Parameters.AddWithValue("@PK", "主键"); 
                comm.Parameters.AddWithValue("@Fields", "字段列表"); 
                comm.Parameters.AddWithValue("@Table", "doc_qa"); 
                comm.Parameters.AddWithValue("@Where", "  条件 "); 
                comm.Parameters.AddWithValue("@OrderBy", " 排序的语句"); 
                comm.Parameters.AddWithValue("@PageIndex", 1); 
                comm.Parameters.AddWithValue("@pagesize", 15); 
                conn.Open(); 
                DataSet dst = new DataSet(); 
                SqlDataAdapter da = new SqlDataAdapter(comm); 
                da.Fill(dst); 
                DataTable DataSource = dst.Tables[0];  //记录集 
                DataTable DataCount = dst.Tables[1]; //页码等信息