#region 分页获取数据

        /// <summary>

        /// 分页获取数据

        /// </summary>

        /// <typeparam name="T">实体类</typeparam>

        /// <param name="selectStr">获取列</param>

        /// <param name="tableName">表名</param>

        /// <param name="pageIndex">页码</param>

        /// <param name="pageSize">每页数据量</param>

        /// <param name="strWhere">查询条件</param>

        /// <param name="key">关键字</param>

        /// <param name="orderName">排序字段</param>

        /// <param name="orderType">排序方式（非 0 值则降序）</param>

        /// <param name="groupBy">分组字段</param>

        /// <param name="RecordCount">输出查询的总数据量</param>

        /// <returns></returns>

        public List<T> GetUserAccess<T>(string selectStr, string tableName, int pageIndex, int pageSize, string strWhere, string key, string orderName, string orderType, string groupBy, ref int RecordCount)

        {

            var p = new DynamicParameters();

            p.Add("@PageIndex", pageIndex);

            p.Add("@PageSize", pageSize);

            p.Add("@Order", orderName);

            p.Add("@SelectStr", selectStr);

            p.Add("@OrderType", orderType);

            p.Add("@Groupby", groupBy);

            p.Add("@key", key);

            p.Add("@TableName", tableName);

            p.Add("@WhereCondition", strWhere);

            p.Add("@RecordCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

            p.Add("@PageCount", dbType: DbType.Int32, direction: ParameterDirection.Output);

            p.Add("@error", "", dbType: DbType.String, direction: ParameterDirection.Output);

            List<T> ret = new List<T>();

            using (var conn = DbFactory.GetDbConnection("MirrorMngr"))

            {

                ret = conn.Query<T>("Pro_CommonPager", p, commandType: CommandType.StoredProcedure).ToList();

                //ret = GetCJFDData(ret);

                RecordCount = p.Get<int>("RecordCount");

            }

            return ret;

        }

        #endregion

USE [MirrorMngr]

GO

/****** Object:  StoredProcedure [dbo].[Pro_CommonPager]    Script Date: 09/16/2015 11:50:58 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--　=============================================

--　Author:　　　　　　　　

--　Create　date:　<-->

--　Description:　　　　<通用分页存储过程>

--　=============================================

CREATE   PROCEDURE [dbo].[Pro_CommonPager]

(

@PageIndex int,--索引页

@PageSize int,--每页数量2

@TableName nvarchar(),--查询表名3

@Order  nvarchar(),--排序的列4

@SelectStr nvarchar() = '*',--查询的列5

@WhereCondition Nvarchar(max)='',--查询条件6

@OrderType bit = , -- 设置排序类型, 非  值则降序

@Groupby NVarChar() = '' ,--分組

@RecordCount int=- output,--总行数

@PageCount int=- output,--总页数10

@error nvarchar()='' OUTPUT, --错误信息11

@key nvarchar()--关键字

--@retun nvarchar()='' output --错误信息11

)

AS

declare @strSQL nvarchar(max) -- 主语句

declare @strTmp nvarchar(max) -- 临时变量

declare @strOrder nvarchar(max) -- 排序类型

declare @groupByStr nvarchar()--分组变量

declare @whereStr nvarchar(Max)

declare @tableStr nvarchar(Max)

set @groupByStr=''

set @whereStr=''

set @tableStr=''

    if @OrderType !=

        begin

         set @strTmp = '<(select min'

         set @strOrder = ' order by ' + @Order +' desc'

        end

    else

        begin

         set @strTmp = '>(select max'

         set @strOrder = ' order by ' + @Order +' asc'

        end

    if @Groupby <>''

        begin

            set @groupByStr=' group by '+@Groupby+' '

        end

    if @WhereCondition <> ''

        begin

            set @whereStr=' where '+@WhereCondition+' '

        end

    set @tableStr=' '+@TableName + ' ' + @groupByStr + ' ' + @strOrder + ' '

    if @groupByStr <> ''

      begin

        set @tableStr=' select  [' + @key + '] from  (select  top ' + str((@PageIndex-)*@PageSize) + ' '+@SelectStr+' from '+@TableName +' '+ @whereStr + ' ' + @groupByStr + ' ' + @strOrder + ') strTable'

      end

      else

      begin

        set @tableStr=' select  top ' + str((@PageIndex-)*@PageSize) + ' [' + @key + '] from  '+@TableName + ' '+@whereStr+' ' + @strOrder + ' '

      end

    set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '

     + @TableName + ' where ' + @key + ' not in ('+@tableStr+ ')'

     + ' ' + @groupByStr + ' ' + @strOrder   

    if @WhereCondition <> ''

     set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '

      + @TableName + ' where ' + @key + ' NOT IN ('+@tableStr + ') and (' + @WhereCondition + ')  ' + @groupByStr + ' ' + @strOrder  

    if @PageIndex =

        begin

         set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from '

          + @TableName + ' ' + @whereStr + ' ' + @groupByStr + ' ' + @strOrder

        end

    exec (@strSQL)

    if(@@error<>)

    begin

     set @error='分页数据出错﹗';

     return;

    end

    --SET @retun= @strSQL;

--print @strSQL

 --创建一个临时表用于快速计算行数

 --create table  #tmp ( counts int );---创建临时表

 --取得总记录数   

    IF @Groupby <> ''

        begin

            SET @strTmp = 'SELECT  @RecordCount=Count(*) FROM (select '+@Groupby+' from ' + @TableName +' '+@whereStr+' '+@groupByStr+' ) groupTable'

        end

      else

        begin

            SET @strTmp = 'SELECT  @RecordCount=Count(*) FROM '+ @TableName +' '+@whereStr+' '

        end

     EXEC SP_EXECUTESQL @strTmp,N'@RecordCount int output',@RecordCount output  

     if(@@error<>)

     begin

      set @error='分页数据出错﹗';

      --drop table #tmp;

      return;

     end    

 --select @RecordCount=counts from #tmp

 --SET @RecordCount = @@RowCount

  --    获取总页数

 --    "CEILING"函数:取得不小于某数的最小整数  

 SET @PageCount = CEILING(@RecordCount * 1.0 / @PageSize)   

 if(@@error<>)

     begin

      set @error='分页数据出错﹗';

      --drop table #tmp;

     end

 return    

GO