asp.net data access layer stored procedure paging statement
- 2020-05-07 19:28:31
- OfStack
Therefore, it is better to page in the data access layer, if so, you need to use stored procedures to page. The following is an example of a stored procedure for data paging, which is the employee table in pubs database, and you can refer to it to create your own stored procedures according to the actual situation.
Note: the index of the @pageindex data page, the number of records per page of @dataperpage, and the total number of records used by @howmanyrecords.
Where the Row_number function Numbers each retrieved record in order.
You can then call the stored procedure in the asp.net web code to get the data you want.
Note: the index of the @pageindex data page, the number of records per page of @dataperpage, and the total number of records used by @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
Where the Row_number function Numbers each retrieved record in order.
You can then call the stored procedure in the asp.net web code to get the data you want.