Friday, January 30, 2009

Paging in ASP.NET using NHiberate

NHibernate, the object-relational mapping (ORM) framework for .NET, supports custom pagination for collections. It provides a potential alternative to the built-in paging mechanism and native support found in ASP.NET GridView web controls. NHibernate exposes in the API for IQuery and ICriteria two methods, SetFirstResult and SetMaxResult, that can be used to enable paging:

Collections are pageable by using the IQuery interface with a filter:

IQuery q = s.CreateFilter( collection, "" ); // the trivial filter
q.setFirstResult(PageSize * pageNumber);
IList page = q.List();

Adding Paging to the Base DAO

The design and architecture of my project, where NHibernate style pagination shall be introduced, was deeply influenced by Billy McCafferty's NHibernate Best Practices with ASP.NET resulting in the proliferation of Data Access Objects (DAO) throughout the guts of the application. Each DAO maps one-to-one to a single matching table in the database.

For example, a 'Task' table would have a corresponding 'TaskDao' in the data access layer (DAL) of the application. All of these DAOs inherit from the same base class 'GenericNHibernateDao' responsible for containing commonly shared code including managing NHibernate sessions and providing generic methods for summoning a specific persisted instance by ID and for saving/deleting an existing instance.

The initial step to implement paging was adding the following members to the aforementioned base class 'GenericNHibernateDao':

public abstract class GenericNHibernateDao : IGenericDao
public int PageSize
get { return _pageSize; } set { _pageSize = value; }

public int PageNumber
get { return _pageNumber; } set { _pageNumber = value ; }

protected int GetFirstResultPosition()
return _pageSize * (_pageNumber - 1);

protected void SetPagingFor(IQuery query)

protected void SetPagingFor(ICriteria criteria)

other non-paging related members...

The property 'PageSize' gets/sets the number of instances expected to be displayed on a web page for a specific strongly typed collection. Essentially, it handles how many rows to return for a embedded control (such as a GridView) on the page. This property is intended to be internally consumed by NHibernate's 'SetMaxResults' method belonging to IQuery or ICriteria. For example:

IQuery query = Session.CreateQuery();

'PageNumber' specifies the set of multiple instances to be returned and displayed on a web page as identified and grouped by a page sequence numeric value (This is the equivalent of 'PageIndex' property for GridViews). For example, if you had a total of five pages worth of data rows then displaying the second page would require setting the page number value to '2' (e.g. _dao.PageNumber = 2). Just as with 'PageSize', this property is intended to be used by the method 'GetFirstResultPosition' as will be explained next.

The protected method 'GetFirstResultPosition' calculates the actual row at which to start paging based on the values provided by 'PageSize' and 'PageNumber'. This method's return value is expected to be passed to NHibernate's 'SetFirstResult' method, once again, part of IQuery or ICriteria. For example:

IQuery query = Session.CreateQuery();

The overloaded method named 'SetPagingFor' performs the actual paging functionality via the 'SetFirstResult' and 'SetMaxResult' methods of IQuery and ICriteria. (As an aside, the use of IQuery to build and execute HQL is much more common on this project in comparison to the almost non-existent use of ICriteria).

The base class 'GenericNHibernateDao' was also modified to have one of its existing methods 'GetAll' call 'SetPagingFor'. (The 'GetAll' method simply returns a list of all strongly type objects in the database without any specified criteria or filtering):

// GenericNHibernateDao class
public IList<T> GetAll()
ICriteria criteria = Session.CreateCriteria(persitentType);
SetPagingFor(criteria); // this is newly added!
return criteria.List();

As it shall be made clear later, this new line of code will optionally provide paging functionality when 'GetAll' is requested, if needed, but not required per se.

Applying Paging Functionality in the DAOs

Now that the base class 'GenericNHibernateDao' has been updated to manage paging, any of its derived DAO classes are instantly equipped to perform paging themselves. To actually invoke the paging functionality for any of the DAOs, simply set the appropriate values for page size and page number as shown in this example for the 'TaskDao' class:

// In context (such as a Presenter or Controller class
// part of an MVP/MVC applied framework)
TaskDao _taskDao = new TaskDao();
_taskDao.PageSize = 20;
_taskDao.PageNumber = 5;
IList<TaskDao> list = _taskDao.GetAll();

Without paging, 'GetAll' would have returned something in the neighborhood of 100 or so rows. With paging, the returned list will instead be only 20 rows starting at row (i.e. position) # 80. While a hundred rows might not sound too substantial, larger sets of data can have a more noticeable effect on your application's day-to-day operations if your table contains tens or hundreds of thousands of rows. Your performance will be progressively impacted as your application scales with more data.

On the other hand, NHibernate's paging will significantly decrease the size of your result set. This is in stark contrast to the default behavior of the existing paging available in any GridView control. If this native functionality of the control is used, it will return all rows from the database and then page them in memory. As mentioned earlier, this can lead to slower performance as your data grows. More on this later.

If, for any reason, paging is not essential for a particular web page in possession of a control bound to a strongly typed list (for example,a very small static list of data) then setting the page size and number properties is not required at all. Simply call 'GetAll' by itself disregarding the 'PageSize' and 'PageNumber' properties and the DAO should return all rows found in the associated database table. What makes this possible is that the default values for those two paging properties are formally declared in the DAO base class to behave as expected for "non-pageable" collections:

public abstract class GenericNHibernateDao : IGenericDao
protected int _pageSize = -1; // default for unlimited page size
protected int _pageNumber = 1; // default for first item in collection

// more members...

Typically, a DAO might have other custom methods that return more narrowly focused (i.e. filtered) lists of typed objects than what 'GetAll' offers. For these other DAO methods, the same pattern can be followed by adding the one line of code calling 'SetPagingFor'. For example, the 'TaskDao' might have a method that returns tasks that were completed in 2006 excluding any other tasks not done within that same year:

// TaskDao class
public IList<TaskDao> GetTasksCompletedIn2006()
IQuery query = Session.CreateQuery(
"some HQL statement that filters tasks by 2006...");
SetPagingFor(query); // newly added!
return query.List();

It is generally good practice not to pass the values for page size and number directly via the parameter list for any of these custom filtered data access methods. A few reasons to avoid this: (a) it can quickly clutter the intent of the method, and (b) it would prevent the paging functionality from being optional and, as a result, become less flexible, less reusable, and more cumbersome to work with.

Consequently, while it would be tempting to write the method's signature as such:

IList<TaskDao> list = _taskDao.GetAllTasksWithSubtasks(
param1, param2, param3, ..., paramN, pageSize, pageNumber);

It is preferable to do the following instead:

_taskDao.PageSize = 20;
_taskDao.PageNumber = 5;
IList<TaskDao> list = _taskDao.GetAllTasksWithSubtasks(
param1, param2, param3, ..., paramN);

The Downside of NHibernate Paging with ASP.NET Controls

As indicated earlier, one weakness of NHibernate's paging when combined with ASP.NET's GridView control involves some loss of "out-of-the-box" functionality. Under more conventional circumstances, when a collection of objects are bound to a GridView control, one of the built-in paging features of that control is to automatically render on the web page the navigation hyperlinks for the pages. For example, you might see following below your control:

" 1 2 3 ... 10 "

The GridView's default behavior assumes that all data bound directly to its DataSource can be paged as long as the number of items of that data is greater than its PageSize property value. Hence, if that condition is met, the control will slice up and present the data as appropriate.

Conversely, this is not true when using NHibernate style pagination. When binding to an NHibernate paged list method, the GridView's PageSize value will usually be set to the same size as the paged data list, a mere subset of the total data found in the database (or data source). Behind the scenes, the PageIndex property of the GridView will reset to zero because the number of items bound to its DataSource is less than or equal to the PageSize. Therefore, the GridView is under the impression that the data it receives is not pageable and, in turn, disables any paging features, unaware that more items do indeed exist but were just not provided at that moment. The paging features lost include not just immobilizing navigation links but also removing the availability of the event handlers linked with changing the page index.

Without the convenience of auto-generating navigation links, two options emerge that might help to produce the same desired behavior:

  1. Inherit from the GridView control and attempt to confirm whether or not if any paging methods can be overridden some how or in some way
  2. Create a custom, reusable user control to implement the navigation features

Initially, the easier path was taken by developing a very rudimentary and simple implementation of option # 2. This entailed providing in extremely basic custom controls functionality for navigating between pages using homemade "Previous" and "Next" buttons. Currently, it is not implemented as a shareable user control nor is it able to display the pages counts. I intend on exploring option # 1 a bit more in the event that option # 2 evolves into something more elaborate and unwieldy. Until then, it is a work in progress.

A third option does exist involving the possible use of the ObjectDataSource control. However, that strategy can lead down a less than desirable path for the following reasons:

  1. loss of control of how the data access is managed
  2. ease of maintainability diminishes if any widespread changes were to emerge in the future within areas of the application relying on paging
  3. disrupts and conflicts with how the MCP/MVC methodology is currently applied on the project
  4. increased difficulties in writing and running reliable automated unit tests

All things considered, despite some trade offs and a little bit of work, leveraging NHibernate's ability to do paging can be an area that could contribute significantly in optimizing and improving the performance of a data-intensive web application.


sneal said...

Yeah, it's almost never a good idea to return all rows without some sort of criteria and/or paging.

The whole paging thing would be very easy to create using an MVC framework, but you could also create a reusable component for that like this

The 1:1 DAO to domain object would be better replaced by a few aggregate roots and repositories; It sounds like you essentially have a table row gateway pattern.

Ray Vega said...

Much agreed about using an MVC framework. Things become difficult whenever you try to do something that does not conform to how ASP.NET WebForms normally behaves.

Brian Wilkins said...

Yes, but how do you determine the total page numbers? Do you divide the total records by records you want to view to get the page amount?

sneal said...

Brian, you have to run a separate count query to get the total number of non-paged rows.

Ray Vega said...

@sneal- That link looks familiar. :-)

@brian- What sneal suggests is your best option.