ASP 101 - Active Server Pages 101 - Web03
The Place ASP Developers Go!

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
What is Adovbs.inc and Why Do I Need It?
An Overview of ASP.NET
Connections, Commands, And Procedures

QUICK TIP:
Authenticating Anonymous requests
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


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.


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers