Brief Introduction to Web Applications

Paging

Few companies' catalogs can fit on a single Web page. Say you set up a search engine for your product catalog and a hundred items fit the search criteria that the user specified. Rather than provide links to all of those products on the resulting Web page, you'll probably want to break up the results into a series of pages and display the contents of only the first page.

Displaying just the first page is generally simple. But how do you supply functionality to allow the user to move to the next page of the result set, or to move to a specific page?

ASP.NET and ADO.NET offer features that can help you serve up the results of your queries a page at a time. Let's look at those features now.

Paging Features of the Web DataGrid

The Web DataGrid includes features that make it extremely easy to display the results of a query a page at a time. The DataGrid control includes properties such as AllowPaging and PagerStyle that you can set programmatically to control how and where you display paging information for the data to which the DataGrid is bound. Figure 14-2 shows an example of a Web page that was built using a bound DataGrid and its paging features.

Figure 14-2

A sample Web page that uses the DataGrid's paging features

For developers building Web applications with Visual Studio .NET, the simplest way to set these properties is to use the Property Builder for the Web DataGrid. To invoke this builder, right-click a DataGrid on a Web Form and select Property Builder from the resulting context menu. Then select the Paging option on the left side of the builder, and you'll see the options displayed in Figure 14-3.

Figure 14-3

Setting paging properties of the DataGrid using the Property Builder

The AllowPaging Property

Setting the AllowPaging property to True tells the DataGrid to build links that will allow the user to jump from one page of the result set to the next. Once you've set this property, the DataGrid will automatically create page links along with the results when you bind the control to your data source. You can choose to display Next and Previous buttons or numeric links that allow the user to jump to a specific page.

When the user moves to a new page, the DataGrid control's PageIndexChanged event will fire. You can use this event to determine which page of data the user has selected and set the DataGrid control's CurrentPageIndex property accordingly.

The AllowCustomPaging and VirtualItemCount Properties

Let's say that you know how to retrieve just the contents of the desired page, but you still want to use the DataGrid control's paging features to build the links to other pages in the result set. If you set the DataGrid control's AllowCustomPaging property to True, you can then set the DataGrid control's VirtualItemCount property to the total number of rows in the result set. The DataGrid will then build the page links based on the VirtualItemCount property rather than on the number of rows in the data source.

The following code snippet retrieves just the first 10 rows of a query into a DataReader but sets other properties of the DataGrid to create the links to the other pages of results.

Visual Basic .NET

gridResults.AllowPaging = True gridResults.AllowCustomPaging = True gridResults.CurrentPageIndex = 0 gridResults.PageSize = 10 gridResults.PagerStyle.Mode = PagerMode.NumericPages gridResults.PagerStyle.Position = PagerPosition.TopAndBottom Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" Dim cnNorthwind As New OleDbConnection(strConn) strSQL = "SELECT COUNT(CustomerID) FROM Customers" Dim cmdFetchRowCount As New OleDbCommand(strSQL, cnNorthwind) strSQL = "SELECT TOP 10 CustomerID, CompanyName, ContactName, Country " & _          "FROM Customers" Dim cmdFetchOnePage As New OleDbCommand(strSQL, cnNorthwind) cnNorthwind.Open() gridResults.VirtualItemCount = cmdFetchRowCount.ExecuteScalar() Dim rdrOnePage As OleDbDataReader = cmdFetchOnePage.ExecuteReader() gridResults.DataSource = rdrOnePage gridResults.DataBind() rdrOnePage.Close() cnNorthwind.Close()

Visual C# .NET

gridResults.AllowPaging = true; gridResults.AllowCustomPaging = true; gridResults.CurrentPageIndex = 0; gridResults.PageSize = 10; gridResults.PagerStyle.Mode = PagerMode.NumericPages; gridResults.PagerStyle.Position = PagerPosition.TopAndBottom; string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; OleDbConnection cnNorthwind = new OleDbConnection(strConn); strSQL = "SELECT COUNT(CustomerID) FROM Customers"; OleDbCommand cmdFetchRowCount = new OleDbCommand(strSQL, cnNorthwind); strSQL = "SELECT TOP 10 CustomerID, CompanyName, ContactName, Country " +          "FROM Customers"; OleDbCommand cmdFetchOnePage = new OleDbCommand(strSQL, cnNorthwind); cnNorthwind.Open(); gridResults.VirtualItemCount =                       Convert.ToInt32(cmdFetchRowCount.ExecuteScalar()); OleDbDataReader rdrOnePage = cmdFetchOnePage.ExecuteReader(); gridResults.DataSource = rdrOnePage; gridResults.DataBind(); rdrOnePage.Close(); cnNorthwind.Close();

The query that we used in this code snippet is simple. It uses the TOP clause to fetch just the first 10 rows for the query. That syntax is great if you want to retrieve the first page of data, but how do you retrieve the contents of other pages in the result set?

Paging Features of the DataAdapter Fill Method

In Chapter 5, you learned about the features of the DataAdapter object. You might remember that the DataAdapter object's Fill method is overloaded and that one of the signatures lets you retrieve a subset of the results returned by the DataAdapter. In this particular method, the second parameter controls how many rows you skip before you start fetching data and the third parameter controls the maximum number of rows to retrieve.

Let's say you want to display only 10 rows per page. So, to fetch just the rows for the fifth page, you would skip the first 40 rows and fetch the next 10. The following code would retrieve that fifth page of rows into a DataSet:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Provider=SQLOLEDB;Data Source=(local)\NetSDK;" & _           "Initial Catalog=Northwind;Trusted_Connection=Yes;" strSQL = "SELECT CustomerID, CompanyName, ContactName, Country " & _          "FROM Customers" Dim da As New OleDbDataAdapter(strSQL, strConn) Dim ds As New DataSet() da.Fill(ds, 40, 10, "Customers")

Visual C# .NET

string strConn, strSQL; strConn = "Provider=SQLOLEDB;Data Source=(local)\\NetSDK;" +           "Initial Catalog=Northwind;Trusted_Connection=Yes;"; strSQL = "SELECT CustomerID, CompanyName, ContactName, Country " +          "FROM Customers"; OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); DataSet ds = new DataSet(); da.Fill(ds, 40, 10, "Customers");

While this code is very simple, there's a major drawback to the approach. In this example, you're still asking the database to return data for all rows in the table. You're still paying the performance penalty of fetching those 40 rows even though the DataAdapter isn't adding those rows to the DataSet.

Building Queries That Return a Page of Data

SQL Server and Access databases support the TOP clause in a query. You can use the TOP clause to return just the first n rows from a query. So, you could use the following query to return just the first 10 rows from the Customers table, ordered by the Country and CustomerID columns:

SELECT TOP 10 CustomerID, CompanyName, ContactName, Country     FROM Customers     ORDER BY Country, CustomerID

If you want to break the results into pages, you can use this syntax to return just the rows for that particular page. For example, if you want to return the rows for the fifth page, you would want to retrieve rows 41-50 from the Customers table.

SELECT TOP 10 CustomerID, CompanyName, ContactName, Country     FROM Customers WHERE CustomerID NOT IN     (SELECT TOP 40 CustomerID FROM Customers ORDER BY Country, CustomerID)     ORDER BY Country, CustomerID

This query includes a subquery that locates the first 40 rows ordered by the Country and CustomerID columns and then looks for the first 10 rows that do not appear in that subquery, again ordered by the Country and CustomerID columns. Here's a more generic way to write the query:

SELECT TOP PageSize Column1, Column2, ... FROM MyTable     WHERE KeyColumn NOT IN     (SELECT TOP RowsToSkip KeyColumn FROM MyTable ORDER BY SortOrder)     ORDER BY SortOrder

Both SQL Server and Access support both the TOP and NOT IN clauses. However, these clauses aren't supported by all databases, so this isn't a completely universal solution. Oracle, for example, does not support the TOP clause.

While Oracle does not support the TOP clause, it does support a somewhat analogous feature: rownum. Oracle numbers the rows that the query returns and you can use rownum to retrieve just the first n rows that the query returns. However, Oracle generates the row numbers before applying the sort order for the query, so it's a little tricky to use rownum to return a page of data for a query where you want to use a sort order. With a little cajoling and a couple subqueries, you can use the rownum feature to return a specific page of data for a query that uses a sort order. The following Oracle query returns the same page (rows 41-50) of the Customers table:

SELECT CustomerID, CompanyName, ContactName, Country FROM     (SELECT CustomerID, CompanyName, ContactName, Country,              rownum AS Row_Num FROM         (SELECT CustomerID, CompanyName, ContactName, Country             FROM Customers ORDER BY Country, CustomerID)         WHERE rownum <= 10)     WHERE ROW_NUM > 40

	I don't claim to be an Oracle guru. There's probably an easier way to build such queries for Oracle.

The PagingOptions Sample

On the companion CD, you'll find a sample Web application called PagingOptions. This Web application uses various techniques to allow the user to page through the results of the query. Each example displays data and page links using the DataGrid. One page in the sample application relies on the DataGrid to control paging. There are other pages that cache data in ViewState, Session, and the database. There's a page that relies on subqueries to retrieve a single page worth of data from the database, one that stores the results of the initial query in the database, and one that retrieves.