数据库通用分页存储过程
浏览数：32 /
时间：2015年06月12日
USE [你的数据库名称] GO /****** Object: StoredProcedure [dbo].[P_Pager_Select] Script Date: 04/14/2014 14:40:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[P_Pager_Select] @tbname sysname, --要分页显示的表名 @FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段 @PageCurrent int=1, --要显示的页码 @PageSize int=10, --每页的大小(记录数) @FieldShow nvarchar(1000)=‘‘, --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段 @FieldOrder nvarchar(1000)=‘‘, --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC用于指定排序顺序 @Where nvarchar(4000)=‘‘, --查询条件 @RecordCount int=1 OUTPUT ,--总记录数 @PageCount int=1 OUTPUT --总页数 AS SET NOCOUNT ON --检查对象是否有效 --IF OBJECT_ID(@tbname) IS NULL --BEGIN -- RAISERROR(N‘对象"%s"不存在‘,1,16,@tbname) -- RETURN --END IF OBJECTPROPERTY(OBJECT_ID(@tbname),N‘IsTable‘)=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N‘IsView‘)=0 AND OBJECTPROPERTY(OBJECT_ID(@tbname),N‘IsTableFunction‘)=0 BEGIN RAISERROR(N‘"%s"不是表、视图或者表值函数‘,1,16,@tbname) RETURN END --分页字段检查 IF ISNULL(@FieldKey,N‘‘)=‘‘ BEGIN RAISERROR(N‘分页处理需要主键（或者惟一键）‘,1,16) RETURN END --其他参数检查及规范 IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1 IF ISNULL(@PageSize,0)<1 SET @PageSize=10 IF ISNULL(@FieldShow,N‘‘)=N‘‘ SET @FieldShow=N‘*‘ IF ISNULL(@FieldOrder,N‘‘)=N‘‘ SET @FieldOrder=N‘‘ ELSE SET @FieldOrder=N‘ORDER BY ‘+LTRIM(@FieldOrder) IF ISNULL(@Where,N‘‘)=N‘‘ SET @Where=N‘‘ ELSE SET @Where=N‘WHERE (‘+@Where+N‘)‘ --如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值) IF @PageCount IS NULL BEGIN DECLARE @sql nvarchar(4000) SET @sql=N‘SELECT @PageCount=COUNT(*)‘ +N‘ FROM ‘+@tbname +N‘ ‘+@Where EXEC sp_executesql @sql,N‘@PageCount int OUTPUT‘,@RecordCount OUTPUT SET @PageCount=(@RecordCount+@PageSize-1)/@PageSize END IF @PageCurrent>@PageCount BEGIN SET @PageCurrent=@PageCount END IF @PageCount=0 BEGIN SET @PageCurrent=1 END --计算分页显示的TOPN值 DECLARE @TopN varchar(20),@TopN1 varchar(20) SELECT @TopN=@PageSize, @TopN1=@PageCurrent*@PageSize --第一页直接显示 DECLARE @SQL1 VARCHAR(500) IF @PageCurrent=1 BEGIN EXEC(N‘SELECT TOP ‘+@TopN +N‘ ‘+@FieldShow +N‘ FROM ‘+@tbname +N‘ ‘+@Where +N‘ ‘+@FieldOrder) END ELSE BEGIN --生成主键(惟一键)处理条件 DECLARE @Where1 nvarchar(4000),@s nvarchar(1000) SELECT @Where1=N‘‘,@s=@FieldKey WHILE CHARINDEX(N‘,‘,@s)>0 SELECT @Where1=@Where1 +N‘ AND a.‘+LEFT(@s,CHARINDEX(N‘,‘,@s)-1) +N‘=‘+LEFT(@s,CHARINDEX(N‘,‘,@s)-1) ,@s=STUFF(@s,1,CHARINDEX(N‘,‘,@s),N‘‘) SELECT @Where1=STUFF(@Where1+N‘ AND a.‘+@s+N‘=‘+@s,1,5,N‘‘), @TopN=@TopN1-@PageSize --执行查询 declare @filter varchar(100) SET @filter=replace(@Where,‘WHERE‘,‘AND‘) print(N‘SET ROWCOUNT ‘+@TopN1 +N‘ SELECT ‘+@FieldKey +N‘ INTO # FROM ‘+@tbname +N‘ ‘+@Where +N‘ ‘+@FieldOrder +N‘ SET ROWCOUNT ‘+@TopN +N‘ DELETE FROM #‘ +N‘ SELECT ‘+@FieldShow +N‘ FROM ‘+@tbname +N‘ a WHERE EXISTS(SELECT * FROM # WHERE ‘+@Where1 +N‘)‘+@filter+@FieldOrder) --SET @SQL1=(N‘SET ROWCOUNT ‘+@TopN1 -- +N‘ SELECT ‘+@FieldKey -- +N‘ INTO # FROM ‘+@tbname -- +N‘ ‘+@Where -- +N‘ ‘+@FieldOrder -- +N‘ SET ROWCOUNT ‘+@TopN -- +N‘ DELETE FROM #‘ -- +N‘ SELECT ‘+@FieldShow -- +N‘ FROM ‘+@tbname -- +N‘ a WHERE EXISTS(SELECT * FROM # WHERE ‘+@Where1 -- +N‘) ‘+@FieldOrder) EXEC(N‘SET ROWCOUNT ‘+@TopN1 +N‘ SELECT ‘+@FieldKey +N‘ INTO # FROM ‘+@tbname +N‘ ‘+@Where +N‘ ‘+@FieldOrder +N‘ SET ROWCOUNT ‘+@TopN +N‘ DELETE FROM #‘ +N‘ SELECT ‘+@FieldShow +N‘ FROM ‘+@tbname +N‘ a WHERE EXISTS(SELECT * FROM # WHERE ‘+@Where1 +N‘) ‘+@FieldOrder) --SET @SQL1=(N‘SET ROWCOUNT ‘+@TopN1 -- +N‘ SELECT ‘+@FieldKey -- +N‘ INTO # FROM ‘+@tbname -- +N‘ ‘+@Where -- +N‘ ‘+@FieldOrder -- +N‘ SET ROWCOUNT ‘+@TopN -- +N‘ DELETE FROM #‘ -- +N‘ SELECT ‘+@FieldShow -- +N‘ FROM ‘+@tbname -- +N‘ a WHERE EXISTS(SELECT * FROM # WHERE ‘+@Where1 -- +N‘) ‘+@FieldOrder) PRINT @SQL1 END
/// <summary> /// 查询操作记录-分页 /// </summary> /// <param name="pageindex">当前页码</param> /// <param name="pagesize">每页行数</param> /// <param name="recordCount">共多少条数据</param> /// <param name="pageCount">供多少页</param> /// <param name="fieldOrder">按什么排序</param> /// <param name="where"></param> /// <returns></returns> public List<Model.SSART.Auction> Select(int pageindex, int pagesize, out int recordCount, out int pageCount, string where, string fieldOrder) { DbParameter[] prams = { DbRead.MakeInParam("@tbname", DbType.String, 100, "Auction"), DbRead.MakeInParam("@FieldKey", DbType.String, 100, "AuctionID"), DbRead.MakeInParam("@PageSize", DbType.Int32, 4, pagesize), DbRead.MakeInParam("@PageCurrent", DbType.Int32, 4, pageindex), DbRead.MakeOutParam("@RecordCount", DbType.String, 10000), DbRead.MakeOutParam("@PageCount", DbType.String, 10000), DbRead.MakeInParam("@FieldOrder", DbType.String, 500, fieldOrder), DbRead.MakeInParam("@Where", DbType.String, 4000, @where) }; DataTable dt = DbRead.ExecuteDataSet("P_Pager_Select", CommandType.StoredProcedure, prams).Tables[0]; recordCount = Convert.ToInt32(prams[4].Value); pageCount = Convert.ToInt32(prams[5].Value); if (dt != null) { return (from DataRow row in dt.Rows select new Model.SSART.Auction { AuctionID = row["AuctionID"].ToInt(0), ViewAddress = row["ViewAddress"].ToString(), Turnover = row["Turnover"].ToInt(0), CreatedTime = row["CreatedTime"].ToDateTime(DateTime.Now), Status = row["Status"].ToInt(0), AuHousesId = row["AuHousesId"].ToInt(0), ProvinceId = row["ProvinceId"].ToInt(0), CityId = row["CityId"].ToInt(0), CountyId = row["CountyId"].ToInt(0), Title = row["Title"].ToString(), StartTime = row["StartTime"].ToDateTime(DateTime.Now), EndTime = row["EndTime"].ToDateTime(DateTime.Now), Address = row["Address"].ToString(), ViewStartTime = row["ViewStartTime"].ToDateTime(DateTime.Now), ViewEndTime = row["ViewEndTime"].ToDateTime(DateTime.Now) }).ToList(); } return null; }
/// <summary> /// 查询操作记录-分页 /// </summary> /// <param name="pageindex">当前页码</param> /// <param name="pagesize">每页行数</param> /// <param name="recordCount">共多少条数据</param> /// <param name="pageCount">供多少页</param> /// <param name="fieldOrder">按什么排序</param> /// <param name="where"></param> /// <returns></returns> public List<Model.SSART.Auction> Select(int pageindex, int pagesize, out int recordCount, out int pageCount, string where, string fieldOrder = "CreatedTime DESC") { return dal.Select(pageindex, pagesize, out recordCount, out pageCount, where, fieldOrder); }
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
