Wednesday, July 9, 2008

Creating a stored procedure for Custom Paging with the ASP.NET DataGrid Control

The DataGrid control is most flexible and robust data control offered b ASP.NET. It displays data from the database table, accommodates in-row editing and updating of displayed data, sorting of columns, and built-in paging.

The DataGrid built-in paging –> Storing the data source that is being displayed in the DataGrid. The data source must be retrieved in each post back, this obviously the least efficient approach.

The paging offered by the DataGrid control requires minimal coding. This code handles the page changing events, refreshes the data sources and binds the DataGrid to the DataSource again. The drawback is that it requires the entire data source be generated or retrieved in each postback. If data source contains 100 rows of data and the DataGrid will be displaying 10 rows at a time, the DataGrid will navigate through 10 pages of data. The paging requires the entire DataSource be present and CurrentPageIndex being set. If the CurrentPageIndex is set to 3, the DataGrid will display the rows 31 through 40 from the DataSource. Retrieving the complete DataSource incurs significant overhead in most cases.

Custom Paging provided by DataGrid gives the developer complete control over the paging behavior. Custom paging is enabled through the AllCustomPaging attribute being set to true. With Custom paging enabled, the entire data source is not required to be available to the DataGrid because developer controls the data that is being displayed. With this in mind, the most efficient method for implementing custom paging would be to only return the rows of data from the data source that are to be displayed on the currently displayed page. This can be accomplished best using a stored procedure.

Creating the Stored Procedure
A stored procedure is the best suited solution because it will be processed by SQL Server with only the results of the stored procedure being returned. Decreasing the amount of data passed back will significantly improve the performance of any data call. Additionally, the first time that a stored procedure is executed, SQL Server creates an execution plan for the stored procedure and caches it so that future calls to the stored procedure are much faster. The stored procedure created is shown below.

Create Procedure dbo.sqlj_GetClientsByPage

-- Declare parameters.
@CurrentPage As tinyint, @PageSize As tinyint, @TotalRecords As int OUTPUT
As

-- Turn off count return.
Set NoCount On

-- Declare variables.
Declare @FirstRec int
Declare @LastRec int

-- Initialize variables.
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize + 1)

-- Create a temp table to hold the current page of data
-- Add an ID column to count the records
Create Table #TempTable (ClientId int IDENTITY PRIMARY KEY, Name varchar(50), Balance smallmoney)

--Fill the temp table with the reminders
Insert Into #TempTable (Name, Balance)
Select Name As Name, Balance As Balance From Clients Order By Name

--Select one page of data based on the record numbers above
Select ClientId, Name, Balance From #TempTable Where ClientId > @FirstRec And ClientId < @LastRec --Return the total number of records available as an output parameter Select @TotalRecords = Count(*) From Clients The stored procedure above accepts a parameter that designates the currently displayed page in the DataGrid as well as the capacity of rows to display on each page. It returns the total number of rows that is found. A temporary table is created and populated with all client rows. A second query then pulls the correct range of rows out of the data in the temporary table. Using the Stored Procedure with a DataGrid Control