#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: <2012-11-8> -- Description: <通用分页存储过程> -- ============================================= CREATE PROCEDURE [dbo].[Pro_CommonPager] ( @PageIndex int,--索引页 1 @PageSize int,--每页数量2 @TableName nvarchar(500),--查询表名3 @Order nvarchar(500),--排序的列4 @SelectStr nvarchar(500) = '*',--查询的列5 @WhereCondition Nvarchar(max)='',--查询条件6 @OrderType bit = 0, -- 设置排序类型, 非 0 值则降序 7 @Groupby NVarChar(100) = '' ,--分組 8 @RecordCount int=-1 output,--总行数 9 @PageCount int=-1 output,--总页数10 @error nvarchar(100)='' OUTPUT, --错误信息11 @key nvarchar(500)--关键字 --@retun nvarchar(2000)='' output --错误信息11 ) AS declare @strSQL nvarchar(max) -- 主语句 declare @strTmp nvarchar(max) -- 临时变量 declare @strOrder nvarchar(max) -- 排序类型 declare @groupByStr nvarchar(100)--分组变量 declare @whereStr nvarchar(Max) declare @tableStr nvarchar(Max) set @groupByStr='' set @whereStr='' set @tableStr='' if @OrderType != 0 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-1)*@PageSize) + ' '+@SelectStr+' from '+@TableName +' '+ @whereStr + ' ' + @groupByStr + ' ' + @strOrder + ') strTable' end else begin set @tableStr=' select top ' + str((@PageIndex-1)*@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 = 1 begin set @strSQL = 'select top ' + str(@PageSize) + ' ' + @SelectStr + ' from ' + @TableName + ' ' + @whereStr + ' ' + @groupByStr + ' ' + @strOrder end exec (@strSQL) if(@@error<>0) 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<>0) 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<>0) begin set @error='分页数据出错﹗'; --drop table #tmp; end return GO