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: 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. 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. 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. 
FIGURE 2: The stored
procedure in action within the SQL
Server Query Analyzer. 
FIGURE 3: A sample
application that uses the stored procedure in FIGURE 1 to fetch pages of
records.
