Effective Data Paging Using SQL Server 2005 and Microsoft's Enterprise Library
by Gal Ratner
Displaying records from a table in a tabular way became very easy in ASP.NET. All you have to do is set
a DataSource property on a DataGrid component to a DataSet containing records (or any object implementing
the IList or IListSource interface). If the number of records in your data source is greater than the size
of your screen, you can page through your records using the paging mechanism built in to the DataGrid.
In most cases, this is a convenient and easy solution, but as your website becomes mature and the database
accumulates more data you will probably notice a considerable amount of delay while paging the grid. Don't
worry, your web server is still fine and so is your database server. The problem relies on the fact that
your data grid pulls out all the records in your data source and then divides it into pages. In essence,
you cache all the records in your table every time the page is loaded regardless of what you see on the
screen. Try paging through one million records and you will probably need to wait about five minutes
between page refreshes. That is assuming your web server can actually cache the data and will not run
out of memory.
Effective data paging always has two parameters, PageSize and CurrentPage. Non-effective data paging
will most likely have something like CurrentRecord and MaxRecords. This forces you to save the last
record somewhere in order to know which records you are going to be pulling out next. Also, non-effective
data paging can be done using a data adapter.
Overloads Public Function Fill( _
ByVal dataSet As DataSet, _
ByVal startRecord As Integer, _
ByVal maxRecords As Integer, _
ByVal srcTable As String _
) As Integer
myDataAdapter.Fill(myDataSet, 10, 20, "Clients")
Or a stored procedure.
CREATE PROCEDURE sproc_get_clients
@lastRecordID int,
@pageSize int
AS
SET ROWCOUNT @pageSize
SELECT
client_id,
client_name
FROM
tbl_client
WHERE
client_id > @ lastRecordID
ORDER BY client_id ASC
GO
Effective data paging is currently limited to stored procedures only. Effective data paging will always
page the data inside the application boundaries, meaning that you will not move data from server to server
just to page it.
SQL Server 2005 has made a big leap forward in the sense that it is actually geared towards data paging.
In our current stored procedure, we are going to be working with two new T-SQL functions. ROW_NUMBER is
a ranking function allowing you to provide sequential integer values to result rows. OVER specifies the
columns that will get a sequential integer value. As with SQL Server 2000, we are still going to have
to select our data into a temporary table. But don't worry, any movement of data into a temporary table
is extremely fast and does not require a lot of memory. In addition, the table is being destroyed and
the memory released at the end of the procedure's execution. Here is the code for our stored procedure:
CREATE PROCEDURE [dbo].[sproc_get_clients]
@PageSize [int] = -1,
@CurrentPage [int] = -1
WITH EXECUTE AS CALLER
AS
SELECT
ROW_NUMBER() OVER(ORDER BY client_name ASC) AS rownum,
client_id,
client_name
INTO
#tmp_tbl_client
FROM
tbl_client
CREATE UNIQUE CLUSTERED INDEX
idx_uc_rownum
ON
#tmp_tbl_client(rownum)
SELECT
rownum,
client_id,
client_name,
FROM
#tmp_tbl_client
WHERE
rownum BETWEEN (@CurrentPage-1)*@PageSize+1 AND @CurrentPage*@PageSize
ORDER BY
client_name ASC
The Enterprise Library from Microsoft is a convenience tool that can be found
here
and can actually save
you lots of time by preventing you from coding the same old ADO.NET objects over and over again. The
newest version is actually database independent and code changes are not needed between Oracle and SQL
Server. Tutorials on the Enterprise Library can be found on Microsoft's "patterns & practices"
website, and I'm sure if you Google it, you will find a lot more online. In this article, I'm assuming
you are already familiar or getting familiar with the Enterprise Library.
We are going to be working with two tiers in our example. The data tier will connect to the database and
activate the paged stored procedure while our presentation layer will manage the DataGrid.
Implementing the DataGrid: Since we are going to be paging within the application boundaries, our data
source will contain the exact records we are going to display on each page making the default data grid
paging obsolete. We will have to implement custom paging. This isn't a hard thing to do, as it only
requires us to set three parameters. In the data grid class, we need to set AllowCustomPaging property
to "True". This will allow us to define the virtual item count.
The VirtualItemCount property convinces the DataGrid to lay out its navigation to fit our desired total
range or records instead of its current DataSource size. It is then up to us to make sure our
DataSource contains the correct records. Getting the record count from the database and setting
VirtualItemCount in the grid looks like this:
In the Data Layer:
Public Function getClientCount() As Integer
Dim clientCount As Integer = 0
Try
Dim dbCommandWrapper As DBCommandWrapper = _
objDatabase.GetSqlStringCommandWrapper( _
"SELECT COUNT(client_id) FROM tbl_client")
clientCount = Convert.ToInt32(objDatabase.ExecuteScalar(dbCommandWrapper))
Catch ex As Exception
Throw New Exception("Error getting client count: " & ex.Message)
End Try
Return clientCount
End Function
In the presentation layer:
Function getItemCount()
Dim itemCount As Integer = 0
Dim client As New clientHandler
Try
client.useDefaultDatabase()
itemCount = client.getClientCount()
Catch ex As Exception
messageLabel.Text = "Error getting number of records: " & ex.Message
End Try
Return itemCount
End Function
clientDataGrid.VirtualItemCount = getItemCount()
Finally, setting the current page looks like this:
Sub clientDataGrid_PageIndexChanged(ByVal sender As Object,
ByVal e As DataGridPageChangedEventArgs)
clientDataGrid.CurrentPageIndex = e.NewPageIndex
Call bindGrid(clientDataGrid.PageSize, e.NewPageIndex + 1)
End Sub
All that is left for us to do is rebind the grid to the data source:
Sub bindGrid(ByVal pageSize As Integer, ByVal currentPage As Integer)
Dim client As New clientHandler
Try
client.useDefaultDatabase()
Dim objDataSet As DataSet = Nothing
objDataSet = client.getClients(pageSize, currentPage)
clientDataGrid.DataSource = objDataSet.Tables(0)
clientDataGrid.DataBind()
Catch ex As Exception
messageLabel.Text = "Error binding grid: " & ex.Message
End Try
End Sub
The end result will be a data grid moving fast between millions of records. For maximum performance, set your
pages to a smaller size. Remember, to implement paging, you cannot remove the _VIEWSTATE from your grid. So,
in every round trip you are actually getting two grids, but only one of them is visible. (This is actually the
default state for each DataGrid.)
About the Example
The example contains four files in two directories. The data directory contains our stored procedure conveniently
encapsulated within a text file and our data object, which uses the Enterprise Library to manage a table. The
web directory contains our web page along with the code behind it.
You can download the example in zip file format from here: DataGridPaging.zip (3.3KB)
About the Author
Mr. Gal Ratner graduated from the Technion Institute in Israel and has been writing software for over 10 years.
He is the founder and CEO of Inverted Software located in southern California, which consults to large organizations.
