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

最新推荐文章于 2021-01-26 18:20:40 发布

ahsjh2444

最新推荐文章于 2021-01-26 18:20:40 发布

阅读量98

点赞数

文章标签： c#

原文链接：http://www.cnblogs.com/VvxT/archive/2011/01/06/1927521.html

版权

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

如有更好的欢迎讨论！

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

转载于:https://www.cnblogs.com/VvxT/archive/2011/01/06/1927521.html

ahsjh2444

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL：分页存储过程(优化了部分语句,附C#调用接口)

网上分页存储过程的代码很多，这个是我一直在用的经典的三层架构里提取出来的，并做了部分语句的优化...如有更好的欢迎讨论！set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGO--------------------------------------用途：分页存储过程(对有主键的表效率极高) --说明：（优化了部分语句...
复制链接

扫一扫

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

“相关推荐”对你有帮助么？