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当一个数据源