Implementing Paging
It is always a good idea to restrict the number of rows returned with a query. Currently, this
implementation returns all the rows in the INVENTORY table. It works well enough now because the
table contains fewer than 100 rows. If this were implemented in a scenario in which the result set
were in the thousands or hundreds of thousands, it would not perform as well.
The IQuery API provides two methods for implementing paging: SetMaxResults() and
SetFirstResult(). The SetMaxResults() method accepts an integer as a parameter that
defines the maximum number of rows that should be returned for the query. This is often
referred to as Top N.
NOTE You don’t need to worry about implementing paging differently between,
for example, Microsoft SQL Server and Oracle. SQL Server uses Top N, whereas
Oracle uses rownum to restrict results. Code it once using NHibernate and it
works without modification whether the database is changed from SQL Server
to Oracle or vice versa.
The SetFirstResult() method also accepts an integer as a parameter. As the name of the method
implies, it sets the first row returned from the database. Therefore, for the first query, the value
should be 0, with future queries being n plus the value sent to the SetMaxResults() method.
NOTE When using IQuery for paging, the first value passed to the
SetFirstResult() should be 0. However, the ICriteria SetFirstResult()
method expects a 1.
The steps required to add paging functionality to the GuitarStore WPF program are as follows:
1. Create a method that accepts both a max result and a first result as parameters and uses
them to return the expected result.
2. Program a method to retrieve the total number of rows on the INVENTORY table.
3. Create and use a PopulateDataGrid() method to populate the DataGrid, rather than using
the Window_Loaded() method.
4. Add paging buttons and logic to the GuitarStore WPF program.
CHAPTER 2: Using HQL
62
The first action taken to implement paging in the GuitarStore WPF program is to create a new
GetPagedInventory() method. This method should accept a max result and a first result as
parameters. Add the code shown in Listing 2-9 to the NHibernateInventory class.
LISTING 2-9: HQL paging method
public IList GetPagedInventory(int MaxResult, int FirstResult)
{
string hqlQuery = “select Builder, Model, Price, Id “ +
“from Inventory order by Builder”;
using (ITransaction transaction = Session.BeginTransaction())
{
IQuery query = Session.CreateQuery(hqlQuery)
.SetMaxResults(MaxResult)
.SetFirstResult(FirstResult);
return query.List();
}
}
Generated SQL (where max=25 and first=0):
select TOP (@p0)
inventory0_.BUILDER as col_0_0_,
inventory0_.MODEL as col_1_0_,
inventory0_.PRICE as col_2_0_,
inventory0_.ID as col_3_0_
from INVENTORY inventory0_
order by inventory0_.BUILDER;
@p0 = 25 [Type: Int32 (0)]
Generated SQL (where max=25 and first=26):
SELECT TOP (@p0)
col_0_0_,
col_1_0_,
col_2_0_,
col_3_0_
FROM (select inventory0_.BUILDER as col_0_0_,
inventory0_.MODEL as col_1_0_,
inventory0_.PRICE as col_2_0_,
inventory0_.ID as col_3_0_,
ROW_NUMBER()
OVER(ORDER BY inventory0_.BUILDER) as __hibernate_sort_row
from INVENTORY inventory0_) as query
WHERE query.__hibernate_sort_row > @p1
ORDER BY query.__hibernate_sort_row;
@p0 = 25 [Type: Int32 (0)],
@p1 = 26 [Type: Int32 (0)]
Note that two generated SQL queries are shown. The first displays the NHibernate-generated SQL
query created when the SetMaxResults() method is 25 and the SetFirstResult() method is
0. When the first result is 0, it generally means that it is the first page being selected. The second
Working with CreateQuery()
63
NHibernate-generated SQL query results from SetMaxResults() being 25 and SetFirstResult()
being 26. The second SQL query returns rows 26 through 50.
When implementing paging, it is common practice to provide information about the current page
and the overall result set to the user. That means the GuitarStore WPF program should populate
a label with information about where the paged result set is in relation to the entire number of
selectable rows on the table — for example, “Records 0 to 25 of 196 displayed.” To do this, add a
GetInventoryCount() method to the NHibernateInventory class that returns the total number of
records on the INVENTORY table. This method is shown in Listing 2-10.
LISTING 2-10: HQL method to retrieve total record count of the INVENTORY table
public int GetInventoryCount()
{
using (ITransaction transaction = Session.BeginTransaction())
{
IQuery query = Session.CreateQuery(“select count(*) from Inventory”);
return Convert.ToInt32(query.UniqueResult());
}
}
Generated SQL:
select count(*) as col_0_0_
from INVENTORY inventory0_
Up until now, the binding of the CreateQuery result set has been performed within the Window_
Loaded() method of the MainWindow.xaml.cs file found in the GuitarStore WPF project. Instead
of the using the Window_Loaded() method, a new method called PopulateDataGrid() is created in
Listing 2-11. This new method is needed to provide paging buttons with a method for triggering the
retrieval of a paged result set.