分页存储过程
1 USE [database] 2 GO 3 /****** Object: StoredProcedure [dbo].[SP_Com_SelectByPage] Script Date: 03/03/2014 13:01:19 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER PROCEDURE [dbo].[SP_Com_SelectByPage] 9 ( 10 @tblName nvarchar(Max), ----要显示的表或多个表的连接 11 @fldName nvarchar(max) = '*', ----要显示的字段列表 12 @pageSize int = 1, ----每页显示的记录个数 13 @page int = 1, ----要显示那一页的记录 14 @fldSort nvarchar(max) = null, ----排序字段列表或条件 15 @Sort bit = 1, ----排序方法，0为升序，1为降序(如果是多字段排列Sort指代最后一个排序字段的排列顺序(最后一个排序字段不加排序标记)--程序传参如：' SortA Asc,SortB Desc,SortC ') 16 @strCondition nvarchar(max) = null, ----查询条件,不需where 17 @ID nvarchar(150), ----主表的主键 18 @Dist bit = 0 ----是否添加查询字段的 DISTINCT 默认0不添加/1添加 19 ) 20 AS 21 SET NOCOUNT ON 22 Declare @sqlTmp nvarchar(max) ----存放动态生成的SQL语句 23 Declare @strTmp nvarchar(max) ----存放取得查询结果总数的查询语句 24 Declare @strID nvarchar(max) ----存放取得查询开头或结尾ID的查询语句 25 Declare @pageCount int ----查询结果分页后的总页数 26 Declare @Counts int ----查询到的记录数 27 Declare @strSortType nvarchar(10) ----数据排序规则A 28 Declare @strFSortType nvarchar(10) ----数据排序规则B 29 30 Declare @SqlSelect nvarchar(max) ----对含有DISTINCT的查询进行SQL构造 31 Declare @SqlSelectCount nvarchar(max) 32 Declare @SqlCounts nvarchar(max) ----对含有DISTINCT的总数查询进行SQL构造 33 Declare @FSort nvarchar(max) 34 Declare @DSort nvarchar(max) 35 set @pageCount=1 36 set @Counts=1 37 38 if @Dist = 0 39 begin 40 set @SqlSelect = 'select ' 41 set @SqlSelectCount = 'select ' 42 set @SqlCounts = 'Count(*)' 43 end 44 else 45 begin 46 set @SqlSelect = 'select distinct ' 47 --set @SqlCounts = 'Count(DISTINCT '+@ID+')' 48 set @SqlSelectCount = 'count(*) from ( ' 49 set @SqlCounts = 'select distinct '+@ID+' from '+@tblName+')as T' 50 end 51 52 53 if @Sort=0 54 begin 55 set @strFSortType=' ASC ' 56 set @strSortType=' DESC ' 57 end 58 else 59 begin 60 set @strFSortType=' DESC ' 61 set @strSortType=' ASC ' 62 end 63 64 if @fldSort IS NOT NULL or @fldSort <>'' 65 begin 66 set @FSort=' order by '+ @fldSort +' '+ @strFSortType 67 set @DSort=' order by '+ @fldSort +' '+ @strSortType 68 end 69 ELSE 70 Begin 71 SET @fldSort='' 72 END 73 74 75 76 77 --------生成查询语句-------- 78 --此处@strTmp为取得查询结果数量的语句 79 --print @strCondition 80 if @strCondition is null or @strCondition='' --没有设置显示条件 81 begin 82 if @Dist = 0 83 begin 84 set @sqlTmp = @fldName + ' From ' + @tblName 85 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName 86 set @strID = ' From ' + @tblName 87 end 88 else 89 begin 90 set @sqlTmp = @fldName + ' From ' + @tblName 91 set @strTmp = @SqlSelect+' @Counts='+@SqlSelectCount+@SqlCounts 92 set @strID = ' From ' + @tblName 93 end 94 end 95 else 96 begin 97 if @Dist =0 98 begin 99 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition 100 set @strTmp = @SqlSelect+' @Counts='+@SqlCounts+' FROM '+@tblName + ' where (1>0) ' + @strCondition 101 set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition 102 end 103 else 104 begin 105 set @sqlTmp = + @fldName + 'From ' + @tblName + ' where (1>0) ' + @strCondition 106 set @strTmp = @SqlSelect+' @Counts='+@SqlSelectCount+'select distinct '+@ID+' from '+@tblName+' where (1>0) ' + @strCondition+')as T' 107 set @strID = ' From ' + @tblName + ' where (1>0) ' + @strCondition 108 end 109 end 110 --print @strTmp 111 ----取得查询结果总数量----- 112 exec sp_executesql @strTmp,N'@Counts int out ',@Counts out 113 declare @tmpCounts int 114 if @Counts = 0 115 set @tmpCounts = 1 116 else 117 set @tmpCounts = @Counts 118 119 --取得分页总数 120 set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize 121 122 /**//**当前页大于总页数 取最后一页**/ 123 if @page>@pageCount 124 set @page=@pageCount 125 126 --/*-----数据分页2分处理-------*/ 127 declare @pageIndex int --总数/页大小 128 declare @lastcount int --总数%页大小 129 130 set @pageIndex = @tmpCounts/@pageSize 131 set @lastcount = @tmpCounts%@pageSize 132 if @lastcount > 0 133 set @pageIndex = @pageIndex + 1 134 else 135 set @lastcount = @pageSize 136 137 --//***显示分页 138 if @strCondition is null or @strCondition='' --没有设置显示条件 139 begin 140 --if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 141 --begin 142 set @strTmp= @SqlSelect+' * from ('+@SqlSelect+' '+@fldName+',Row_number() over('+ @FSort+') as IDRank from '+@tblName+')' 143 +' AS IDWithRowNumber where IDRank>'+CAST(@pageSize*(@page-1) AS Varchar(20)) +' and IDRank<'+CAST(@pageSize*@page+1 AS Varchar(20)) 144 --end 145 --else 146 -- begin 147 -- set @page = @pageIndex-@page+1 --后半部分数据处理 148 -- if @page <= 1 --最后一页数据显示 149 -- set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName 150 -- +@DSort+') AS TempTB'+@FSort 151 -- else 152 -- set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName 153 -- +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName 154 -- +@DSort+')' 155 -- +@DSort+') AS TempTB'+@FSort 156 -- end 157 end 158 159 else --有查询条件 160 begin 161 --if @pageIndex<2 or @page<=@pageIndex / 2 + @pageIndex % 2 --前半部分数据处理 162 --begin 163 set @strTmp= @SqlSelect+' * from ('+@SqlSelect+' '+@fldName+',Row_number() over('+ @FSort+') as IDRank from '+@tblName+' Where (1>0) '+@strCondition+')' 164 +' AS IDWithRowNumber where IDRank>'+CAST(@pageSize*(@page-1) as Varchar(20))+' and IDRank<'+CAST(@pageSize*@page+1 as Varchar(20)) 165 --end 166 --else 167 --begin 168 -- set @page = @pageIndex-@page+1 --后半部分数据处理 169 -- if @page <= 1 --最后一页数据显示 170 -- set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@lastcount as VARCHAR(4))+' '+ @fldName+' from '+@tblName 171 -- +' where (1>0) '+ @strCondition +@DSort+') AS TempTB'+@FSort 172 -- else 173 -- set @strTmp=@SqlSelect+' * from ('+@SqlSelect+' top '+ CAST(@pageSize as VARCHAR(4))+' '+ @fldName+' from '+@tblName 174 -- +' where '+@ID+' not in('+ @SqlSelect+' top '+ CAST(@pageSize*(@page-2)+@lastcount as Varchar(20)) +' '+ @ID +' from '+@tblName 175 -- +' where (1>0) '+ @strCondition +@DSort+')' 176 -- + @strCondition +@DSort+') AS TempTB'+@FSort 177 --end 178 end 179 --print @strTmp 180 ------返回查询结果----- 181 182 exec sp_executesql @strTmp 183 SELECT @pageCount AS PageCount,@Counts AS Counts 184 --print @strTmp 185 SET NOCOUNT OFF
相应的类和方法
public class PageResultDTO { public PageResultDTO(); public int Counts { get; set; } public int PageCount { get; set; } public DataTable Result { get; set; } } public PageResultDTO GetPagingData(PageSearchDTO page) { SqlParameter[] sqlPar = new SqlParameter[] { new SqlParameter("@tblName",page.TblName), new SqlParameter("@fldName",page.FieldName), new SqlParameter("@pageSize",page.PageSize), new SqlParameter("@page",page.Page), new SqlParameter("@fldSort",page.FieldSort), new SqlParameter("@Sort",page.Sort), new SqlParameter("@strCondition",page.Condition), new SqlParameter("@ID",page.ID), new SqlParameter("@Dist",page.IsDistint), }; DataSet ds_result = SqlHelper.ExecuteStoreProcedureDataSet(spName.GetPagingData, sqlPar); PageResultDTO pageresult = new PageResultDTO(); if (ds_result != null) { pageresult.Result = ds_result.Tables[0]; pageresult.PageCount = Convert.ToInt32(ds_result.Tables[1].Rows[0]["PageCount"]); pageresult.Counts = Convert.ToInt32(ds_result.Tables[1].Rows[0]["Counts"]); } return pageresult; }
