Asp.net data access layer Stored Procedure paging statement

Therefore, it is best to use the Stored Procedure for paging at the data access layer. the following uses the employee table in the pubs database as an example to store data paging. You can refer to it to create your own stored procedure based on your actual situation.

Note: @ pageindex refers to the index of the data page, @ dataperpage refers to the number of records on each page, and @ howmanyrecords is used to obtain the total number of records.
Copy codeThe Code is as follows:
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

The Row_number function can number each retrieved record in order.

Next, you can call the stored procedure in the background code of the asp.net webpage to obtain the desired data.