The ASP.NET DataGrid exposes a wonderful capability: data paging support.
When paging is enabled in the DataGrid, a fixed number of records is shown at
a time. Additionally, paging UI is also shown at the bottom of the DataGrid
for navigating through the records. The paging UI allows you to navigate
backwards and forwards through displayed data, displaying a fixed number of
records at a time.
There's one slight wrinkle. Paging with the DataGrid requires all of the
data to be bound to the grid. For example, your data layer will need to
return all of the data and then the DataGrid will filter all the displayed
records based on the current page. If 100,000 records are returned when
you're paging through the DataGrid, 99,975 records would be discarded on each
request (assuming a page size of 25). As the number of records grows, the
performance of the application will suffer as more and more data must be sent
on each request.
One good approach to writing better paging code is to use stored procedures.
Following Query shows a sample stored procedure that pages through the Orders
table in the Northwind database. In a nutshell, all you're doing here is
passing in the page index and the page size. The appropriate resultset is
calculated and then returned.
CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn
-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
-- Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY (1, 1) NOT NULL,
OrderID int
)
-- Insert into the temp table
INSERT INTO #PageIndex (OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC
-- Return total count
SELECT COUNT(OrderID) FROM Orders
-- Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID > @PageLowerBound AND
PageIndex.IndexID < @PageUpperBound
ORDER BY
PageIndex.IndexID
END
The total number of records returned can vary depending on the query being
executed. For example, a WHERE clause can be used to constrain the data
returned. The total number of records to be returned must be known in order
to calculate the total pages to be displayed in the paging UI. For example,
if there are 1,000,000 total records and a WHERE clause is used that filters
this to 1,000 records, the paging logic needs to be aware of the total number
of records to properly render the paging UI.
thanx,
Smith
