Customizing Pagination

Data Bound

Technologies: Data Binding | DataGrid Control | Pagination | ADO.NET

Languages: C#

 

Customizing Pagination

Sorting Out the DataGrid Control s Pagination

 

By Dino Esposito

 

Most software applications involve data access and reporting in one way or another. In particular, Web applications must perform these two functions, so Web programmers are always on the alert looking for any automated method of associating rows of data with GUI elements. The answer to this growing demand for automatic binding between data sources and graphical elements is .NET databound controls. The goal of this column is to help you understand this aspect of .NET data access and reporting.

 

In real-world scenarios, pagination is an important issue when you have a large number of records to display. To preserve a good level of scalability on the server, you may decide to show the records that form the key view of your application in equally sized pages. Then you can supply a series of buttons to move back and forth between them. With a sized view and a pager bar you are pretty much finished.

 

If your target is ASP.NET and you plan to use the DataGrid control, you are even closer to solving the issue of submitting thousands of records to users. The DataGrid control, in fact, provides a basic, semi-automatic infrastructure for data pagination. Setting up the DataGrid control to work in paging mode is easy and effective. First, enable pagination by setting the AllowPaging property to True. Next, decide the style for user interface elements related to pagination. These are the pager bar with the buttons that let you navigate through pages and the page itself that can be adapted to the specified number of items and columns.

 

Internally, the DataGrid control takes care of extracting all and only those records that fall in the range of the current page. Specify the whole data source to page through by setting the control s DataSource property to make the DataGrid responsible for all that relates to pagination. In other words, pagination is extremely easy and effective and, more importantly, is enabled in a totally declarative way. The difficulty with DataGrid-based pagination has to do with the extra usage of the Web server s memory, especially when you work with large-sized data sources.

 

Storing and Retrieving Items

The key principle that makes datagrids work is that they always assume the whole data source is available on the server. The programmer is responsible for this; the DataGrid control, for performance reasons, does not cache the contents of the DataSource property. The expected size of the data source can easily exceed any reasonable threshold set to preserve the health of the Web server s memory.

 

Any time the user selects a new page for display, a postback event is fired. This originates a roundtrip to the server and the necessity for the programmer to restore the datagrid state by re-assigning the DataSource property. This is needed because the DataGrid control does not save the data source contents to its auto-restoring view state.

 

As a programmer, you can decide whether to cache the grid s data source locally to speed up the data retrieval. According to the required level of scalability, you could extract the records as a DataSet and store it to Session, Cache, or on a disk file. An alternative is to re-execute the query against the database each time the data is needed. As a side note, consider that as long as you can keep the number of total users under control, you can easily tune up the hardware and software for the system and afford using the Session object. The Session object, in fact, still remains the fastest way to cache data on the Web server and, with ASP.NET, it works transparently also on Web farms and Web garden architectures.

 

In terms of raw performance, default grid pagination makes particular sense if you cache records on the Web server. Otherwise you would take the concrete risk of querying for thousands of records all those that form the view and displaying only, say, 10 of them, the actual size of the viewable page. If there is no cache available, the most relevant part of the fetched records has been downloaded for nothing! Worse yet, they will be continuously downloaded each time a new grid s page is requested.

 

Custom Pagination

The DataGrid control also provides for a form of user-defined pagination that works around this issue. In this case, the control does not implement any algorithm to page between a specified group of items. You only tell it about the number of items you virtually plan to page through and ensure it finds the items it needs to lay out the page. As a programmer, you are responsible for making the contents of each single displayable page available. The DataGrid does not take care of locating and extracting the records that fall into the given page, but simply assumes that all the records currently associated with the DataSource property form the current page.

 

The layout of the code that you have to write is not significantly different with default and 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. The user-defined pagination requires much less memory as it keeps in memory only the records strictly necessary for the current page. From this perspective, the level of scalability you can reach is unmatched. Let s see how to turn it on for ASP.NET applications and examine the troubles raised in real-world implementation.

 

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:

 

 

AllowPaging="True"

AllowCustomPaging="True"

OnPageIndexChanged="PageIndexChanged">

 

and programmatically during the 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);

}

 

AllowCustomPaging set to True lets the grid know how to process the contents of 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 is not strictly necessary to set them explicitly 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 any 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. It carries the data defined as part of the DataGridPageChangedEventArgs data structure. This comprises two data members of particular importance: NewPageIndex and CommandSource. The former contains the index of the new page to set as the consequence of the user action. The latter points to the button object that raised the event, namely the object, if any, that the user clicked on to change the page.

 

To ensure that the datagrid correctly displays the items that belong to the new page, write the handler like this:

 

void PageIndexChanged(Object sender,

DataGridPageChangedEventArgs e)

{

  grid.CurrentPageIndex = e.NewPageIndex;

  // TO DO: refresh the data

  // source and re-bind

}

 

How you retrieve the data source is strictly application-dependent and has to do with any caching policies that you may have implemented. No matter how you get the data source collection, you must then assign it to the DataSource property of the grid and call the DataBind method to completely refresh the user interface. During the execution of DataBind, the grid control extracts all and only the items that it needs from the associated data source object to display them according to the pagination policy and the current page. Those items are then copied into the Items collection and made available for further processing. All this is not specific to custom pagination, but applies to any supported form of datagrid pagination.

 

Two programming aspects that differentiate custom and standard pagination are the items count and the use of the Items collection. The DataGrid control determines the page count by dividing the total number of items by the page size. In case of default pagination, the total number of items is given by the size of the data object associated with the DataSource property. Custom pagination, instead, assumes that you provide the VirtualItemCount property with the total number of items you plan to move through. Since the DataGrid control provides the pager bar functionality anyway, it utilizes this information to set it up properly. In addition, make sure that the Items collection is filled at any time with all and only the records for the current page.

 

In summary, to enable an effective custom pagination on an ASP.NET DataGrid control, you must:

  • Allow pagination by setting AllowPaging to True and make it custom by setting AllowCustomPaging to True.
  • Register an event handler for the PageIndexChanged event. This handler will switch to the new page index and ensure that the records for the page are safely loaded.
  • Set the total number of items you plan to page through. This number is not the page size but the page size multiplied by the total number of pages you foresee having.
  • Provide the DataSource property only with the few records that the current page is expected to display. This is the key factor that provides high scalability, but it is also the major hurdle to overcome in a number of real-world scenarios.

 

Fetching Pages of Records

Suppose that you have 3,000 records to display in pages of 20 records each. Custom pagination requires you to set the value of VirtualItemCount to 3000 and PageSize to 20. The value of PageCount is inferred and set to 150. The code for the PageIndexChanged event handler may look like this:

 

void PageIndexChanged(Object sender,

DataGridPageChangedEventArgs e)

{

  grid.CurrentPageIndex = e.NewPageIndex;

  int n = grid.CurrentPageIndex;

  grid.DataSource = CreateDataSource(n);

  grid.DataBind();

}

 

This code gets the current page index, extracts the subset of records from the page, and refreshes the view. The problem is how to extract all and only the records that fit in page n. Unfortunately, there is no predefined rule or consolidated programming approach to implement this. It is now and for the foreseeable future a strictly application-specific technique. In the majority of cases, you will use a SQL-based DBMS to fetch records out. SQL does not provide any facility for pagination. Worse yet, within the SQL language there is no explicit notion of the record position. Any record can be identified only through the value of any of its fields using the WHERE clause.

 

You have very limited support from the language when it comes to skipping over pages of records to directly reach the record, which is supposed to be under the previous conditions the first of a given page.

 

The support you get from SQL is reduced to using server cursors (the FETCH statement) within a stored procedure and to the TOP n clause in the SELECT statement.

 

Fetching means that you select a bunch of records and then scroll over them, selecting only those that match some conditions that you cannot express through the WHERE clause. For example, the stored procedure shown in FIGURE 1 executes a query and associates it with a T-SQL scroll cursor. Next, it moves to the absolute position of the record that is expected to be the first in the page. A loop to load the remainder of records that form the current page completes the procedure. The first argument of the stored procedure is the page you want to display. The second argument is the size of the page.

 

CREATE PROCEDURE FetchEmployeesByPage

(@PageNumber smallint, @PageSize smallint)

AS

 

-- @PageNumber, page to fetch

-- @PageSize, records that fit into one page

 

-- internal variables

  DECLARE @MyIndex smallint

  DECLARE @FirstRecPos smallint

 

-- sets the position of the first record to

-- retrieve in the page

  SET @FirstRecPos = (@PageNumber-1) * @PageSize + 1

 

-- cursor associated with the query string

  DECLARE employees_cursor SCROLL CURSOR FOR

  SELECT employeeid,firstname,lastname FROM employees

  ORDER BY lastname

 

-- opens the cursor

  OPEN employees_cursor

 

-- fetches the first record in the page

  FETCH ABSOLUTE @FirstRecPos FROM employees_cursor

 

-- loops through the cursor to pick up the

-- other records in the page

  SET @MyIndex = 1

  WHILE @@FETCH_STATUS = 0 AND @MyIndex < @PageSize

  BEGIN

     FETCH NEXT FROM employees_cursor

     SET @MyIndex = @MyIndex + 1

  END

 

-- clean up

  CLOSE employees_cursor

  DEALLOCATE employees_cursor

GO

FIGURE 1: A stored procedure that utilizes T-SQL server cursors to fetch records by position.

 

Using the fetch cursor is effective because it lets you jump to the first record you need and pick up only the records you need. However, on average, it is not particularly fast and returns a multiple recordset. Each record ends up forming its own one-record return table. FIGURE 2 shows this when the stored procedure runs within the SQL Server Query Analyzer.

 


FIGURE 2: The stored procedure in action within the SQL Server Query Analyzer.

 

To obtain a really usable ADO.NET object, you need to further process the result set on the client to build up a valid and all-encompassing DataTable object. You can do this by looping through the various tables returned with the DataSet or the DataReader and add the record to a new data container. FIGURE 3 demonstrates the output of a sample application that utilizes the stored procedure in FIGURE 1 to fetch pages of records.

 


FIGURE 3: A sample application that uses the stored procedure in FIGURE 1 to fetch pages of records.

 

Using SELECT TOP

The T-SQL language supported by SQL Server 7.0 and higher supplies a special clause, TOP, to select only the first n records out of a query. You use TOP like this:

 

SELECT TOP 5 * FROM Employees

 

As a result, you get back only the first five records selected by the query. Used in conjunction with ad hoc database columns, the TOP clause turns out to be extremely useful in implementing custom pagination. If your database has an identity column that contains values determined by a regular succession, you can use this column to query for the first record in each page. For example, suppose that your identity column has values incremented by 1. The first row would take 1, the second 2, and so forth. A query string like this does just what you need:

 

SELECT TOP n * FROM Products WHERE ProductId > (n-1)*4

 

The ProductId column is the identity column. The value of n is the desired size of the page and 4 is the number of the page to display. Assuming an incremental series of values in the ProductId column, (n-1)*PageNumber is the magic formula that returns the ID of the first record in the specified page. To make sure that you take only the records that fit in that page, you can use the TOP clause. TOP is a non-standard SQL clause. It works with SQL Server 7.0 and higher and the Jet engine, but not with Oracle or Sybase. The Informix DBMS supports a similar clause but with the name FIRST instead of TOP.

 

There are pros and cons with this approach. It returns a regular recordset that is immediately usable and does not need any preparation work as in the previous case. However, this is a technique that can be heavily affected by deletions. It works great as long as you ensure that the regular succession of values is persistent across updates. This can be obtained only in one way: by executing deletions through a stored procedure that takes care of restoring the regular succession of values. Otherwise, holes in the succession could easily break the simple arithmetic rule set up to identify the first record in the page. Identity columns cannot be programmatically updated, but you work around this by using a non-identity, custom-made column that is easily updateable.

 

Whatever approach you choose to control paging, don t forget to set the virtual item count. For example, you obtain the total number of records in the table by executing the code shown in FIGURE 4.

 

public void SetVirtualItemCount()

{  

  // Set up the connection

  String strConn =

   "DATABASE=Northwind;SERVER=localhost;UID=sa;PWD=;";

  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;

}

FIGURE 4: Setting the VirtualItemCount property for a DataGrid control.

 

Notice the role of ExecuteScalar, which is a method exposed by the SqlCommand and OleDbCommand classes that executes a query and returns the scalar value stored in the first row and column of the resulting record set.

 

Custom Pagination in Action

The key to effective custom pagination is the algorithm you choose to select the records for each page. While you can easily figure out a couple of rather general techniques to create custom pages of data, this remains an application-specific feature. You should exploit any peculiarity of your tables and architecture to make it even more effective and optimized, for example through forms of Web server or database server caching. The code provided through the Beta 2 documentation and in this article should get you started.

 

The files referenced in this article are available for download.

 

Dino Esposito (mailto:[email protected]) is a trainer and consultant for Wintellect (http://www.wintellect.com) where he manages the ADO.NET class. Dino writes the Cutting Edge column for MSDN Magazine and Diving Into Data Access for MSDN Voices. He is the author of Building Web Solutions with ASP.NET and ADO.NET from Microsoft Press. Dino is also the cofounder of http://www.VB2TheMax.com.

 

 

 

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