所以最好在數據訪層分頁,如果這樣就要使用存儲過程來分頁.以下是以pubs 數據庫中的employee表為例來進行數據分頁的存儲過程,你可以參考它根據實際情況來創建自己的存儲過程.
注:@pageindex 數據頁的索引,@dataperpage 每頁的記錄數目,@howmanyrecords 用來獲取總的記錄數.
代碼如下:
create proc getdata @pageindex int,@dataperpage int,@howmanyrecords int output
as
declare @temptable table
(
rowindex int,
emp_id char(9),
fname varchar(20),
minit char(1),
lname varchar(30)
)
insert into @temptable
select row_number() over(order by emp_id) as rowindex,emp_id,fname,minit,lname
from employee
select @howmanyrecords=count(rowindex) from @temptable
select * from @temptable
where rowindex>(@pageindex-1)*@dataperpage
and rowindex<=@pageindex*@dataperpage
declare @howmanyrecords int
exec getdata 2,5,@howmanyrecords output
select @howmanyrecords
declare @x int, @y int, @z int
select @x = 1, @y = 2, @z=3
select @x,@y,@z
create proc getdata2 @pageindex int,@dataperpage int,@howmanyrecords int output
as
declare @temptable table
(
rowindex int,
emp_id char(9),
fname varchar(20),
minit char(1),
lname varchar(30)
)
insert into @temptable
select row_number() over(order by emp_id) as rowindex,emp_id,fname,minit,lname
from employee
select @howmanyrecords=count(rowindex) from @temptable
select * from @temptable
where rowindex>(@pageindex-1)*@dataperpage
and rowindex<=@pageindex*@dataperpage
其中Row_number 函數可以給檢索來的每條記錄按照排序來編號.
接下來你就可以在asp.net 網頁后臺代碼中調用該存儲過程,就可以獲取想要的數據.
