ASP.NET Paging

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
)
AS

DECLARE @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 @maximumRows

SELECT e.*, d.name as DepartmentName
FROM employees e
INNER JOIN Departments D ON
e.DepartmentID = d.DepartmentID
WHERE employeeid >= @first_id
ORDER BY e.EmployeeID

SET 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