通用分页存储过程

作者:幻想曲 来源:幻想曲 时间:2007-11-28 13:08:00 


/*
功能: 通用分页存储过程
参数:
@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]; //页码等信息 



标签:分页,存储过程,sql
0
投稿

猜你喜欢

  • Python数据结构之栈、队列的实现代码分享

    2023-12-07 12:39:08
  • Python简单爬虫导出CSV文件的实例讲解

    2022-10-11 03:39:19
  • python使用js2py库运行js代码

    2022-09-06 17:06:55
  • python基础之元组

    2021-06-28 08:06:24
  • python 处理数字,把大于上限的数字置零实现方法

    2022-11-13 09:20:56
  • Mysql的Table doesn't exist问题及解决

    2024-01-16 05:03:13
  • JS异步函数队列功能实例分析

    2024-04-22 13:26:28
  • C#操作SQLite数据库方法小结(创建,连接,插入,查询,删除等)

    2024-01-23 01:06:29
  • JS将数字转换成三位逗号分隔的样式(示例代码)

    2024-05-02 16:26:59
  • Python中的变量赋值

    2023-07-23 06:00:10
  • 原生JavaScript实现的简单省市县三级联动功能示例

    2024-06-05 09:13:24
  • 基于Python实现自动化文档整理工具

    2021-10-01 01:29:19
  • Python虚拟环境virtualenv的安装与使用详解

    2022-02-22 02:19:46
  • 用javascript实现select的美化

    2007-05-11 16:50:00
  • pycharm使用正则表达式批量添加print括号完美从python2迁移到python3

    2021-11-06 15:30:35
  • vscode中配置jupyter的详细步骤(彻底解决Failed to start the Kernel问题)

    2022-06-21 06:56:26
  • 浅谈Python中range和xrange的区别

    2021-04-18 14:52:13
  • Mysql主从复制注意事项的讲解

    2024-01-28 10:57:32
  • 一文学会利用python解决文章付费限制问题

    2021-04-09 08:23:51
  • Python入门篇之字典

    2022-09-24 23:53:47
  • asp之家 网络编程 m.aspxhome.com