Introduction
The GridView is new in ASP.Net 2.0 and provides you with an easy way of displaying grids of data from various data sources. If you have lots of data to display it is very easy to turn on paging in the gridview to provide users with more manageable chunks of data and use screen real estate better. However, the default method of paging with the GridView can come with performance drawbacks when you have hundreds of records to deal with. This is because all the data is brought back to the client application and processed to provide paged data to your control. This article will show you how to offload paging to SQL Server to gain the performance benefits of it's optimized query engine. This will cause less data to be transferred over the network, less processing on the client and provide more responsive behaviour to users of your application.
What this post will accomplish
This post is made up of three parts:
- Implementing paging using a stored procedure created for SQL Server 2005/2008
- Setting up the middle layers of your application to support paging
- Implementing paging with the GridView using the ObjectDataSource
- In this post we will implement and test a stored procedure that provides paging. I will be using the AdventureWorks database for this post which you can find here
Designing The Stored Procedure
I will be using the AdventureWorks database to create the stored procedure. For our example we want to display employees in a specific department. Instead of using temporary tables or table variables to build our result set - as is common in paging routines - we will be using a Common Table Expression (CTE) one of the new features of SQL Server 2005. CTE's provide additional performance gains over temp tables and table variables and also provides for cleaner looking code as oposed to using derived tables. We will also use the ROw_NUMBER() function, another new feature of SQL Server 2005 to generate row numbers that are used as references to remember our position in the dataset when we return to the server for the next or previous page of data. Here is the basic idea:
- Callers of your stored procedrre will specify a department number, a page number and the number of records they want returned which is generally referred to as the page size.
- In order to provide the correct resulset to your callers you will need to have your data queried in a specific order and indexe each row uniquely so that subsequent calls to your stored procedure can use the page number to correctly return the next or previous set of data
- Limit the number of records return to the caller by using the requested page size
Let's take a look at that plumbing involved with implementing our idea.
Listing 1.
CREATE PROCEDURE [dbo].[HR_GetByDepartmentID]
@DepartmentID int,
@PageNum int,
@PageSize int,
@TotalRowsNum int output
AS
-- SET NOCOUNT ON added to prevent extra round trip to the server
-- to retrieve row count
SET NOCOUNT ON;
-- Use ROW_NUMBER function
WITH Employee_cte As (
SELECT
'RowNumber' = ROW_NUMBER() OVER(ORDER BY hre.EmployeeID DESC)
,hre.EmployeeID
,pc.ContactID
,hrd.DepartmentID
,pc.FirstName
,pc.LastName
,pc.EmailAddress
,hrd.Name As DepartmentName
FROM HumanResources.Employee AS hre
INNER JOIN Person.Contact As pc
ON hre.ContactID = pc.ContactID
INNER JOIN HumanResources.EmployeeDepartmentHistory As hredh
ON hre.EmployeeID = hredh.EmployeeID
INNER JOIN HumanResources.Department As hrd
ON hredh.DepartmentID = hrd.DepartmentID
WHERE hredh.EndDate IS NULL AND hrd.DepartmentID = COALESCE(@DepartmentID hrd.DepartmentID)
)
-- Query result
SELECT *
FROM Employee_cte
WHERE RowNumber BETWEEN ((@PageNum - 1) * @PageSize + 1) AND (@PageNum * @PageSize)
ORDER BY DepartmentName, LastName ASC
-- Returns total records count
SELECT
@TotalRowsNum = COUNT(*)
FROM HumanResources.Employee AS hre
INNER JOIN Person.Contact As pc
ON hre.ContactID = pc.ContactID
INNER JOIN HumanResources.EmployeeDepartmentHistory As hredh
ON hre.EmployeeID = hredh.EmployeeID
INNER JOIN HumanResources.Department As hrd
ON hredh.DepartmentID = hrd.DepartmentID
WHERE hredh.EndDate IS NULL AND hrd.DepartmentID = @DepartmentID
Stay tuned for Part II of this article where I will talk about setting up the middle layers to support paging.
Thanks,
Denzil Brown
