Data Paging

Display Your Data One Page at a Time

DataDriven

LANGUAGES: VB

TECHNOLOGIES: ADO.NET | SQL Server | Stored Procedures | DataGrid control

 

Data Paging

Display Your Data One Page at a Time

 

By Wayne S. Freeze

 

One of the nice features of a Web page is that you can make it as long as you want. This makes it easy to create database programs that retrieve a variable number of rows from your database. However, just because you easily can display a virtually unlimited number of database rows on a Web page doesn t mean you should. If you display too much information, your Web page will take a long time to load and will consume additional resources on both your Web server and your database server.

 

The solution to this problem is to display only some of the data on a Web page while providing an easy way for the user to request additional information. This concept is known as data paging. In this article, I ll explore some different approaches to data paging that you can use in your ADO.NET applications.

 

Simple Paging

The easiest way to page through your data is to design your database in such a way so you easily can retrieve a range of data you want to display. Then you can use a simple program to retrieve a range of rows from the database and display them in the user s browser (see FIGURE 1).

 


FIGURE 1: Displaying information from the music database one page at a time (CDPage1VB.aspx). (See end of article for information on downloading accompanying files.)

 

This program retrieves a series of rows from the Music database using a stored procedure named GetCDListByPage1 and displays them using the DataGrid control. The key to making this program work is the stored procedure. It simply retrieves the rows from the CDs table when the value of CDId is between @StartKey and @StopKey. Then, GetCDListByPage1 joins these rows with rows from the Artists table using the ArtistId column. Finally, the stored procedure sorts the resulting rows by CDId:

 

CREATE Procedure GetCDListByPage1

   @StartKey As Int, @StopKey As Int

As

   Select CDId, CDTitle, ArtistName

   From CDs c, Artists a

   Where c.ArtistId = a.ArtistId And

   CDId >= @StartKey And CDId <= @StopKey

   Order By CDId

 

The GetDataForGrid subroutine (see FIGURE 2) is responsible for calling the GetCDListByPage1 stored procedure and binding the results to the DataGrid control. The routine takes a single parameter named KeyValue, which contains the CDId value for the first row to be displayed.

 

Sub GetDataForGrid(KeyValue As Integer)

  Dim i As Integer

 

  Dim ds As New DataSet

  Dim conn As New SqlConnection(ConnectionStr)

  Dim adpt As New SqlDataAdapter

  Dim cmd As New SqlCommand("GetCDListByPage1", conn)

 

  cmd.CommandType = CommandType.StoredProcedure

  cmd.Parameters.Add("@StartKey", SqlDbType.Int).Value = _

   KeyValue

  cmd.Parameters.Add("@StopKey", SqlDbType.Int).Value = _

   KeyValue + 9

 

  adpt.SelectCommand = cmd

  adpt.Fill(ds, "CDList")

 

  If ds.Tables("CDList").Rows.Count > 0 Then

    DataGrid1.DataSource = ds.Tables("CDList").DefaultView

    DataGrid1.DataBind()

    i = ds.Tables("CDList").Rows.Count

    TextBox1.Text = ds.Tables("CDList").Rows(i-1). _

     Item("CDId").ToString

  End If

End Sub

FIGURE 2: Getting the information from the Music database and binding it to the DataGrid (CDPage1VB.aspx).

 

The routine begins by creating a SqlCommand object containing the parameters to the stored procedure. KeyValue contains the starting row that should be retrieved from the database. Because this Web page displays only 10 rows at a time, @StopKey is set to a value nine higher than @StartKey. Next, the SqlCommand object is assigned to the SelectCommand property of a SqlDataAdapter object. Then, the data adapter s Fill method is used to call the stored procedure and load the 10 rows into a DataSet object.

 

If any rows are retrieved, they are bound to the data grid. Then, the CDId value of the last row retrieved is saved in a TextBox control on the page. This value will be used when the user requests a new page. To prevent the user from seeing or modifying this value, the Width property is set to zero. So, even though the text box is hidden from view, this value actually is transmitted to the browser and can be retrieved and manipulated as necessary.

 

The Web page has two buttons, one that displays the next 10 values (Button2) and another that displays the previous 10 values (Button1). In the event associated with Button2, the code merely calls the GetDataForGrid routine using a starting CDId value that is one higher than the value stored in TextBox1. The Button1 event, on the other hand, subtracts 20 from the value in TextBox1 to get the new starting value. Of course, if the value in TextBox1 is less than 20, a value of one is used to ensure the user doesn t move beyond the first row.

 

This approach to paging has a couple of big advantages but some big disadvantages as well. Because the Web page is stateless, this approach is very scalable. The only state information is in the value stored in the hidden text box on the page. The other advantage is that the stored procedure only has to process 10 rows to get all the information needed. Even though the Order By clause is included in the stored procedure, the effect of the sort is trivial because only 10 rows are sorted.

 

While this is a very efficient and scalable program, its disadvantages limit its usefulness. First, the routine assumes you have a numeric key value in the database that can be used to retrieve the row values. It also assumes this numeric key value will never have gaps in the numbers. Otherwise, the routine wouldn t be able to compute the next group of rows to be retrieved. This routine only works when the data stored in the database is retrieved in order by the key value. If you wish to retrieve the data from the database in a different order, this approach is useless.

 

Paging with Temporary Tables

One way to address the requirement that the data must be extracted from the database using a numeric primary key is to do a bit of fancy stored-procedure programming. Rather than simply retrieving the rows based on CDId, you can create a stored procedure that uses a temporary table to hold some intermediate results and then return the selected rows to your application.

 

In SQL Server, a temporary table is created like a normal table, except that the name of the table is preceded by a pound sign (#). SQL Server creates temporary tables in tempdb, so they don t become part of the normal database where your application data is stored. A temporary table will exist as long as an open connection to the database exists and is accessible only to the creator. If you create a table whose name begins with two pound signs (##), the table is considered to be a global temporary table that is accessible to anyone.

 

The key to making this stored procedure work is that the temporary table is created with an Identity column. The Identity column contains a unique numeric value that is incremented by SQL Server automatically each time a row is inserted into the table. This means you can create a temporary table with all the fields you want to return as well as another column that contains the Identity value.

 

In FIGURE 3, the GetCDListByPage2 stored procedure creates a temporary table with the same three columns used in the previous example, as well as a fourth Identity column named Seq. This column is defined as the primary key for the table, which means the rows will be retrieved in order by primary key.

 

CREATE Procedure GetCDListByPage2

   @StartKey As Int, @StopKey As Int

As

   Create Table #TempTable (

      Seq Int Identity Primary Key,

      CDId Int,

      CDTitle Varchar(255),

      ArtistName Varchar(255))

  

   Insert Into #TempTable (CDId, CDTitle, ArtistName)

      Select CDId, CDTitle, ArtistName

      From CDs c, Artists a

      Where c.ArtistId = a.ArtistId

      Order By  ArtistName, CDTitle

 

   Select Seq, CDId, CDTitle, ArtistName

   From #TempTable

   Where Seq Between @StartKey And @StopKey

 

   Drop Table #TempTable

FIGURE 3: The GetCDListByPage2 stored procedure retrieves data sorted on a non-numeric key.

 

Once the table is created, an Insert statement with a Select clause is used to populate the temporary table. The Select statement extracts the data from the permanent tables and sorts it in the proper order. Notice that the Seq column is not included in the Insert statement because SQL Server generates its value automatically as the rows are inserted into the table.

 

Another Select statement is executed against the temporary table to retrieve the desired rows. Because the rows are in the proper order already, there is no need for an Order By clause to sort the data before it s returned.

 

Finally, after the data is returned, a Drop Table statement is run to delete the temporary table explicitly. Although this isn t absolutely necessary, it doesn t hurt and makes clear to anyone looking at the stored procedure that the temporary table merely exists during this stored procedure.

 

A variation of the GetCDListByPage2 stored procedure is shown in FIGURE 4. This stored procedure takes advantage of SQL Server 2000 s ability to define and use table variables. Because table variables are more efficient than temporary tables, the stored procedure should run a little faster. However, there isn t any other difference between the two approaches.

 

CREATE Procedure GetCDListByPage2A

   @StartKey As Int, @StopKey As Int

As

   Declare @TempTable Table(

      Seq Int Identity Primary Key,

      CDId Int,

      CDTitle Varchar(255),

      ArtistName Varchar(255))

 

   Insert Into @TempTable (CDId, CDTitle, ArtistName)

      Select CDId, CDTitle, ArtistName

      From CDs c, Artists a

      Where c.ArtistId = a.ArtistId

      Order By ArtistName, CDTitle

 

   Select Seq, CDId, CDTitle, ArtistName

   From @TempTable

   Where Seq Between @StartKey And @StopKey

FIGURE 4: The GetCDListByPage2A stored procedure uses a table variable in place of a temporary table.

 

Note that both of these stored procedures include an Order By clause, which forces the database server to sort all of the rows in the table. Depending on the size of your table, this may or may not be a problem. For small tables, this shouldn t be a problem. However, if you have a lot of rows, this approach may consume too many database resources to be practical. One way to address this problem is to include some additional conditions into the Where clause of the Select statement nested inside the Insert statement. The fewer records you retrieve, the better.

 

Paging with Cached Data

The DataGrid control is a very powerful tool for displaying data in a Web page. Among its many features is the ability to display the contents of a DataSet object one page at a time. This means you can let the DataGrid control handle all the paging details and greatly simplify your program. This approach results in a Web page with an output similar to the previous Web page (see FIGURE 5), but it also means the page works much differently.

 


FIGURE 5: Using the DataGrid control to handle the details about paging (CDPage3VB.aspx).

 

The HTML code associated with the DataGrid control is shown in FIGURE 6. In order for the DataGrid control to manage paging, you need to set the AllowPaging property to True. Then, you need to specify the number of rows to be displayed using the PageSize property. Finally, you need to specify in the OnPageIndexChanged property the event that will be fired each time the user switches pages.

 

    cellspacing="0" cellpadding="0"

    style='bordercolor="#C0C0C0" bgcolor="#FFFFC9"

    valign="top"' width="100%"

    AutoGenerateColumns="false"

    AllowPaging="true"

    PageSize="10"

    OnPageIndexChanged="DataGrid1_Paged"

    runat="server">

 

 Font-Bold="true" />

 Font-Bold="true" />

 

 DataField="ArtistName" />

 

FIGURE 6: Laying out the form containing the data grid (CDPage3VB.aspx).

 

For a DataGrid control to manage paging, you must load all the data into a DataSet object, not just the records currently displayed. This means that a relatively simple stored procedure like the following can be used to retrieve the data. Note that the stored procedure needs to sort the data in the order it should be displayed, although no other special processing is required:

 

CREATE Procedure GetCDListByPage3

As

   Select CDId, CDTitle, ArtistName

   From CDs c, Artists a

   Where c.ArtistId = a.ArtistId

   Order By ArtistName, CDTitle

 

The code needed to manage the DataGrid is spread over three different routines (see FIGURE 7). The DataGrid1_Paged event contains the code that will be executed when the user wishes to move to a different page. The location of the new page is stored in the event arguments associated with the DataGrid. Simply get the NewPageIndex property and save it in the DataGrid s CurrentPageIndex property. Then, the DataGrid needs to be repopulated with data, so a call to GetDataForGrid is made to get the data and rebind it to the grid.

 

Sub DataGrid1_Paged(sender As Object, _

 e As DataGridPageChangedEventArgs)

  DataGrid1.CurrentPageIndex = e.NewPageIndex

  GetDataForGrid()

End Sub

 

Sub GetDataForGrid()

  Dim ds As New DataSet

  ds = Cache("Music.CDList")

  Label1.Text = "Data was retrieved from cache."

 

  If IsNothing(ds) Then

    ds = GetDataFromDatabase()

    Cache.Insert("Music.CDList", ds, Nothing, _

     DateTime.Now.AddMinutes(15), TimeSpan.Zero)

    Label1.Text = "Data was retrieved from the database."

  End If

 

  If ds.Tables("CDList").Rows.Count > 0 Then

    DataGrid1.DataSource = ds.Tables("CDList").DefaultView

    DataGrid1.DataBind()

  End If

 

End Sub

 

Function GetDataFromDatabase() As DataSet

  Dim ds As New DataSet

  Dim conn As New SqlConnection(ConnectionStr)

  Dim adpt As New SqlDataAdapter

 

  adpt.SelectCommand = New _

   SqlCommand("GetCDListByPage3", conn)

  adpt.Fill(ds, "CDList")

  Return ds

End Function

FIGURE 7: The code that supports the DataGrid (CDPage3VB.aspx).

 

The GetDataForGrid subroutine creates a DataSet object containing the data to be paged and binds it to the data grid. Doing a full table scan plus a sort each time you want to display a page is expensive, so the Cache object is used. The Cache object allows you to store an object in the Web server s memory rather than recreate it each time you need it. Unlike the Application and Session objects, the Cache object is optimized to store transient data. This means that the object stored in the cache automatically will be removed when any of a rich set of conditions is reached. This makes the Cache object is ideal for storing a DataSet object that holds the information to be displayed in the DataGrid.

 

The Cache object allows you to associate a particular object with a key value, much like the Application and Session objects. You can create a new entry in the cache like this:

 

Cache("CacheKey") = ObjectToBeCached

 

You can return an item from the cache like this:

 

ObjectFromCache = Cache("CacheKey")

 

Even if you had previously stored an object in the cache, it may be removed for a variety of reasons, so you can t assume that the information you want is in the cache. You must check the object to see if the object is valid before attempting to use it. Nothing will be returned if the object doesn t exist in the cache.

 

Back in the GetDataForGrid subroutine, the DataSet object is extracted from the Cache object. If the DataSet is Nothing, then the GetDataFromDatabase function is called to get a fresh copy of the data. Then, the data is inserted into the cache using the Insert method. The DataSet object could have been inserted directly, but the Insert method is used so the DataSet will be removed from the cache automatically in 15 minutes. This ensures a recent copy of the data is available. Finally, the DataSet object is bound to the DataGrid and the results are displayed to the user.

 

Conclusion

Although paging through a database is highly desirable from a user s point of view, it is not always the right thing to do. It s relatively easy to page through a numerically ordered collection of data, such as the 100 top-selling CDs. However, paging through a set of data that has been sorted in other ways can consume a lot of database resources or Web server memory. Whether these resources are worth the price is up to you to decide.

 

The sample database and code is available for download. The database is packaged as a Jet (Access) database. Simply use Data Transformation Services to copy the tables from the Jet database to your SQL Server database. Then use Query Analyzer to add the stored procedures. Finally, copy the aspx and jpg files to your Web server and modify the information in the ConnectionStr constant to access your database, and you should be ready to run.

 

In my next column, I ll discuss how to create typed database objects to isolate your ASP.NET application from the physical database and the database-access methods. In the meantime, if you have questions about this article or have questions you would like to see me address in future articles, please send me an e-mail at mailto:[email protected]. I d love to hear from you.

 

The files referenced in this article are available for download.

 

Wayne S. Freeze is a full-time computer-book author with more than a dozen titles to his credit, including ASP.NET Database Programming Bible (Hungry Minds 2001), Windows Game Programming with Visual Basic and DirectX (QUE, 2001), and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds, 2000). He has nearly 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master s degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send him e-mail at mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.

 

Tell us what you think! Please send any comments about this article to [email protected]. Please include the article title and author.

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish