Paging Through Data Sources

Overview

Men acquire a particular quality by constantly acting a particular way.... You become just by performing just actions, temperate by performing temperate actions, brave by performing brave actions.-Aristotle

In real-world scenarios, pagination is an important issue when you have a lot of records to display. To preserve a good level of scalability and performance on the server, you might decide to show the records that form the key view of your application in equally sized pages. You would then want to create a pager bar that has a bunch of ad hoc buttons that allow users to navigate between pages. So with a data source, a sized view, and a pager bar, you are pretty much done; the DataGrid itself would do the job of paging the records.

As we saw in the previous chapter, setting up the DataGrid control to work in paging mode is easy and effective. First you enable data paging by setting the AllowPaging property to true. Next you decide the style for user-interface elements related to pagination. Internally, the DataGrid control takes care of extracting from the bound data source the records that fall in the range of the current page. When you work with large-sized data sources, the risk of taxing the Web server's memory too much is high. For mostly read-only data globally shared by all sessions, caching for pagination can be a feasible solution. However, the accuracy of this statement depends on the effective size of the data source. Caching a few dozen, or even hundreds, of records is much different than caching a few thousand!

If session-specific data is to be paged, binding and caching the entire data source should be avoided because the data would be loaded for each user, potentially degrading overall scalability. However, if you're determined to exploit the DataGrid's built-in paging capabilities without caching data somewhere and somehow, you inevitably experience the paradox of pagination. The DataGrid control requires you to load and bind the data source at each request. If caching is not used, you execute a query at each request. You could end up querying for 1000 records (if that is the size of the entire data source) to display only the 20 that fit in the requested page.

Custom Paging with DataGrid Controls

In situations in which you have to display session-specific or highly volatile data, you can exploit another feature of the DataGrid control—custom pagination. When custom pagination is enabled, the control does not implement any algorithm to page over a bound data source. The DataGrid still supplies the built-in infrastructure for paging (the pager bar, informational properties, and events), but it expects the programmer to set the DataSource property with only the records to be displayed for a given page.

When working in custom-paging mode, the DataGrid control does not locate and extract the records that fall into the given page. It simply assumes all the records currently associated with the DataSource property form the page. The advantage is that only the records that fit into the page are held in memory, resulting in a significant savings in the amount of system resources used. Because the records are obtained by querying the database, you always display up-to-date data, which is a strict requirement for databases that get stale relatively quickly. A less pleasant side effect of this approach is that, depending on the database management system (DBMS) you're using and the style of the pager you expect to provide, coming up with a page-based query command might not be easy. In this chapter, though, we'll focus on providing basic strategies for custom paging.

The layout of the code you need to write is not significantly different whether you use default or custom pagination. You have to provide the content of the page in both cases—the whole source for built-in pagination, and only the current page if custom paging is turned on. There are clear differences in how the two approaches work in terms of overall performance and scalability. Factors that determine which method to use are (in order of priority):

1. How often your data gets stale and needs to be updated
2. The scope of the data, be it session or application
3. The size of the overall data source to page through

The more static your data is, the more you can exploit caching, both data and page-output caching. (We'll cover page-output caching in Chapter 14.) The more dynamic your data is, the more you should try to minimize the amount of data being read and stored. In systems that need to be highly–scalable, support a variable, and have a normally high number of concurrent users (not all Web systems are like this), you should also have a middle tier that can be scaled out to mitigate the impact that data retrieval has on performance.

Let's see how to turn custom pagination on for ASP.NET applications and examine the complications raised in real-world implementations.

Enabling Custom Pagination

To enable custom pagination for ASP.NET DataGrid controls, you must ensure that both AllowPaging and AllowCustomPaging properties are set to true. This can be done declaratively through the ASP.NET page layout using the following code:

 AllowPaging="true"
 AllowCustomPaging="true"
 OnPageIndexChanged="PageIndexChanged">

Alternatively, you can programmatically set these properties to true during page loading or whenever it makes sense to your application:

void Page_Load(object sender, EventArgs e) {
 grid.AllowPaging = true;
 grid.AllowCustomPaging = true;
 PageIndexChanged += new DataGridPageChangedEventHandler(
 this.PageIndexChanged);
}

Setting the AllowPaging property to true tells the DataGrid control that the support for paging must be initialized (that is, the pager must be displayed). Setting the AllowCustomPaging property to true instructs the control not to page through the data source but to just read and display all the data items bound to the DataSource property.

In addition to enabling pagination, you must provide settings for the pager and the page size. This is the same as for normal pagination. The pager and the page size take default values, so it's not necessary to explicitly set them in your code—although you can if you don't like the standard settings. However, you must supply a valid handler for the PageIndexChanged event. This event is fired whenever the user clicks on a page button or in any way causes, even programmatically, the current page to change. The event handler to write is based on the DataGridPageChangedEventHandler delegate type. (For more information on default paging, see Chapter 6.)

Setting the Virtual Item Count

During the execution of the DataBind method, the DataGrid control extracts all the items from DataSource and copies them into the Items collection. This behavior is common to both paging scenarios. What differs in the two cases is the subset of data items extracted from DataSource. The DataGrid control determines the page count, dividing the total number of items by the page size. In the case of default pagination, the total number of items is given by the record count in the data source.

With custom pagination, on the other hand, the data source contains only the records that fit in the page being displayed. The total number of items to page through can't be inferred from any available data. For this reason, you must provide that number by using an ad hoc property—the VirtualItemCount property. The following code shows the typical way to retrieve a value to set the property. This sample procedure simply executes a SELECT COUNT query on the Northwind Products table.

public void SetVirtualItemCount() 
{
 // Set up the connection
 string strConn = "...";
 SqlConnection conn = new SqlConnection(strConn);

 // Set up the command
 string strCmd = "SELECT COUNT(*) FROM products"; 
 SqlCommand cmd = new SqlCommand(strCmd, conn);

 // Execute the command
 conn.Open();
 int nItemCount = (int) cmd.ExecuteScalar();
 conn.Close();

 // Set the grid's virtual item count
 grid.VirtualItemCount = nItemCount;
 return;
}

Note the use of the command's ExecuteScalar method to retrieve a single, scalar value from a query. The DataGrid control uses the virtual item count information to properly set up the pager bar. Setting the AllowCustomPaging and VirtualItemCount properties are two requirements for the DataGrid custom pagination.

Paging Using Key Values

With custom pagination, the trick is to figure out a (customized) way to page through records using SQL commands. Because the contents of each page are retrieved with an individual query, the command text must be designed to return a precise subset of the records that match certain criteria. Data paging means that you have a base query command but need to scroll through its resultset one page at a time. How can you force the DBMS to return only a particular portion of the selected records? Unfortunately, there's no official answer to this question, but a few best practices have been identified over the years. More important, these best practices—usually, smart SQL tricks—are database specific.

Important

Generally, we could say the difficulty of getting pages of records out of a query can be traced back to the nature and design of the SQL language. Within SQL, there's no explicit notion of the record position. Any record can be identified through the value of any of its fields by using the WHERE clause or server cursors and the FETCH statement. Specific DBMS systems, then, might add custom features to support position-based row identification. Oracle, for example, does that through the ROWNUM keyword; Microsoft SQL Server uses different tools, which aren't, functionally speaking, completely equivalent.

To start experimenting with custom paging, let's consider a simple, maybe even simplistic, case. The assumption we make here is not unrealistic, but it's too strong a requirement for most real-world cases. In addition, it has a few shortcomings that make it suitable only for read-only tables. Let's suppose the table to page through has a column (not necessarily a key column) with numeric values unfolded in regular succession. By regular succession, we mean that given an index, the corresponding value can be determined algorithmically, just like a mathematical succession.

Figure 7-1 shows the rows that form the Products table in the Northwind database. Focus on the productid column and its sequence of values.

Figure 7-1: A screen shot with some of the rows in the Northwind.Products table.

The productid column is made of integer, progressive values that start from 1. The regularity of the succession extends over the whole column and is never broken. In light of this, the value at row n is a function of a certain expression F:

value = F(n)

In particular, for the productid column the function is extremely simple and summarized by the following succession:

F(0) = 1
F(n+1) = F(n)+1

When the table provides a column with a regular succession of values, the following SQL code allows you to extract in a very flexible and generic way the rows that form a given page:

SELECT * FROM products WHERE 
productid >= F(PageSize*(PageIndex-1)+1) AND 
productid <= F(PageSize*PageIndex)

Because the size of the page is a fixed setting, the only variable parameter in the expression is the index of the page. The following listing shows a full application that pages through a list of products:

private SqlDataReader m_dataReader;
private string m_connString = 
 "DATABASE=Northwind;SERVER=localhost;UID=sa;";
private string m_cmdCount = "SELECT COUNT(*) FROM products";
private string m_cmdQuery = "SELECT productid AS 'ID', " + 
 "productname AS 'Product', quantityperunit AS 'Packaging' " + 
 "FROM products " +
 "WHERE productid >=@minProductID AND productid <=@maxProductID";
void Page_Load(object sender, EventArgs e) {
 if (!Page.IsPostBack)
 SetVirtualItemCount();
 BindData();
}

SqlDataReader CreateDataSource(int nPageIndex) {
 // page index is assumed to be 0-based
 int pageSize = grid.PageSize;
 int minProductID = pageSize * nPageIndex + 1;
 int maxProductID = pageSize * (nPageIndex + 1);

 // Set up the command
 SqlConnection conn = new SqlConnection(m_connString);
 SqlCommand cmd = new SqlCommand(m_cmdQuery, conn);
 cmd.Parameters.Add("@minProductID", SqlDbType.Int).Value = minProductID;
 cmd.Parameters.Add("@maxProductID", SqlDbType.Int).Value = maxProductID;

 // Execute the command through a global data reader
 conn.Open();
 m_dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 return m_dataReader;
}

void PageIndexChanged(object sender, DataGridPageChangedEventArgs e) {
 grid.CurrentPageIndex = e.NewPageIndex;
 BindData();
}

void BindData() {
 m_dataReader = CreateDataSource(grid.CurrentPageIndex);
 grid.DataSource = m_dataReader;
 grid.DataBind();
 m_dataReader.Close();
}

void SetVirtualItemCount() {
 SqlConnection conn = new SqlConnection(m_connString);
 SqlCommand cmd = new SqlCommand(m_cmdCount, conn);

 // Execute the command
 conn.Open();
 int nItemCount = (int) cmd.ExecuteScalar();
 conn.Close();

 grid.VirtualItemCount = nItemCount;
 return;
}

Figure 7-2 shows the custom paging application in action.

Figure 7-2: The grid exploits the characteristics of the productid column to page through records.

Notice a couple of things in Figure 7-2. The pager bar is displayed on top of the grid, and the check box allows you to switch between the pager styles. The code discussed so far works regardless of the paging mode you opt for.

Fixed-Size DataGrid Controls

The DataGrid control is rendered as an HTML table, so the actual size of the HTML table depends on a number of factors, including the font, length of the text, and number of items displayed. Especially when you're paging through a data source using a grid control, it can be annoying to have the body of the page move up or down a few pixels each time the height of the grid changes. Setting the Width property to a specified maximum number of pixels allows you to maintain a fixed width for the grid. The same trick doesn't work for the height. In this case, the drawback is that the final page of the grid (which normally contains a smaller number of items) is stretched to occupy the fixed height. A possible workaround is padding the data source with empty rows prior to binding to the grid. An even better approach, one that doesn't require any programmatic work, is to wrap the DataGrid in a fixed-height HTML table with an empty row at the bottom:

The unique cell of the bottom row must have a 100 percent height so that it will automatically occupy any space left by the topmost grid. Combine this structure with some ad hoc visual settings and you'll have the grid paging always within the boundaries of the parent table. In particular, you might want to use a borderless DataGrid and define border settings on the table. Also, you should use the same background color for the table and the grid.

Shortcomings of the Key-Based Approach

There are pros and cons with this column-based approach. It is simple to code and effective in returning a compact resultset that is immediately bindable and requires no further preparation work. Column updates do not affect the functionality, and row insertion is a nonissue too, as long as you guarantee and preserve the regularity of the succession. On the downside, though, I should mention that this technique works only with primarily read-only, look-up tables.

Deletions are one type of operation that can really nullify the functionality of this technique. The deletion of a row creates a hole in the succession, which then breaks the arithmetic rule set up to identify the first record in the page. You can try to work around the issue by resorting to nonidentity, custom-made columns that can be programmatically updated. In other words, you must perform the deletion through a stored procedure that first executes the deletion and then reorders the index column. To make this approach more complex, though, consider that in some cases the stored procedure (or, in general, the sequence of statements involved) needs to be embedded in a transaction.

In the remainder of the chapter, we'll analyze more powerful approaches that work in a larger number of cases. We'll discuss the solutions based on the paging mode you want to support—for example, next/previous navigation and random access to pages. However, no matter which approach you choose to control paging, don't forget to set the virtual item count.

Note

The DataGrid is the only ASP.NET control that provides a built-in infrastructure for pagination. The DataList is the control that most closely resembles the DataGrid in terms of functionality. The key difference between the two is just pagination. However, if you use custom paging and don't mind using external link buttons to navigate, obtaining a pageable DataList is not a hard task at all. We'll examine the DataList in Chapter 9 and review how to extend it to support pagination.

Next Previous Navigation

When we want to move to the next or previous page, a more general query command can be used to leverage any column that has unique values but which might not form a regular succession. The idea is that you cache the first and last key values in the currently displayed page and select the previous (or next) page based on that. If the page size is set to 10, moving to the previous page entails selecting the first 10 records whose key value is less than the cached value. If you move to the next page, the first 10 records with a key greater than the cached last key are selected.

Defining the SQL Code

With the NextPrev pager mode, you can view only pages close to the currently displayed one—that is, pages immediately previous to or following the page you are viewing. This constraint enables us to set up ad hoc SQL code that works regardless of the values in the key column. To sum it up, the pivot column doesn't have to be the key column—it only has to contain unique values. For the SQL code to work, you need to cache the value on this key column for the first and last items in the page. The values require persistent storage that survives across page requests. These key values can be stored in the view state:

ViewState["FirstKey"] = grid.DataKeys[0];
ViewState["LastKey"] = grid.DataKeys[grid.Items.Count-1];

This code stores in the view state the minimum and maximum key values in the page. The key values are retrieved from the DataKeys collection, and the name of the key column is set using the DataKeyField property. Prior to executing the query command, the value of the key is read back from the view state, as shown here:

// going to page back
int productID = Convert.ToInt32(ViewState["FirstKey"]);
// going to page ahead
int productID = Convert.ToInt32(ViewState["LastKey"]);

Let's have a look at the SQL code to move to the previous record and to move to the next record. Later in the "Adding First and Last Buttons" section, we'll discuss how to move to the first record and move to the last record.

Moving to the Previous Record

When the user clicks to view the previous page of a data source, the grid has cached the key values for the first and last records displayed in the current page. Assuming a default page size of 10, the records that fit in the previous page are the last 10 whose key value is less than the first key of the current page.

In SQL Server, the TOP n clause allows you to select the first n records that match. Unfortunately, though, there's no equivalent clause that lets you select the last n matching records. To obtain the same result, you must resort to a little trick and use a virtual table. You use the following Transact-SQL (T-SQL) code. The key column is productid.

SELECT * FROM 
(SELECT TOP 10 productid, productname, quantityperunit 
 FROM products WHERE productid <@productID ORDER BY productid DESC) AS t 
ORDER BY productid

Before we discuss the query any further, a few general points need to be addressed. The code here is not standard SQL but is written in the SQL dialect that SQL Server supports—the Transact-SQL language. It can't be considered a general solution, as it exploits features of recent versions of SQL Server and might not be supported by other DBMS. Unlike the SQL code we considered for the application in Figure 7-2, the code shown here can't be used with just any database server. The features that make it to some extent unique and SQL Server–specific are the TOP n clause and the use of subqueries.

The T-SQL code used to query for the previous page is based on two nested queries. The innermost query selects the first 10 records that have a key value less than the first key displayed in the current page. Because the table is queried in descending order, the top 10 records are actually the last 10 but are listed from the highest to the lowest key. We name this in-memory table AS t and, through the outermost query, select all the records in it ordered by the key column. This final operation returns the last 10 records sorted in the natural ascending order.

Important

Very few databases support the TOP n clause in the SELECT statement. The TOP clause was originally introduced in Microsoft Access and then imported into SQL Server beginning with version 7. It has no counterpart in SQL Server version 6.5 nor in any version of Oracle. Oracle, though, has an alternative mechanism that is in some aspects even more powerful. (We'll discuss this feature more in the "Selecting Records in Oracle" section later in the chapter.) Informix SQL does provide a TOP-like behavior through the FIRST clause. Generally, the SET ROWCOUNT statement, although a bit less flexible, can be used as a rougher counterpart when the TOP clause is not supported. The main difference is that the TOP clause is command specific, whereas the SET ROWCOUNT statement is a global environment setting.

Note

Many T-SQL statements that include subqueries can sometimes be formulated as joins. Other statements, like ours here, can be posed only with subqueries. In T-SQL, there is usually no relevant performance difference between a statement that includes a subquery and a functionally equivalent command that does not. In some cases, though, a join gets slightly better performance. What's important to note for our purposes, though, is that subqueries can be significantly faster than server cursors and temporary tables. Server cursors, in particular, should always be your last resort.

Moving to the Next Record

Extracting the records to display in the next page is a bit easier and faster once you know the last key in the current page. In this case, the TOP n clause works just fine and without the need of other tricks. The following T-SQL code simply selects the top 10 records with a key value greater than the last key (the productid column) in the page.

SELECT TOP 10 productid, productname, quantityperunit 
FROM products WHERE productid >@productID

Both the commands we've examined so far, for moving to the previous and next records, use a parameter named @productID. This parameter is set with the value retrieved from the view state for the first or last key according to the previous or next command to execute.

Setting Up the Next/Previous Navigation

The following code shows the initialization step of the sample application that demonstrates next/previous pagination:

void Page_Load(object sender, EventArgs e) {
 if (!Page.IsPostBack)
 {
 SetVirtualItemCount();
 ViewState["LastKey"] = 0;
 ViewState["FirstKey"] = 0;
 m_cmdText = m_cmdQueryNext;
 BindData();
 }
}

The default value of the view-state entries is 0, and the records that fit in the first page are retrieved as a next-page command with a LastKey value of 0.

The command to execute is decided by looking at which link the user clicked to page back or forth. The ASP.NET page is fired by the PageIndexChanged event when the user clicks to page back or forth. The event doesn't provide explicit information about the direction of the paging. However, you can easily figure it out by comparing the values of the CurrentPageIndex property and the NewPageIndex event data member.

if (grid.CurrentPageIndex > e.NewPageIndex)
 // moving forward
else
 // moving back

At the time the PageIndexChanged fires, the new page index has not yet been set. Moreover, you have to explicitly set it in the event handler.

Knowing whether you're moving to the next or previous record is important in this case because, based on it, we decide which query command to run. Another way to obtain that information is by exploiting the services of the ItemCommand event. The sample code discussed here implements this technique because it's more general and will work even with future extensions.

When the user clicks to change pages, regardless of whether it is to page back or forth, the DataGrid fires an ItemCommand event in which the CommandName member is set to Page. The CommandArgument member, on the other hand, is accordingly set to Next or Prev. All these keywords don't have to be addressed as literals—predefined constants should be used instead. The following code shows the ItemCommand event handler:

void ItemCommand(object sender, DataGridCommandEventArgs e)
{
 switch(e.CommandArgument.ToString())
 {
 case DataGrid.NextPageCommandArgument:
 m_direction = GridNavigation.Next;
 break;
 case DataGrid.PrevPageCommandArgument:
 m_direction = GridNavigation.Previous;
 break;
 }
}

The code stores in an application global variable a value that indicates the direction of the paging. The GridNavigation values belong to a user-defined type that has been defined mostly for clarity:

public enum GridNavigation : int
{
 First = -2,
 Previous = -1,
 Next = 1,
 Last = 2
}

Notice that the enumeration also includes command names to access the first and last pages of the data source. I'll discuss this feature in the next section, "Adding First and Last Buttons." The following listing illustrates the mechanism that allows the sample application to fetch records using the SQL commands discussed earlier. Note that the code employs a data reader (instead of a slower DataTable) to bind data to the DataGrid:

void BindData() {
 m_dataReader = CreateDataSource();
 grid.DataSource = m_dataReader;
 grid.DataBind();
 m_dataReader.Close();

 // Configure for the view state
 ViewState["FirstKey"] = grid.DataKeys[0];
 ViewState["LastKey"] = grid.DataKeys[grid.Items.Count-1];
}

SqlDataReader CreateDataSource() {
 // page size is assumed to be 10 
 int productID = 0;
 string cmdText = "";
 switch(m_direction)
 {
 case GridNavigation.Previous:
 productID = Convert.ToInt32(ViewState["FirstKey"]);
 cmdText = m_cmdQueryPrev;
 break;
 case GridNavigation.Next:
 productID = Convert.ToInt32(ViewState["LastKey"]);
 cmdText = m_cmdQueryNext;
 break;
 }
 return ExecuteCommand(cmdText, productID);
}

SqlDataReader ExecuteCommand(string cmdText, int productID) {
 SqlConnection conn = new SqlConnection(m_connString);
 SqlCommand cmd = new SqlCommand(cmdText, conn);
 cmd.Parameters.Add("@productID", SqlDbType.Int).Value = productID;

 // Execute the command through a global data reader
 conn.Open();
 m_dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 return m_dataReader;
}

As mentioned in Chapter 5, you can populate a list-bound control like the DataGrid using a data reader object. The data reader works connected to the data source. For this reason, you must close it after calling the control's DataBind method. The data reader is marked with the CloseConnection flag so that closing the reader would automatically close the underlying connection. The CreateDataSource method examines the determined direction and sets the command text to execute and the key value to use as a parameter.

Selecting Records in Oracle

Speaking of Oracle databases, you should note that named parameters in Oracle must be prefixed with a colon (:). In SQL Server, you precede the name of parameters by using the @. symbolThe SQL language supported by Oracle databases doesn't provide a facility like the TOP n clause for SELECT statements. However, particularly for data paging, the Oracle database provides an even more powerful feature than TOP—the ROWNUM operator.

The ROWNUM operator returns the ordinal position of a row in a resultset. In light of this, and given a page size of 10, moving to a page requires the following, surprisingly simple, query:

SELECT * FROM table WHERE
ROWNUM > (PageIndex*(PageSize-1)+1 AND ROWNUM > (PageIndex*PageSize)

The ROWNUM operator has a drawback that should be discussed. An Oracle database assigns the unique index before sorting the result set. So if you have a query that includes an ORDER BY clause, the sequence of ROWNUM values is not ordered as the clause indicates. To fix things up, you must use an outermost query that does not require sorting.

Adding First and Last Buttons

A next/previous navigation model can be made even richer by using a pair of extra links for the first and last pages of the data source. In this case, though, you can't count on any support from the DataGrid control. You must modify the pager bar on the fly to accommodate for the extra buttons, add a handler for the Click event, and update the view.

The following code shows how you can modify the pager bar by adding a text label displaying the number of the current page (as mentioned in Chapter 6) and the links for the first and last pages. The key procedure is the ItemCreated event handler:

void ItemCreated(object sender, DataGridItemEventArgs e) {
 if (e.Item.ItemType == ListItemType.Pager) {
 if (grid.PagerStyle.Mode == PagerMode.NextPrev) {
 TableCell pager = e.Item.Cells[0];
 AddPageLabel(pager);
 AddLinkFirstPage(pager);
 AddLinkLastPage(pager);
 }
 }
}

Creating Link Buttons Dynamically

The First and Last buttons are to be added as link buttons or labels, depending on the page index. If the link can be clicked to go to the first or last page, you render the button as a link; otherwise, you render it as a label or a disabled link. Because the standard pager renders disabled buttons as labels, we'll use this approach here too. Let's consider adding the First button. No significant changes are expected for the Last button.

The First button must be disabled only if the current page is the first. Put another way, the First button must be rendered as a link button only if the Previous button is a link. The Previous button is the first in the Controls collection. You check its type and then create a LinkButton or Label control accordingly:

void AddLinkFirstPage(TableCell pager)
{
 if(pager.Controls[0] is LinkButton) {
 LinkButton btnFirst = new LinkButton();
 btnFirst.Font.Name = grid.PagerStyle.Font.Name;
 btnFirst.Font.Size = grid.PagerStyle.Font.Size;
 btnFirst.ForeColor = grid.PagerStyle.ForeColor;
 btnFirst.Text = "<<";
 if (btnFirst.Font.Name == "webdings")
 btnFirst.Text = "7";
 btnFirst.Click += new EventHandler(GoToFirstPage);
 pager.Controls.AddAt(0, btnFirst);
 }
 else
 {
 Label btnFirst = new Label();
 btnFirst.Font.Name = grid.PagerStyle.Font.Name;
 btnFirst.Font.Size = grid.PagerStyle.Font.Size;
 btnFirst.ForeColor = grid.PagerStyle.ForeColor;
 btnFirst.Text = "<<";
 if (btnFirst.Font.Name == "webdings")
 btnFirst.Text = "7";
 pager.Controls.AddAt(0, btnFirst);
 }
}

You ensure that the font and color used for the First button are identical to those used for other buttons in the pager. You read the default settings from the DataGrid's PagerStyle object. A nice feature you can add is setting the caption of the button to a Webdings symbol if Webdings is the current pager font. The value of 7 corresponds to the 7 symbol. For the Last page, you use the value 8, which corresponds to the 8 glyph.

You add the control to the Controls collection at a specified position, as shown in Figure 7-3.

Figure 7-3: The new pager bar with a pair of First and Last buttons.

The command to select the records of the first page is straightforward and consists of a pretty simple SELECT command:

SELECT TOP 10 productid, productname, quantityperunit FROM products

Getting the records of the last page is a bit more involved. The problem has two facets. Because users can click on the Last button at any time, we can't assume we know the last key of the last page. Knowing this value would make the query trivial; unfortunately, we do not know it. The second issue to consider is that we don't know exactly how many records would fit in the last page, as that depends on the page size and the size of the data source. The following query would select the last n records in the data source with a key value greater than the specified value:

SELECT * FROM (
 SELECT TOP {0} productid, productname, quantityperunit 
 FROM products 
 WHERE productid >@productID ORDER BY productid DESC) 
 AS t ORDER BY productid

The key value we use does not determine the resultset, but the higher value we use the smaller subset of records the query would process. The {0} placeholder in the query is dynamically replaced with the computed number of records in the last page. Here's the necessary code:

SqlDataReader CreateDataSource() { 
 switch(m_direction) {
 
 case GridNavigation.Last:
 productID = Convert.ToInt32(ViewState["LastKey"]);
 int nRem = grid.VirtualItemCount % grid.PageSize;
 nRem = (nRem == 0 ?grid.PageSize :nRem);

 // Replaces the placeholder to include the effective
 // number of records in the last page
 cmdText = String.Format(m_cmdQueryLast, nRem);
 break;
 }
}

The nonzero modulus obtained by dividing the data source size by the page size is the number of rows to retrieve for the last page. If the modulus is zero, the last page is full and the number of rows to retrieve matches the page size.

Setting the Click Handler

What happens when the user clicks on the First or Last button? The action taken depends on the registered Click event handler:

btnFirst.Click += new EventHandler(GoToFirstPage);
btnLast.Click += new EventHandler(GoToLastPage);

In Microsoft Visual Basic .NET, you register handlers using the AddHandler keyword. The structure of both handlers is similar and refers to the code built in the PageIndexChanged event handler.

void GoToFirstPage(object sender, EventArgs e) {
 m_direction = GridNavigation.First;
 GoToPage(sender, 0);
}
void GoToLastPage(object sender, EventArgs e) {
 m_direction = GridNavigation.Last;
 GoToPage(sender, grid.PageCount-1);
}
void GoToPage(object sender, int nPageIndex) {
 // page number is 0-based
 DataGridPageChangedEventArgs evPage;
 evPage = new DataGridPageChangedEventArgs(sender, nPageIndex);
 PageIndexChanged(sender, evPage);
}

As the code illustrates, going to the first or last page is resolved in terms of a page transition accomplished through the standard pager buttons. The GoToPage method invokes the user's defined code for the PageIndexChanged event. The code assumes that such an event handler is defined—a reasonable assumption as we're speaking of code for paging.

Figure 7-4 shows the final page of the grid. Note that the height of the grid seems to be constant. As we explained in the sidebar "Fixed-Size DataGrid Controls," this is only a trick performed using a wrapper HTML table element.

Figure 7-4: The final page of the grid.

Adding ToolTips to the Pager

As shown in Figure 7-3, the pager buttons include ToolTips. Each ToolTip indicates the number of the page the button would move you to. The index of the next or previous page is a fundamental piece of information if no other feedback is provided about the current page. With next/previous navigation, users don't know what page they're currently viewing, so ToolTips help considerably. In the sample code discussed here, the current page index is displayed through a label, so ToolTips aren't really necessary.

You can add ToolTips on the fly to pager buttons as well as header cells. In both cases, all you need to do is write a handler for the ItemCreated event. The following code shows how to proceed with the Previous button—the second button in the pager:

if (e.Item.ItemType == ListItemType.Pager) {
 if (grid.PagerStyle.Mode == PagerMode.NextPrev) { 
 TableCell pager = e.Item.Cells[0];
 WebControl ctl = (WebControl) pager.Controls[1]);
 ctl.ToolTip = String.Format("Go to page #{0}", 
 grid.CurrentPageIndex);
 }

Note that the CurrentPageIndex property returns 0-based values, whereas the displayed page indexes are 1-based. For this reason, the 1-based index of the previous page coincides with the CurrentPageIndex property. Similarly, the index of the next page is CurrentPageIndex plus 2.

Random Page Navigation

Let's examine the tricks of data pagination when the NumericPages pager mode is used. In this case, we can't rely on any features and values in the previously viewed page. Data access must be independent from external parameters and only a function of the page index and page size. The ROWNUM statement of Oracle databases is ideal for setting up a random-access paging mechanism. Unfortunately, other databases, including SQL Server, don't provide for that. In this section, we'll discuss and put to work an alternative trick based entirely on the SQL language.

A page that uses a grid in NumericPages pager mode has a much simpler layout (and involves less code) than a page based on the NextPrev pager mode. For one thing, a unique SQL command is needed and used regardless of the page to be accessed. Second, the direction of the navigation is unimportant and doesn't require ad hoc handling. Let's have a closer look at the necessary SQL statement.

Selecting a Specified Page of Records

To select from a SQL Server database only the records that fit into the specified page (identified by an index), a rather complex and nested query is necessary. The resultset is selected in three steps. First you get the smallest resultset that includes the desired records. Next you get rid of the additional records, and then you sort the remaining rows properly.

Suppose that, given a page size of 10, you want to obtain the records of page 3 according to a particular order. The last 10 rows of the first 30 are the rows you are looking for. How can you get them? Let's see what happens with the Products table in the Northwind database:

SELECT TOP 10 * FROM
 (SELECT TOP 30 * FROM products ORDER BY productid) AS t1
ORDER BY productid DESC

This nested query selects the correct subset of records but in the reverse order. It takes the first 10 rows out of the first 30 in descending order. A third query must be placed to wrap this code with the sole goal of sorting the previous resultset:

SELECT * FROM
 (SELECT TOP 10 * FROM
 (SELECT TOP 30 * FROM products ORDER BY productid) AS t1
 ORDER BY productid DESC) AS t2
ORDER BY productid

The following listing shows how this statement is turned into .NET executable code. Replace the numeric values with a pair of {n} placeholders, and run the following code:

string cmdText = String.Format(m_cmdQuery, 
 grid.PageSize, grid.PageSize*pageIndex);
SqlConnection conn = new SqlConnection(m_connString);
SqlCommand cmd = new SqlCommand(cmdText, conn);
conn.Open();
m_dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
conn.Close();

One problem raised with this code is that the last page always shows up with 10 records—the regular records plus the last in the previous page. The culprit of this is the SELECT TOP 10 outer query. A simple way to work around this issue is by associating the TOP clause with the actual number of rows in the final page:

int recsInLastPage = grid.VirtualItemCount % grid.PageSize;
if (recsInLastPage == 0)
 recsInLastPage = grid.PageSize;

int sizeOfPage = grid.PageSize;
if (pageIndex == grid.PageCount)
 sizeOfPage = recsInLastPage;
string cmdText = String.Format(m_cmdQuery, 
 sizeOfPage, grid.PageSize*pageIndex);

The code discussed so far includes two sorting operations that are not lightweight ones. However, indexes are just the right tool to leverage to make the query run faster. Using a clustered index on the ORDER BY field (productid, in the preceding example) speeds the query up immensely; using a clustered primary key index would speed things up even more—an order of magnitude faster.

For completeness, you might also want to consider the following, functionally equivalent, version of the query:

SELECT * FROM 
 (SELECT TOP 30 * FROM products ORDER BY productid) AS t1
WHERE productid NOT IN 
 (SELECT TOP 20 productid FROM products ORDER BY productid)

The idea is that you first select the minimum number of records that will contain the desired subset, and then you run an outer query in which you filter out the records that don't belong to the page. For example, if page #3 is to be retrieved, you discard the records of the first two pages. This second query also doesn't suffer from the last-page drawback we had to fix earlier. In terms of raw performance, the second query runs statistically slower than the first except in one circumstance—when you have additional criteria set on the table. If the main query, which was SELECT * FROM products in the previous example, contains a WHERE clause, especially for larger tables, the second query tends to run about 15 percent faster. The break-up point seems to be around 150,000 records. As a disclaimer, I want to recommend that you don't take this statistical analysis for pure gold but instead roll your own before you make a decision.

Note

The TOP clause doesn't accept parameters, so it's hard with this trick to use code from within a stored procedure. However, the SQL Server query engine can still do a good job reusing the execution plans of the subqueries.

Customizing the Structure of the Pager

When working in NumericPages pager mode, the pager bar is made of a single table cell populated with an alternate sequence of links and blank literals. One link is actually a nonclickable label control. By default, the links and the label have simple numeric captions, which makes the pager bar difficult to read.

Unfortunately, the DataGrid control doesn't support a format string for the links in the numeric pager bar. To customize the text of the buttons, you can only write the usual ItemCreated handler:

void ItemCreated(object sender, DataGridItemEventArgs e) { if (e.Item.ItemType == ListItemType.Pager) { if (grid.PagerStyle.Mode != PagerMode.NumericPages) return; TableCell pager = (TableCell) e.Item.Cells[0]; for (int i=0; i

The modified grid is shown in Figure 7-5.

Figure 7-5: A DataGrid control with a customized numeric pager bar.

In some cases, a particularly large data source might lead to a too-long list of links for the real estate of the pager bar. The pager's PageButtonCount property lets you control how many links are to be displayed. By default, PageButtonCount is set to 10. If extra buttons are required, the DataGrid adjusts for extra pagination buttons, as shown in Figure 7-6.

Figure 7-6: A pager bar with ellipsis buttons when too many links are required.

Building a Completely Customized Personal Pager

Although the DataGrid doesn't provide a built-in mechanism to plug in a custom pager, you can always replace the standard pager with your own. You can hide the pager by turning off its Visible attribute. However, nothing really prevents you from displaying both the ordinary pager and a personal pager:

The personal pager is any custom toolbar appended at the bottom of the grid. The DataGrid and your personal pager are two rows of the same table, as in the following schema:

...

personal pager goes here

The following code shows how to transform the pager in a numeric text box with a button to jump to the specified page:

The code that actually moves to the specified page is as follows:

void GoToPage(object sender, EventArgs e) {
 int pageIndex = Convert.ToInt32(txtGoto.Text)-1;
 if (pageIndex >=0) {
 grid.CurrentPageIndex = pageIndex;
 BindData();
 }
}

The GoToPage method doesn't include any validation logic. You can either validate the content of the text box programmatically or resort to a range validator control. (See Chapter 3.)

The lower bound of the range can be set declaratively to 1. As for the upper bound, you have to set it dynamically—for example, in the BindData method.

gotoValidator.MaximumValue = grid.PageCount.ToString();

Figure 7-7 shows a grid control with a personal pager bar.

Figure 7-7: A DataGrid control with a personal pager along with the standard pager. The two work side by side without any conflicts.

Logical Navigation

So the DataGrid control—unique among the ASP.NET data-bound controls—provides two basic types of paging, which differ in the way in which the page content is retrieved. With default paging, you bind the control to the whole data set and then make it responsible for selecting the subset of records that fits in each displayed page. Custom paging, on the other hand, is based on the assumption that the programmer puts into the control's data source only the records that should be displayed in the current page. Does this cover all the possible needs you might have when it comes to planning navigation through a data set? The answer is debatable.

The types of paging you get for free from the DataGrid control certainly do not cover all the possible real-world scenarios you might be facing. On the other hand, by exploiting the DataGrid custom-paging infrastructure, you can easily build new paging engines that fit into your application's architecture.

To top off this chapter, we'll demonstrate how to use the DataGrid custom-paging mechanism to set up a sort of logical data navigation—that is, a navigation engine that displays variable-length pages that are selected based on a logical condition rather than a physical position.

Changing the Pager s Perspective

In the DataGrid control, all the pages except the last one are the same size, as specified through the PageSize property. You select each page to display from the links of the variable-length pager bar. The pager bar contains as many links as there are pages to display. With the help of a practical example, let's see how this model deals with a scenario in which you have a fixed number of pages, each with a variable length.

Suppose you have a query to select all the orders issued by customers in a given year. Using the paging approach discussed so far, you would have a long list of (possibly numbered) pages displayed to the users. Are you really sure that your users would love to scroll through a year's worth of orders by page? How could they orientate themselves in the results once you show them a menu of, say, 40 meaningless page indexes? Wouldn't it be much better if you grouped orders, say, by month or by company? In the traditional way of paging, you keep the page size constant but provide a variable-length pager bar. Logical navigation does just the opposite—it keeps the pager bar size constant but accepts pages of any length (almost any length, actually).

We'll discuss the underpinnings of logical navigation by grouping data by months. However, the mechanism we'll use here is rather general and applies to a number of everyday scenarios: grouping customers or contacts by initials, sales by year or quarters, and bills by weeks or days.

Is the DataGrid Appropriate for the Task?

Although we can successfully employ the DataGrid control for this type of navigation—a special case of custom paging—there's an upper bound we must set for the control to work. The limitation is in the maximum number of rows that can be displayed per page. This number can be any value—yet we have to set it and configure other parameters of the grid accordingly. (We'll discuss this further in the next section.)

Is there another control we can use instead? The DataList control is one of the few alternatives we can rely on. As we'll more clearly see in Chapter 9, the DataList is a data-bound list of rows, each sequentially displayed according to an ASP.NET template. The DataList doesn't pose any limitation on the number of items you can display in a page, but, on the other hand, this happens only because the DataList doesn't have the notion of paging and doesn't provide any built-in facilities.

In summary, you can use both the DataGrid and the DataList for the task, but neither of them is the perfect tool for a logical navigation of data. Both would work, but both require some adjustments.

Configuring the DataGrid

A DataGrid used to implement a logical navigation of data requires some ad hoc settings. First you might want to use a topmost pager bar because the list of items can be long and a topmost pager can simplify movements. For the same reason, a double pager bar would also be more useful. Next you should opt for a NumericPages pager mode and then write an ItemCreated handler to customize the text displayed. For example, you can turn 1 into January, 5 into May, and so forth.

Finally you should carefully choose the values for the DataGrid's PageSize and VirtualItemCount properties and for the PagerStyle's PageButtonCount property. These properties are key to forcing the DataGrid to support logical data navigation. By design, the control can't show more items than the number of PageSize states. You can set PageSize with any value. Let's assume we don't need to display more than 100 rows per page. The PageButtonCount property determines how many buttons are displayed in the pager bar. By default, this value is set to 10. Because we plan to have month names on the pager bar, we must raise this value to 12. Subsequently, the virtual item count is a computed value—1200 (that is, PageSize * PageButtonCount).

In this way, the grid will always display through 12 pages, each of which can have no more than 100 rows.

Binding the Data Source

As mentioned, logical navigation is a special case of custom paging. Whenever the user clicks to view a new page, an ad hoc data source is created based on the page index. You can use the page index directly to obtain the data source or, more generally, use it to reference another data structure (for example, a collection or an array) with more specific information. In the sample scenario we're considering now, the page index would suffice, as it directly represents the index of a month. Let's see how to modify the pager bar to reflect months rather than indexes.

Modifying the Pager Bar

To modify the pager bar of a DataGrid, you write an ItemCreated handler. The handler will process all nonliteral controls in the pager bar and transform the default text—a 1-based number—into a month name. To increase flexibility, the name of the month is generated through an intermediate DateTime object corresponding to the first day of that month in the current year. This date is then formatted into the month name. The ItemCreated handler is shown here:

void ItemCreated(object sender, DataGridItemEventArgs e) { if (e.Item.ItemType == ListItemType.Pager && grid.PagerStyle.Mode == PagerMode.NumericPages) { TableCell pager = (TableCell) e.Item.Controls[0]; for(int i=0; i

As mentioned earlier, a pager bar built in NumericPages pager mode is made of a sequence of link buttons and a single label, all interspersed with blank literal controls. To skip over literals, we use a step of 2 in the for loop. When the i-th control in the pager is a Label control, its value represents the currently selected month. To differentiate it even further, we use a distinct group of visual settings. In particular, we use the same font and colors of the grid's header and draw the month name in its full extension instead of abbreviating it. The final effect is shown in Figure 7-8, and the tab-style look is free!

Figure 7-8: A customized grid that displays data grouped by months rather than by pages.

Selecting the Page's Rows

Each page in the grid displays the resultset generated by the following query with two parameters:

SELECT o.orderid, o.orderdate, c.companyname 
FROM orders AS o 
INNER JOIN customers AS c ON o.customerid=c.customerid 
WHERE Year(orderdate)=@TheYear AND Month(orderdate)=@TheMonth

The year is selected only once per page through the drop-down list. The month is determined from the page index. The method that creates the data source is shown here:

DataTable CreateDataSource(int nYear, int nMonth) {
 SqlConnection conn;
 conn = new SqlConnection(m_connString);
 SqlCommand cmd = new SqlCommand(m_cmdQuery, conn);
 cmd.Parameters.Add("@TheYear", SqlDbType.Int).Value = nYear;
 cmd.Parameters.Add("@TheMonth", SqlDbType.Int).Value = nMonth;

 SqlDataAdapter da = new SqlDataAdapter(cmd);
 DataTable dt = new DataTable();
 da.Fill(dt);
 return dt;
}

In this case, the data source is fully identified by the page index. Generally, the page index is only the starting point to get any information useful for obtaining the final data source. For example, suppose you're paging through the customers of your company. In this case, a user interface in which pages are rendered as groups of initials (for example, A-D, E-H, and so forth) produces much more user-friendly results. To obtain that, only minimal changes to the discussed infrastructure are needed.

To start out, you define an array of information that will be accessed through the page index. In this case, you define each page with a string of initials and store the resulting array in the view state. (You can use any structure as long as it is serializable.)

void Page_Load(object sender, EventArgs e) {
 if (!IsPostBack) {
 ArrayList a = new ArrayList();
 a.Add("a-b-c-d");
 a.Add("e-f-g-h");
 a.Add("i-j-k-l");
 a.Add("m-n-o");
 a.Add("p-q-r");
 a.Add("s-t-u");
 a.Add("v-w-x-y-z");
 ViewState["PageMap"] = a;
 grid.VirtualItemCount = a.Count*100; //100 is max page size
 LoadData();
 }
}

The query that creates the data source adds a LIKE criterion to the WHERE clause for each letter in the selected string of initials:

private DataTable CreateDataSource() {
 // Retrieve the paging info
 ArrayList info = (ArrayList) ViewState["PageMap"];
 string strInitials = info[grid.CurrentPageIndex].ToString();

 // Prepare the SQL command to execute
 StringBuilder sb = new StringBuilder("");
 sb.Append(m_cmdQuery);

 // Append a WHERE clause for each initial in the comma-separated string
 string[] a = strInitials.Split('-');
 for(int i=0; i

Figure 7-9 shows the final results.

Figure 7-9: A customized grid that displays logically grouped data.

Conclusion

No matter what type of application you are building—whether it's a Web application, Microsoft Windows application, or Web service—you can rarely afford downloading and caching the whole data source you're expected to display. Sometimes, test environments might lead you to believe such a solution works great and is the preferable one. Even more often, test environments lie and mislead you. The size of the data source does matter, and the more the application must be scaled the more size does matter.

The DataGrid control has two ways of paging. In one case, you bind the control to the whole data source and let it go. For Web applications, this approach is twice as risky. For one thing, you must be sure you tested the application with sample databases of the proper order of magnitude. Also, a Web application loses its state across two successive requests and the DataGrid control doesn't employ countermeasures to avoid that. As a result, each time the page is requested, the data source must be restored. In summary, either you can afford caching the whole data source or opt for the second type of paging—custom paging. In addition, while computing the size of the data source, don't forget to multiply it by a factor if the data to cache is session specific!

Custom paging is a memory-effective technique. Only the data needed at a particular time is loaded into memory. If this approach sounds familiar—perhaps familiar even to the point of dj vu—you're more than right. Custom paging is a common best practice for paging—integrated here with an ASP.NET server control and exposed by the framework. You should consider using custom paging for everything except small and global data with a low level of volatility.

A second problem to consider for data paging is the structure and the content of each page. What's a page in your particular context? Is it simply a sequence of data rows listed in any order? Or is a page a group of related records that respond to some criteria? In this chapter, we showed how to customize paging to make a DataGrid display rows grouped by months or name. SQL commands are key elements to setting up functional and effective pagination. Paging through the resultset returned by a query is not a feature the SQL language supports natively as part of the SQL-92 standard. For this reason, you should look carefully at the features of the SQL language for the particular DBMS you're using.

In the next chapter, we'll look at how to make the data-access layer more effective and data-driven applications more user-friendly. We'll consider compound queries and scalability, but also reporting and master/detail views.

Resources

•  
• Data Paging: Display Your Data One Page at a Time (http://www.aspnetpro.com/features/2002/05/asp200205wf_f/asp200205wf_f.asp)
•  
• Customizing Pagination (http://www.aspnetpro.com/features/2002/03/asp200203de_f/asp200203de_f.asp)
•  
• Paging Results with ASP.NET's PagedDataSource (http://www.sitepoint.com/article/921)
•  
• Paging with Repeater control in ASP.NET (http://www.charon.co.uk/content.asp?CategoryID=4&ArticleID=21)
•  
• Design a Custom Paging Solution (http://www.fawcette.com/dotnetmag/2002_10/magazine/columns/architecting)
•