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

最新推荐文章于 2022-11-03 11:10:28 发布
原创最新推荐文章于 2022-11-03 11:10:28 发布 · 814 阅读
CC 4.0 BY-SA版权
网上分页存储过程的代码很多，这个是我一直在用的经典的三层架构里提取出来的，并做了部分语句的优化...

如有更好的欢迎讨论！


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