I’ve dealt with custom ASP.NET Paging lately and wanted to share the fastest possible way to do that with you if you don’t use ADO.NET or any ASP.NET Controls.
Create a stored Procedure like this:
CREATE PROCEDURE [dbo].[usp_PageResults_NAI]
(
@startRowIndex int,
@maximumRows int
)
ASDECLARE @first_id int, @startRow int
— A check can be added to make sure @startRowIndex isn’t > count(1)
— from employees before doing any actual work unless it is guaranteed
— the caller won’t do that— Get the first employeeID for our page of records
SET ROWCOUNT @startRowIndex
SELECT @first_id = employeeID FROM employees ORDER BY employeeid— Now, set the row count to MaximumRows and get
— all records >= @first_id
SET ROWCOUNT @maximumRowsSELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeIDSET ROWCOUNT 0
GO
And in ASP.NET you execute a DataReader against this piece of code:
42 string SQL = “pics_Paging”;
43
44 cmd.Connection = cn;
45 cmd.CommandText = SQL;
46 cmd.Parameters.AddWithValue(“@startRowIndex”, 6);
47 cmd.Parameters.AddWithValue(“@maximumRows”, 4);
48 cmd.CommandType = System.Data.CommandType.StoredProcedure;
That’s all and it’s pretty speedy, thanks to 4GuysFromRolla.com for the input! All you have to do now is to create the logic for startRowIndex and maximumRows which is a piece of cake 😉
0 comments ↓
There are no comments yet...Kick things off by filling out the form below.
Leave a Comment