SQL：分页存储过程(优化了部分语句,附C#调用接口)（转）

于 2013-11-01 11:54:55 发布
阅读量799 收藏
点赞数
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/hefeng_aspnet/article/details/13998691

网上分页存储过程的代码很多，这个是我一直在用的经典的三层架构里提取出来的，并做了部分语句的优化...

如有更好的欢迎讨论！

set  ANSI_NULLS ON
set  QUOTED_IDENTIFIER ON
GO
------------------------------------
--用途：分页存储过程(对有主键的表效率极高) 
--说明：（优化了部分语句）
------------------------------------
 
ALTER  PROCEDURE  [dbo].[TP_GetRecordByPage]
     @tblName      varchar (255),       -- 表名
     @fldName      varchar (255),       -- 主键字段名
     @PageSize     int  = 10,           -- 页尺寸
     @PageIndex    int  = 1,            -- 页码
     @IsReCount    bit  = 0,            -- 返回记录总数, 非 0 值则返回
     @OrderType    bit  = 0,            -- 设置排序类型, 非 0 值则降序
     @strWhere     varchar (1000) = ''   -- 查询条件 (注意: 不要加 where)
AS
 
declare  @strSQL   varchar (6000)       -- 主语句
declare  @strTmp   varchar (800)        -- 临时变量(查询条件过长时可能会出错，可修改100为1000)
declare  @strOrder varchar (400)        -- 排序类型
 
if @OrderType != 0
begin
     set  @strTmp = '<(select min'
     set  @strOrder = ' order by ['  + @fldName + '] desc'
end
else
begin
     set  @strTmp = '>(select max'
     set  @strOrder = ' order by ['  + @fldName + '] asc'
end
 
set  @strSQL = 'select top '  + str(@PageSize) + ' * from ['
     + @tblName + '] where ['  + @fldName + ']'  + @strTmp + '(['
     + @fldName + ']) from (select top '  + str((@PageIndex-1)*@PageSize) + ' ['
     + @fldName + '] from ['  + @tblName + ']'  + @strOrder + ') as tblTmp)'
     + @strOrder
 
if @strWhere != ''
     set  @strSQL = 'select top '  + str(@PageSize) + ' * from ['
         + @tblName + '] where ['  + @fldName + ']'  + @strTmp + '(['
         + @fldName + ']) from (select top '  + str((@PageIndex-1)*@PageSize) + ' ['
         + @fldName + '] from ['  + @tblName + '] where '  + @strWhere + ' '
         + @strOrder + ') as tblTmp) and '  + @strWhere + ' '  + @strOrder
 
if @PageIndex = 1
begin
     set  @strTmp = ''
     if @strWhere != ''
         set  @strTmp = ' where '  + @strWhere
 
     set  @strSQL = 'select top '  + str(@PageSize) + ' * from ['
         + @tblName + ']'  + @strTmp + ' '  + @strOrder
end
 
if @IsReCount != 0
     set  @strSQL = 'select count(*) as Total from ['  + @tblName + ']' + ' where '  + @strWhere
 
exec  (@strSQL)

以下是C#调用的接口方法

使用时注意一下命名空间改为你的，需要引用DBUtility操作类。

/// <summary>
/// 得到记录总数
/// </summary>
/// <param name="tableName">表名</param>
/// <returns></returns>
public  static  int  GetRecordCount( string  tableName)
{
     return  GetRecordCount(tableName, "" );
}
/// <summary>
/// 得到记录总数
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="strWhere">筛选条件（可以不用加where）</param>
/// <returns></returns>
public  static  int  GetRecordCount( string  tableName, string  strWhere)
{
     string  strsql = string .Empty;
     if  ( string .IsNullOrEmpty(strWhere))
         strsql = "select count(*) from "  + tableName;
     else
         strsql = "select count(*) from "  + tableName + " where "  + strWhere;
     object  obj = TopkeeOA.DBUtility.DbHelperSQL.GetSingle(strsql);
     if  (obj == null )
     {
         return  0;
     }
     else
     {
         return  int .Parse(obj.ToString());
     }
}
 
/// <summary>
/// 分页获取数据列表（分页）
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="PageSize">每页显示多少要记录</param>
/// <param name="PageIndex">当前页</param>
/// <param name="strWhere">筛选条件（可为空）</param>
/// <param name="FieldName">排序字段（一般为主键ID）</param>
/// <param name="OrderType">排序类型（1为降序，0为升序）</param>
/// <returns></returns>
public  static  DataSet GetList( string  tableName, int  PageSize, int  PageIndex, string  strWhere, string  FieldName, int  OrderType)
{
     SqlParameter[] parameters = {
             new  SqlParameter( "@tblName" , SqlDbType.VarChar, 255),
             new  SqlParameter( "@fldName" , SqlDbType.VarChar, 255),
             new  SqlParameter( "@PageSize" , SqlDbType.Int),
             new  SqlParameter( "@PageIndex" , SqlDbType.Int),
             new  SqlParameter( "@IsReCount" , SqlDbType.Bit),
             new  SqlParameter( "@OrderType" , SqlDbType.Bit),
             new  SqlParameter( "@strWhere" , SqlDbType.VarChar,1000),
             };
     parameters[0].Value = tableName;
     parameters[1].Value = FieldName;
     parameters[2].Value = PageSize;
     parameters[3].Value = PageIndex;
     parameters[4].Value = 0;
     parameters[5].Value = OrderType;
     parameters[6].Value = strWhere;
     return  TopkeeOA.DBUtility.DbHelperSQL.RunProcedure( "TP_GetRecordByPage" , parameters, "ds" );
}

第一次使用请把“ALTER PROCEDURE ”改为“CREATE PROCEDURE ”以便创建一个新的存储过程。

AspNetPager调用示例：

private  void  DataBinder()
         {
             DataList1.DataSource = GetList( "Test" ,AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, strWhere, "" , 0);
             DataList1.DataBind();
         }
 
         protected  void  AspNetPager1_PageChanged( object  src, EventArgs e)
         {
             DataBinder();
         }

上面的是通用型的比较适用于sql2000，只支持主键排序，需要其它字段的大家可以改一下，下面的这个适用于sql 2005的通用分页排序存储过程，支持多字段排序，可以不一定要求是以主键排序

set  ANSI_NULLS ON
set  QUOTED_IDENTIFIER ON
GO
ALTER  Procedure  [dbo].[TP_GetRecordByPage2005]
  @tableName varchar (50),            --表名
  @fields varchar (2000) = '*' ,       --字段名(全部字段为*)
  @orderField varchar (500),          --排序字段(必须!支持多字段)
  @sqlWhere varchar (2000) = Null ,    --条件语句(不用加where)
  @pageSize int ,                     --每页显示多少条记录
  @pageIndex int  = 1 ,               --指定当前为第几页
  @orderType int  = 1,                --排序方式，非0则降序
  @TotalPage int  output               --返回总页数
as
begin
     Begin  Tran --开始事务
     Declare  @sql nvarchar(4000);
     Declare  @totalRecord int ;
     Declare  @sort varchar (10);
     
     if @orderType != 0
         set  @sort = ' DESC'
     else
         set  @sort = ' ASC'
 
     --计算总记录数
     if (@SqlWhere= ''  or  @sqlWhere= NULL )
         set  @sql = 'select @totalRecord = count(*) from '  + @tableName
     else
         set  @sql = 'select @totalRecord = count(*) from '  + @tableName + ' where '  + @sqlWhere
     EXEC  sp_executesql @sql,N '@totalRecord int OUTPUT' ,@totalRecord OUTPUT --计算总记录数
 
     --计算总页数
     select  @TotalPage=CEILING((@totalRecord+0.0)/@PageSize)
     if (@SqlWhere= ''  or  @sqlWhere= NULL )
         set  @sql = 'Select * FROM (select ROW_NUMBER() Over(order by '  + @orderField + @sort + ') as RowNum,'  + @fields + ' from '  + @tableName
     else
         set  @sql = 'Select * FROM (select ROW_NUMBER() Over(order by '  + @orderField + @sort + ') as RowNum,'  + @fields + ' from '  + @tableName + ' where '  + @SqlWhere
     
     --处理页数超出范围情况
     if @PageIndex<=0
         Set  @pageIndex = 1
 
     if @pageIndex>@TotalPage
         Set  @pageIndex = @TotalPage
 
      --处理开始点和结束点
     Declare  @StartRecord int
     Declare  @EndRecord int
 
     set  @StartRecord = (@pageIndex-1)*@PageSize + 1
     set  @EndRecord = @StartRecord + @pageSize - 1
 
     --继续合成sql语句
     set  @Sql = @Sql + ') as '  + @tableName + ' where RowNum between '  + Convert ( varchar (50),@StartRecord) + ' and '  +  Convert ( varchar (50),@EndRecord)
 
     Exec (@Sql)
     ---------------------------------------------------
     If @@Error <> 0
       Begin
         RollBack  Tran
         Return  -1
       End
      Else
       Begin
         Commit  Tran
         Return  @totalRecord ---返回记录总数
       End
end

以下是C#通用调用接口，需要引用DBUtility操作类

#region 公用存储过程分页相关操作
     /// <summary>
     /// 得到记录总数
     /// </summary>
     /// <param name="tableName">表名</param>
     /// <returns></returns>
     public  static  int  GetRecordCount( string  tableName)
     {
         return  GetRecordCount(tableName, "" );
     }
     /// <summary>
     /// 得到记录总数
     /// </summary>
     /// <param name="tableName">表名</param>
     /// <param name="strWhere">筛选条件（可以不用加where）</param>
     /// <returns></returns>
     public  static  int  GetRecordCount( string  tableName, string  strWhere)
     {
         string  strsql = string .Empty;
         if  ( string .IsNullOrEmpty(strWhere))
             strsql = "select count(*) from "  + tableName;
         else
             strsql = "select count(*) from "  + tableName + " where "  + strWhere;
         object  obj = TK.DBUtility.DbHelperSQL.GetSingle(strsql);
         if  (obj == null )
         {
             return  0;
         }
         else
         {
             return  int .Parse(obj.ToString());
         }
     }
 
     /// <summary>
     /// 分页获取数据列表（分页）
     /// </summary>
     /// <param name="tableName">表名</param>
     /// <param name="orderField">排序字段(必须!支持多字段，PS：多个字段用","分割，一般不为空，为空时默认为"ID")</param>
     /// <param name="sqlWhere">附加条件语句(不用加where)，可为空</param>
     /// <param name="pageSize">每页显示多少条记录</param>
     /// <param name="pageIndex">指定当前为第几页</param>
     /// <param name="OrderType">排序方式，非0则降序</param>
     /// <returns></returns>
     public  static  DataSet GetList( string  tableName, string  orderField, string  sqlWhere, int  pageSize, int  pageIndex, int  OrderType)
     {
         return  GetList(tableName, "*" , orderField, sqlWhere, pageSize, pageIndex, OrderType);
     }
 
 
     /// <summary>
     /// 分页获取数据列表（分页）
     /// </summary>
     /// <param name="tableName">表名</param>
     /// <param name="fields">字段名(PS：可为空，为空时显示全部字段，显示全部字段为"*")</param>
     /// <param name="orderField">排序字段(必须!支持多字段，PS：多个字段用","分割，一般不为空，为空时默认为"ID")</param>
     /// <param name="sqlWhere">附加条件语句(不用加where)，可为空</param>
     /// <param name="pageSize">每页显示多少条记录</param>
     /// <param name="pageIndex">指定当前为第几页</param>
     /// <param name="OrderType">排序方式，非0则降序</param>
     /// <returns></returns>
     public  static  DataSet GetList( string  tableName, string  fields, string  orderField, string  sqlWhere, int  pageSize, int  pageIndex, int  OrderType)
     {
 
         SqlParameter[] parameters = {
                     new  SqlParameter( "@tableName" , SqlDbType.VarChar, 50),
                     new  SqlParameter( "@fields" , SqlDbType.VarChar, 2000),
                     new  SqlParameter( "@orderField" , SqlDbType.VarChar, 500),
                     new  SqlParameter( "@sqlWhere" , SqlDbType.VarChar,2000),
                     new  SqlParameter( "@pageSize" , SqlDbType.Int),
                     new  SqlParameter( "@pageIndex" , SqlDbType.Int),
                     new  SqlParameter( "@orderType" , SqlDbType.Int),
                     new  SqlParameter( "@TotalPage" , SqlDbType.Int)
                     };
         parameters[0].Value = tableName;
         parameters[1].Value = string .IsNullOrEmpty(fields) ? "*"  : fields;
         parameters[2].Value = string .IsNullOrEmpty(orderField) ? "ID"  : orderField;
         parameters[3].Value = string .IsNullOrEmpty(sqlWhere) ? ""  : sqlWhere;
         parameters[4].Value = pageSize;
         parameters[5].Value = pageIndex;
         parameters[6].Value = OrderType;
         parameters[7].Direction = ParameterDirection.Output;
 
         return  TK.DBUtility.DbHelperSQL.RunProcedure( "TP_GetRecordByPage2005" , parameters, "ds" );
     }
     #endregion

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

csdn_aspnet

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值