I recently came across a problem where I was going to be using huge datasets. Potentially 100k+ records that would be displayed in a data grid. Since the built in paging in the Asp.Net Data Grid sucks so bad I was going to come up with a different method that would use sql to do all my work. I ran across an article on
4GuysFromRolla and worked from there. Its a great solution and extremely fast.
Basically all you do is to declare a new table in your stored procedure, add an identity column, and a column that will link to the primary key of your dataset you want to return. Now you can do a select on your dataset with your order by clause to sort your data and insert each record in your other table based on your row count to get for the page size and the last record index. On the code end of things you just have to keep track of the last index you retrieved and the size of the page you want returned.
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
Subscribe