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.