我們?cè)陂_發(fā)過程中會(huì)出現(xiàn)很多問題，但是遇到問題的時(shí)候都是解決了就可以了，本文是錯(cuò)新技術(shù)頻道小編為大家?guī)淼目偨Y(jié)C#中常用的分頁存儲(chǔ)過程，希望對(duì)你學(xué)習(xí)這方面知識(shí)有所幫助。
表中主鍵必須為標(biāo)識(shí)列，[ID] int IDENTITY (1,1)//每次自增一
1.分頁方案一：(利用Not In和SELECT TOP分頁)
語句形式：
復(fù)制代碼 代碼如下:
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID
SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 頁大小*頁數(shù) id
FROM 表
ORDER BY id))
ORDER BY ID
2.分頁方案二：(利用ID大于多少和SELECT TOP分頁）
語句形式：
?
復(fù)制代碼 代碼如下:
?
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID
SELECT TOP 頁大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 頁大小*頁數(shù) id
FROM 表
ORDER BY id) AS T))
ORDER BY ID
3.分頁方案三：(利用SQL的游標(biāo)存儲(chǔ)過程分頁)
?
復(fù)制代碼 代碼如下:
?
create procedure SqlPager
@sqlstr nvarchar(4000), --查詢字符串
@currentpage int, --第N頁
@pagesize int --每頁行數(shù)
as
set nocount on
declare @P1 int, --P1是游標(biāo)的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1, @rowcount=@rowcount output
select ceiling(1.0*@rowcount/@pagesize) as 總頁數(shù)--,@rowcount as 總行數(shù),@currentpage as 當(dāng)前頁
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off
4.
?
復(fù)制代碼 代碼如下:
?
CREATE Procedure FramWorkPage
@TableName varchar(50), --表名
@Fields varchar(5000) = '*', --字段名(全部字段為*)
@OrderField varchar(5000), --排序字段(必須!支持多字段)
@sqlWhere varchar(5000) = Null,--條件語句(不用加where)
@pageSize int, --每頁多少條記錄
@pageIndex int = 1 , --指定當(dāng)前為第幾頁
@TotalPage int output, --返回條數(shù)
@OrderType bit -- 設(shè)置排序類型,1 升序 0 值則降序
as
begin
declare @strOrder varchar(400) -- 排序類型
Begin Tran --開始事務(wù)
Declare @sql nvarchar(4000);
Declare @totalRecord int;
--計(jì)算總記錄數(shù)
if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
set @sql = 'select @totalRecord = count(*) from ' + @TableName
else
set @sql = 'select @totalRecord = count(*) from ' + @TableName + ' where ' + @sqlWhere
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT--計(jì)算總記錄數(shù)
--計(jì)算總頁數(shù)
select @TotalPage=@totalRecord --CEILING((@totalRecord+0.0)/@PageSize)
if @OrderType = 0
begin
set @strOrder = ' order by [' + @OrderField +'] desc'
--如果@OrderType是0，就執(zhí)行降序，這句很重要！
end
else
begin
set @strOrder = ' order by [' + @OrderField +'] asc'
end
if (@SqlWhere ='''' or @SqlWhere='' or @sqlWhere is NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over( '+@strOrder+' ) as rowId,' + @Fields + ' from ' + @TableName + ' where ' + @SqlWhere
--處理頁數(shù)超出范圍情況
if @PageIndex<=0
Set @pageIndex = 1
if @pageIndex>@TotalPage
Set @pageIndex = @TotalPage
--處理開始點(diǎn)和結(jié)束點(diǎn)
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @pageSize - 1
if @OrderType = 0
begin
set @strOrder = ' order by rowid desc'
--如果@OrderType是0，就執(zhí)行降序，這句很重要！
end
else
begin
set @strOrder = ' order by rowid asc'
end
--繼續(xù)合成sql語句
set @Sql = @Sql + ') as ' + @TableName + ' where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord) + ' '+@strOrder
-- print @sql
Exec(@Sql)
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return -1
End
Else
Begin
Commit Tran
Return @totalRecord ---返回記錄總數(shù)
End
end
以上就是關(guān)于總結(jié)C#中常用的分頁存儲(chǔ)過程，如果你還想了解的跟多，歡迎你來錯(cuò)新技術(shù)頻道進(jìn)行學(xué)習(xí)，更多更全的ASP.NET的知識(shí)一定會(huì)讓你大開眼界。
