SQL分页Procedure

USE [Device]
GO
/****** Object:  StoredProcedure [dbo].[Common_PageList]    Script Date: 2019/1/15 14:14:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[Common_PageList]
(
@tab nvarchar(max),---表名
@PageIndex int, --页码
@PageSize int, --每页容纳的记录数
@Sort VARCHAR(255), --排序字段及规则,不用加order by
@OrderType        int = 1    ,                            -- 排序类型 1:降序 其它为升序
@RecordCount int out
)
AS
declare @strSql nvarchar(max),
@strFld nvarchar(max)='*' --字段字符串
set nocount on;
 
   if @OrderType = 1
        BEGIN
            set @Sort = ' Order by ' + REPLACE(@Sort,',',' desc,') + ' desc '
        END
    else
        BEGIN
            set @Sort = ' Order by ' + REPLACE(@Sort,',',' asc,') + ' asc '        
        END
    
 set @strSql='SELECT @RecordCount=Count(*) From ('+@tab+') a  '
    execute sp_executesql @strSql,N'@RecordCount int out',@RecordCount out

  PRINT(@RecordCount);
  set @strSql=' SELECT * FROM (SELECT ROW_NUMBER() 
  OVER(' + @Sort + ' ) AS rownum, ' + @strFld + ' FROM (' + @tab + ') a  ) AS Dwhere
  WHERE rownum BETWEEN ' + CAST(((@PageIndex-1)*@PageSize + 1) as nvarchar(20)) + ' and ' + cast((@PageIndex*@PageSize) as nvarchar(20))
 

exec (@strSql)
return @RecordCount

 

调用：

        /// <param name="strSql">表或者查询的结果集sql</param>
        /// <param name="orderByColumns">排序的列</param>
        /// <param name="pageIndex"></param>
        /// <param name="PageSize"></param>
        /// <param name="totalCount"></param>
        /// <param name="Sort">排序类型 1:降序 其它为升序</param>
        /// <returns></returns>
        public static DataTable GetResultByPage(string strSql, string orderByColumns, int pageIndex, int PageSize, out int totalCount, string Sort)
        {
            DataSet ds = null;
            totalCount = 0;
            try
            {
                SqlParameter[] sqlparams ={
                                            new SqlParameter("@PageSize", SqlDbType.Int),
                                            new SqlParameter("@pageIndex", SqlDbType.Int),
                                            new SqlParameter("@RecordCount", SqlDbType.Int),
                                            new SqlParameter("@Sort", SqlDbType.VarChar),
                                            new SqlParameter("@tab", SqlDbType.VarChar),
                                            new SqlParameter("@OrderType", SqlDbType.VarChar)
                                          };
                sqlparams[0].Value = PageSize;
                sqlparams[1].Value = pageIndex;

                sqlparams[2].Direction = ParameterDirection.Output;
                sqlparams[3].Value = orderByColumns;
                sqlparams[4].Value = strSql;
                sqlparams[5].Value = Sort;
                ds = DbHelper.ExecuteDataSetByStoredProcedure("Common_PageList", sqlparams);
               

                if (ds != null && ds.Tables.Count > 0)
                {
                   
                    int.TryParse(Convert.ToString(sqlparams[2].Value), out totalCount);
                    //返回结果
                    return ds.Tables[0];
                }
                else
                {
                    return null;
                }
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }

以往分页都是查询两次数据库  一次data，一次sumcount ，此方法只需查询一次数据库

参数sql可以是连表的，跟平常sql一样  这里是把sql当一个数据源