2

Using HQL

In the previous chapter, you learned about a few of NHibernate's capabilities, such as lazy loading, Interceptors, logging, and Events. Most important, you learned how to install and configure NHibernate so that it can be used to retrieve data from a database. This chapter addresses using Hibernate Query Language (HQL), which is a fully object-oriented query language similar in appearance to SQL. This chapter covers the following topics:

• CreateQuery, CreateMultiQuery, GetNamedQuery, DetachedQuery, DetachedNamedQuery, and Futures
• Implementing paging
• Creating a round-trip database counter

INTRODUCTION

The IQuery API is used for executing HQL queries on a database. HQL is my personal preference when I don't have or need a specific class to store the results of a query. As shown later in this chapter, some NHibernate methods require using a strongly typed class to retrieve data from the database.

Many HQL methods are made available via the Session interface to execute a query against the database. Figure 2-1 shows a graphical representation of the methods, such as CreateQuery and CreateNamedQuery, and their corresponding execution methods, such as List<T>() or List(), for using the IQuery API. This chapter describes each of the methods and provides examples demonstrating how they can be used to fulfill most of the GuitarStore project requirements defined in Chapter 1.

FIGURE 2-1

Figure 2-2 shows the IQuery, AbstractQueryImpl, and QueryImpl class diagram. IQuery is the interface that is used to implement and create your query objects. When you call the Session.CreateQuery() method, for example, it returns an IQuery. The methods used to execute HQL queries are found in the NHibernate.Impl namespace. An implementation of the IQuery interface is found in the QueryImpl class, which inherits from the abstract AbstractQueryImpl class. Figure 2-2 also shows a few of the methods found in both the QueryImpl and the AbstractQueryImpl classes. Download the NHibernate source code from http://nhforge.org to see them all and how they work.

FIGURE 2-2

The example project used in this book does not require the creation of complex SQL queries, because only two tables exist. As shown in following code snippet, it is possible to create joins between tables using dot ( . ) notation:

Select g.Type, g.Inventory.Model from Guitar g order by g.Type

This is a powerful technique, and one that makes data access intuitive from a programmer's perspective.

Users new to NHibernate frequently ask which query API should be used, as there are several of them. In most cases it is just a matter of preference. I have seen no proof of one API being faster than another in terms of the performance of generated SQL. In many cases the SQL generated using IQuery is identical to ICriteria. Take, for example, a query used to search for models on the GUITAR table. Listing 2-1 compares the SQL query generated using IQuery and ICriteria.

LISTING 2-1: SQL generated using IQuery and ICriteria

IQuery:
select inventory0_.MODEL as col_0_0_
from INVENTORY inventory0_
where inventory0_.MODEL like @p0

ICriteria:
SELECT this_.MODEL as MODEL1_0_
FROM INVENTORY this_
WHERE this_.MODEL like @p0

Other than the naming and case, the NHibernate-generated SQL queries shown here are identical. Nonetheless, there are some things about the IQuery (HQL) API that I personally like and can recommend:

• You can use it when a query has a WHERE clause that is known at design time.
• Data can be retrieved dynamically or strongly typed.
• You can use it when a query has a significant number of joins.
• You can use it when the entire query is known at runtime.
• You can view and modify the HQL query because it is a string.
• IQuery considers everything lazy by default.
• You can define parameters explicitly using a method or add them directly to the string.

It is common for programs to execute queries with static WHERE clauses. Take the program created in this book, for example. The query used to retrieve the data based on a user's selection of guitar type uses the foreign key relationship between the GUITAR and INVENTORY tables, as shown in the following code snippet.

from Inventory where TypeId = :TypeId order by Builder

The Id of the type selected from the ComboBox is passed to the preceding HQL query, which retrieves the inventory of those types of guitars. Figure 2-3 shows how the GuitarStore Windows Presentation Foundation (WPF) program looks with a filtered DataGrid.

FIGURE 2-3

Until now, only strongly typed data retrieval with NHibernate has been discussed. What if the requirements don't call for retrieving all the columns or properties within the class? True, it is possible to lazy load some of the properties, but that wouldn't be the best solution in this scenario because of the limited amount of data being retrieved. What is needed is projection, an example of which is shown in the following code snippet.

select Model, QOH, Price from Inventory where TypeId = :TypeId

This HQL query returns only the Model, QOH, and Price for a given guitar type. However, you cannot return it as an Inventory class. It is executed using the List() execution method, rather than the List<T>(), as shown in Listing 2-2.

LISTING 2-2: Returning a dynamic HQL result set

public IList GetProjectedInventory()
{
   using (ITransaction transaction = Session.BeginTransaction())
   {
      IQuery query = Session.CreateQuery
           (“select Model, QOH, Price from Inventory order by Builder”);
      return query.List();
   }
}

Generated SQL:

select inventory0_.MODEL as col_0_0_,
       inventory0_.QOH as col_1_0_,
       inventory0_.PRICE as col_2_0_
from INVENTORY inventory0_
order by inventory0_.BUILDER

When the result from an NHibernate query is not strongly typed, using that data becomes a little more complex. This is because with a strongly typed result, it is possible to access the data by property name. Conversely, when a result is not strongly typed, the data is accessible by index only. Figure 2-4 shows the difference between a strongly typed result (on the left) and a dynamic one (on the right).

FIGURE 2-4

You can see a significant difference when you want to bind the dynamic result to a DataGrid. To bind a strongly typed result, you simply set the AutoGenerateColumns attribute of the GuitarStore WPF application to true, and the data is shown as retrieved. This is not the case with a dynamic result set. To display a dynamic result set, you need to build a DataTable, add the columns and rows to it, and then bind the DataTable to the DataGrid. The value of the AutoGenerateColumns attribute of the DataGrid should be set to false.

WORKING WITH CREATEQUERY()

The CreateQuery() method is the most commonly used method in the IQuery API. Implementing the CreateQuery() method in the NHibernate.GuitarStore class library and the GuitarStore WPF program requires the following actions:

1. Program a CreateQuery() method that returns a dynamic list of all inventory.
2. Create a BuildDataTable() method that converts the dynamic list to a DataTable.
3. Bind the result from the BuildDataTable() to a DataGrid.
4. Program a CreateQuery() method that accepts and uses a guitar type as parameter to constrain the result set.

First, open the GuitarStore solution if it's not already open. Within the NHibernate.GuitarStore project, double-click the NHibernateInventory.cs file. Add the method in Listing 2-3, which returns an Inventory result set containing only Model, QOH, and Price.

LISTING 2-3: CreateQuery() method returning projected inventory

public IList GetDynamicInventory()
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    IQuery query = Session.CreateQuery
      (“select Builder, Model, Price, Id from Inventory order by Builder”);
    return query.List();
  }
}

Generated SQL:

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_
from INVENTORY inventory0_
order by inventory0_.BUILDER

Next, from the GuitarStore WPF project, open the MainWindow.xaml.cs file and add the method shown in Listing 2-4. The BuildDataTable() method converts a list of column names and an IList containing an NHibernate dynamic result set into a DataTable.

LISTING 2-4: Creating a DataTable using an IList containing a dynamic result

public DataTable BuildDataTable(List<string> columns, IList results)
{
   DataTable dataTable = new DataTable();
   foreach (string column in columns)
   {
     dataTable.Columns.Add(column, typeof(string));
   }

   if (columns.Count > 1)
   {
     foreach (object[] row in results)
     {
       dataTable.Rows.Add(row);
     }
   }
   return dataTable;
}

To retrieve the dynamic Inventory result set, use the method created previously in Listing 2-3, then bind the result of the BuildDataTable() method to the DataGrid, as shown in Listing 2-5.

LISTING 2-5: Retrieving a dynamic result set and binding tt to a DataGrid

using System.Collections;
using System.Data;
using NHibernate.GuitarStore.DataAccess;

private void Window_Loaded(object sender, RoutedEventArgs e)
{
   NHibernateInventory nhi = new NHibernateInventory();
   List<string> fields = new List<string>
   {
      “Builder”, “Model”, “Price”, “Id”
   };

   IList GuitarInventory = nhi.GetDynamicInventory();
   dataGridInventory.ItemsSource =
             BuildDataTable(fields, GuitarInventory).DefaultView;

   if (GuitarInventory != null)
   {
     dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden;
   }
}

Finally, add a new method to the NHibernateInventory class that accepts a guitar type as a parameter and then constrains the result based on that value, and then modify the comboBoxGuitarTypes_SelectionChanged() method within the MainWindow.xaml.cs file of the GuitarStore WPF project to use the newly added method. Listing 2-6 shows the code for the new method added to the NHibernateInventory class that accepts and uses a guitar type.

LISTING 2-6: CreateQuery() method returning inventory by guitar type

public IList GetDynamicInventory(Guid TypeId)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    string hqlQuery = “select Builder, Model, Price, Id ” +
                      “from Inventory ” +
                      “where TypeId = :TypeId order by Builder”;
    IQuery query = Session.CreateQuery(hqlQuery).SetGuid(“TypeId”, TypeId);

    return query.List();
  }
}

Generated SQL:

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_
from INVENTORY inventory0_
where inventory0_.TYPEID=@p0
order by inventory0_.BUILDER;
@p0 = 471c5b3f-19da-4fcb-8e9f-48dd17a00a3d [Type: Guid (0)]

Before implementing the preceding code in the GuitarStore WPF program, add it to the Main() method of the NHibernate.GuitarStore.Console application and test to ensure that it returns the expected results. Listing 2-7, shows how to do this.

LISTING 2-7: Testing CreateQuery() in the console application

Guid guitarType = new Guid(“471c5b3f-19da-4fcb-8e9f-48dd17a00a3d”);
IList list11 = nhi.GetDynamicInventory(guitarType);
Console.WriteLine(“GetDynamicInventory(guitarType): ” +
                  list11.Count.ToString() + “ results”);

Testing the method in the console application also gives you an opportunity to view the SQL generated by NHibernate.

Lastly, the comboBoxGuitarTypes_SelectionChanged() method should be updated to resemble the code in Listing 2-8.

LISTING 2-8: CreateQuery() method from a SelectionChanged() method

private void comboBoxGuitarTypes_SelectionChanged
                            (object sender, SelectionChangedEventArgs e)
{
  try
  {
    dataGridInventory.ItemsSource = null;
    Guitar guitar = (Guitar)comboBoxGuitarTypes.SelectedItem;
    Guid guitarType = new Guid(guitar.Id.ToString());

    NHibernateInventory nhi = new NHibernateInventory();
    IList GuitarInventory = nhi.GetDynamicInventory(guitarType);
    List<string> fields = new List<string>
    {
       “Builder”, “Model”, “Price”, “Id”
    };
    dataGridInventory.ItemsSource =
                     BuildDataTable(fields, GuitarInventory).DefaultView;

    if (GuitarInventory != null)
    {
      dataGridInventory.Columns[3].Visibility =
                       System.Windows.Visibility.Hidden;
    }
  }
  catch (Exception ex)
  {
    labelMessage.Content = ex.Message;
  }
}

The preceding method captures the selected guitar type from the ComboBox, passes its Id to the GetDynamicInventory() method, builds the DataTable, and binds it to the DataGrid.

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.

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.

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.

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 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.

LISTING 2-11: PopulateDataGrid() using HQL paging Functionality

public int FirstResult = 0;
public int MaxResult = 25;
public int totalCount = 0;

private void PopulateDataGrid()
{
   NHibernateInventory nhi = new NHibernateInventory();
   List<string> fields = new List<string>
   {
     “Builder”, “Model”, “Price”, “Id”
   };
   IList GuitarInventory = nhi.GetPagedInventory(MaxResult, FirstResult);
   int inventoryCount = nhi.GetInventoryCount();
   dataGridInventory.ItemsSource =
           BuildDataTable(fields, GuitarInventory).DefaultView;

   if (GuitarInventory != null)
   {
     dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden;
   }

   labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
                      “ to ” + (FirstResult + GuitarInventory.Count).ToString() +
                      “ of ” + inventoryCount.ToString();
   totalCount = inventoryCount;
}

The preceding listing uses the GetDynamicInventory() method created in Listing 2-6 and the GetInventoryCount() method shown in Listing 2-10. The final step is to add paging buttons to the GuitarStore WPF program that call the PopulateDataGrid() method, which sets the FirstResult and the MaxResult class variables.

Add two buttons by dragging and dropping two Button controls from the Toolbox onto the GuitarStore WPF window. The XAML code should resemble Listing 2-12.

LISTING 2-12: Adding Button controls to the GuitarStore WPF program

<Button Content=“-” IsEnabled=“False” Click=“buttonPrevious_Clicky” Height=“23”
        HorizontalAlignment=“Left” Margin=“445,50,0,0” Name=“buttonPrevious”
        VerticalAlignment=“Top” Width=“20” />
<Button Content=“+” Click=“buttonNext_Click” Height=“23”
        HorizontalAlignment=“Left” Margin=“469,50,0,0” Name=“buttonNext”
        VerticalAlignment=“Top” Width=“20” />

Lastly, add the code for both the buttonPrevious_Click() method and the buttonNext_Click() method, shown in Listing 2-13, to the MainWindow.xaml.cs file.

LISTING 2-13: buttonPrevious_Click() and buttonNext_Click() paging logic

private void buttonPrevious_Click(object sender, RoutedEventArgs e)
{
  if (FirstResult > 0)
  {
    FirstResult = FirstResult - MaxResult;
    if (FirstResult < 0) FirstResult = 0;
  }
  else
  {
    buttonPrevious.IsEnabled = false;
  }

  PopulateDataGrid();

  if (FirstResult.Equals(0))
  {
    buttonPrevious.IsEnabled = false;
  }
}

private void buttonNext_Click(object sender, RoutedEventArgs e)
{
    buttonPrevious.IsEnabled = true;
    FirstResult = FirstResult + MaxResult;

    PopulateDataGrid();

    if (FirstResult > 0)
    {
      buttonPrevious.IsEnabled = true;
    }

    if (FirstResult + MaxResult >= totalCount)
    {
      buttonNext.IsEnabled = false;
    }
}

The result of adding the paging buttons and label for tracking to the GuitarStore WPF program resembles Figure 2-5.

FIGURE 2-5

Using the Database Round-Trip Counter

Recall from Chapter 1 that an Interceptor named SQLInterceptor was implemented that captured the NHibernate-generated SQL query. The same Interceptor included a static integer named QueryCounter that is incremented each time a SQL query is created by NHibernate.

The stoplight image that was added to the GuitarStore WPF program is used to graphically represent the value contained within the QueryCounter property.

Referring to Figures 2-3 and 2-5, you can see the lights are yellow and red, respectively. The GuitarStore WPF program required two round-trips to the database to complete the DataGrid population and retrieve the total number of rows on the INVENTORY table in Figure 2-3. In Figure 2-5, the stoplight is red because three queries are performed when the program begins: selection of the DataGrid data, the ComboBox data, and the total count of rows on the INVENTORY table. Each selection uses a round-trip to the database.

It is not a bad thing to have multiple database round-trips when running a program. The stoplight thresholds used in this example are completely arbitrary and apply only to the GuitarStore WPF program. If this capability is implemented into your program, you need to decide which thresholds best apply to your program.

Calling the SetDatabaseRoundTripImage() method at the end of the PopulateDataGrid() method sets the stoplight image to yellow, which means it took two database round-trips to execute that method. It is important to do this here, because in the next section you will use the CreateMultiQuery method to combine the two round-trips into one.

Working with Calculated Fields

A calculated field derives its value from a calculation of other fields. One example is profit, which is a product of sale price minus cost. It is possible to use calculated fields with NHibernate. To add calculated fields to the GuitarStore solution, you perform the following steps:

1. Add a property to the Inventory.cs file to store the field called Profit.
2. Add a Profit property to the corresponding Inventory.hbm.xml mapping file, and include the formula that calculates the value.
3. Add a Profit column to the HQL query.
4. Add a Profit column to DataTable and display it in DataGrid.

First, open the Inventory.cs file found in the Common directory of the NHibernate.GuitarStore project. Add a new property named Profit so that the class file now resembles Listing 2-14.

LISTING 2-14: Inventory class with additional calculated field property

namespace NHibernate.GuitarStore.Common
{
    public class Inventory
    {
        public Inventory() { }

        public virtual Guid Id { get; set; }
        public virtual Guid TypeId { get; set; }
        public virtual string Builder { get; set; }
        public virtual string Model { get; set; }
        public virtual int? QOH { get; set; }
        public virtual decimal? Cost { get; set; }
        public virtual decimal? Price { get; set; }
        public virtual DateTime? Received { get; set; }
        public virtual decimal? Profit { get; set; }
    }
}

Next, open the Inventory.hbm.xml file in the Mapping directory of the NHibernate.GuitarStore project. Add a new property so that the mapping file now resembles Listing 2-15.

LISTING 2-15: Inventory mapping File with additional calculated field property

<?xml version=“1.0” encoding=“utf-8” ?>
<hibernate-mapping xmlns=“urn:nhibernate-mapping-2.2”
                   assembly=“NHibernate.GuitarStore”>
  <class name=“NHibernate.GuitarStore.Common.Inventory, NHibernate.GuitarStore”
         table=“INVENTORY”>
    <property name=“TypeId”   column=“TYPEID”   type=“System.Guid”
    <property name=“Builder”  column=“BUILDER”  type=“System.String” />
    <property name=“Model”    column=“MODEL”    type=“System.String” />
    <property name=“QOH”      column=“QOH”      type=“System.Int32” />
    <property name=“Cost”     column=“COST”     type=“System.Decimal” />
    <property name=“Price”    column=“PRICE”    type=“System.Decimal” />
    <property name=“Received” column=“RECEIVED” type=“System.DateTime” />
    <property name=“Profit”   formula=“ROUND((Price-Cost), 2)” type=“decimal”  />
  </class>
</hibernate-mapping>

Notice that the formula attribute is used. This attribute supports the insertion of SQL to calculate a property. Executing an HQL query that includes the Profit property results in the NHibernate-generated SQL shown in Listing 2-16. This approach calculates the Profit value on the database.

LISTING 2-16: NHibernate-generated SQL using the formula attribute

select inventory0_.BUILDER as col_0_0_,
       inventory0_.MODEL as col_1_0_,
       inventory0_.PRICE as col_2_0_,
       ROUND((inventory0_.Price-inventory0_.Cost), 2) as col_3_0_,
       inventory0_.ID as col_4_0_
from INVENTORY inventory0_
order by inventory0_.BUILDER

You can implement a calculated field without using the formula attribute. Instead of adding the code previously shown in Listing 2-15 and 2-16, add the code in Listing 2-17 to the Inventory.cs file located in the Common directory of the NHibernate.GuitarStore project. Because the Price and Cost already exist in this Inventory class, Profit can be defined as a normal property and is calculated using local memory.

LISTING 2-17: Inventory class calculated field property

public virtual decimal? Profit
{
  get { return ((decimal)Price - (decimal)Cost); }
}

No addition to the Inventory.hbm.xml mapping file is required if the code in Listing 2-17 is used. Next, add a general-purpose method to the NHibernateInventory class called ExecuteHQL<T>()that returns a strongly typed result set, as shown in Listing 2-18.

LISTING 2-18: General-purpose HQL query method

public IList<T> ExecuteHQL<T>(string hqlQuery)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    IQuery query = Session.CreateQuery(hqlQuery);
    return query.List<T>();
  }
}

Generated SQL:

select inventory0_.ID as ID1_,
       inventory0_.TYPEID as TYPEID1_,
       inventory0_.BUILDER as BUILDER1_,
       inventory0_.MODEL as MODEL1_,
       inventory0_.QOH as QOH1_,
       inventory0_.COST as COST1_,
       inventory0_.PRICE as PRICE1_,
       inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
order by inventory0_.BUILDER

Notice that the SQL NHibernate generates for the Inventory class no longer contains the injected SQL formula, as in Listing 2-18. Modify the PopulateDataGrid() method located in the MainWindow.xaml.cs file within the GuitarStore WPF project, as shown in Listing 2-19.

LISTING 2-19: PopulateDataGrid() with strongly typed result set

private void PopulateDataGrid()
{
   NHibernateInventory nhi = new NHibernateInventory();
   IList<Inventory> GuitarInventory =
          nhi.ExecuteHQL<Inventory>(“from Inventory order by Builder”);
   dataGridInventory.ItemsSource = GuitarInventory;

   if (GuitarInventory != null)
   {
     dataGridInventory.Columns[0].Visibility = System.Windows.Visibility.Hidden;
     dataGridInventory.Columns[1].Visibility = System.Windows.Visibility.Hidden;
     dataGridInventory.Columns[9].Visibility = System.Windows.Visibility.Hidden;
   }
   SetDatabaseRoundTripImage();
}

The Profit property is now visible within the GuitarStore WPF window, as shown in Figure 2-6.

FIGURE 2-6

IMPLEMENTING CREATEMULTIQUERY()

One of the powerful features NHibernate supports is the batching of queries. This means it is possible to execute two or more queries on the database with a single trip. Two steps are needed to combine the query that populates the DataGrid and the query that selects the total row count of the INVENTORY table into one:

1. Create a method that uses CreateMultiQuery().
2. Modify the GuitarStore WPF PopulateDataGrid() method to use the multi-query method.

The first step is to create a new method named GetInventoryPaging() in the NHibernateInventory class to use CreateMultiQuery() and attach both the query that populates the DataGrid and the query that retrieves the total row count on the INVENTORY table. Listing 2-20 shows how it's done.

LISTING 2-20: Example of the CreateMultiQuery() method

public int GetInventoryPaging(int MaxResult, int FirstResult, out IList resultSet)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    string hqlQuery = “select Builder, Model, Price, Id ” +
                      “from Inventory order by Builder”;
    IQuery query = Session.CreateQuery(hqlQuery)
                          .SetMaxResults(MaxResult)
                          .SetFirstResult(FirstResult);
    IQuery count = Session.CreateQuery(“select count(*) from Inventory”);

    IMultiQuery mQuery = Session.CreateMultiQuery()
                                .Add(“result”, query)
                                .Add<long>(“RowCount”, count);
    resultSet = (IList)mQuery.GetResult(“result”);
    int totalCount = (int)((IList<long>)mQuery.GetResult(“RowCount”)).Single();
    return totalCount;
  }
}

Generated SQL:

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)]
from INVENTORY inventory0_;

The GetInventoryPaging() method implements two CreateQuery() methods, each one being a different HQL query. Then the CreateMultiQuery() method is used to execute both methods and return the IMultiQuery interface. Notice the Add() method is used to attach the two HQL queries to the CreateMultiQuery() method. The strings (“result”, “RowCount”) are the values used in the GetResult() method of the IMultiQuery implementation to fetch the results retrieved from the database in a single round-trip.

The final step is to modify the PopulateDataGrid() method within the GuitarStore WPF program to use the method just shown. The PopulateDataGrid() method should resemble the code shown in Listing 2-21.

LISTING 2-21: Implementing CreateMultiQuery() in the GuitarStore WPF program

private void PopulateDataGrid()
{
  NHibernateInventory nhi = new NHibernateInventory();
  List<string> fields = new List<string>
  {
    “Builder”, “Model”, “Price”, “Id”
  };
  IList GuitarInventory = null;
  int inventoryCount = nhi.GetInventoryPaging(MaxResult,
                                              FirstResult,
                                              out GuitarInventory);
  dataGridInventory.ItemsSource =
                   BuildDataTable(fields, GuitarInventory).DefaultView;

  if (GuitarInventory != null)
  {
    dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden;
  }

  labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
                       “ to ” + (FirstResult + GuitarInventory.Count).ToString() +
                       “ of ” + inventoryCount.ToString();

  totalCount = inventoryCount;

  SetDatabaseRoundTripImage();
}

Two different ways to confirm that the count and the DataGrid retrieval occurred via a single round-trip to the database have been implemented. Notice in Figure 2-7 that the stoplight is now green and the tool tip says “1 round-trip to database.”

FIGURE 2-7

The second possibility is to select the View SQL button, which displays the most recently executed NHibernate-generated SQL query. As shown in Figure 2-8, the first query is the paging query, which returns the data for the DataGrid, and the second query is the select count(*) query.

FIGURE 2-8

Not all database management systems (DBMSs) support multiple queries. To determine whether the DBMS supports multiple queries, navigate to the namespace NHibernate.Driver.YOURDRIVER.cs and search for an overridden instance of the SupportsMultipleQueries() method. If you find it and it returns true, then you can use multiple queries. The YOURDRIVER.cs class inherits from the NHibernate.Driver.DriverBase class and contains the virtual method SupportsMultipleQueries(), which returns false. Therefore, unless it is overridden in a child class to return true, batching the queries together does not work.

If you are developing a program that may be used with a number of different DBMSs, you need to be careful about using CreateMultiQuery(). Multiple queries work on Microsoft SQL Server; however, if you point it to an Oracle or DB2 database, you will receive an error stating that this DBMS does not support multiple queries. This is demonstrated in Listing 2-22.

LISTING 2-22: MultiQueryImpl method() throwing an exception

internal MultiQueryImpl(ImplSession session,
  ISessionFactoryImplementor factory)
{
    IDriver driver = session.Factory.ConnectionProvider.Driver:
    if (!driver.SupportMultipleQueries)
    {
       throw new NotSupportedException(
      string.Format(“The driver {0} does not support multiple queries.”,
             driver.GetType().FullName);
    }
    dialect = session.Factory.Dialect;
    this.session = session;
    this.factory = factory;
}

UNDERSTANDING GETNAMEDQUERY()

The GetNamedQuery() method enables programmers to store a static HQL or SQL query that can be accessed and executed by passing its name as a parameter. For example, if you know that the query you need to run may change, an alternative to storing the query as a string in the source code is to store the query in an .hbm.xml file instead. Similar to a normal HQL query using the CreateQuery(), it is possible to send parameters to a named query.

To implement GetNamedQuery, the following must be performed:

1. Create a query.hbm.xml file to contain named queries.
2. Create an HQL query that returns the SUM of each guitar type.
3. Create a general-purpose method that retrieves data using a named query.
4. Add a Sum Button to the GuitarStore WPF program and display the named query results in the DataGrid.

As described in the preceding list, the first action to take is to create a Query.hbm.xml file to store the named queries. The file containing the named queries can have any name, but it is good practice to separate the named queries from the class mappings. It is very important, as previously mentioned, that in the properties window you set the Build Action to Embedded Resource or that you add the .hbm.xml file to the program's working directory. Otherwise, it is not included in the Configuration and an exception is thrown.

The contents of the Query.hbm.xml file are shown in Listing 2-23. Queries to calculate the sum of each guitar type have been added using both SQL and HQL.

LISTING 2-23: Named Query.hbm.xml example

<?xml version=“1.0” encoding=“utf-8” ?>
<hibernate-mapping xmlns=“urn:nhibernate-mapping-2.2”>
  <sql-query name=“GuitarValueByTypeSQL”>
    <![CDATA[
      SELECT G.TYPE, SUM(I.COST)
      FROM GUITAR G, INVENTORY I
      WHERE G.ID = I.TYPEID
      GROUP BY G.TYPE
      ]]>
  </sql-query>
  <query name=“GuitarValueByTypeHQL”>
    <![CDATA[
      select g.Type, SUM(i.Cost)
      from Guitar g, Inventory i
      where g.Id = i.TypeId
      group by g.Type
      ]]>
  </query>
</hibernate-mapping>

Note that using the sql-query element notifies NHibernate that the contained query is native SQL. NHibernate utilizes the SqlQueryImpl class instead of the QueryImpl class to execute the SQL and return the result as an IQuery object.

When an HQL query is provided, the QueryImpl class is used. If you want to view the source code for GetNamedQuery(), it can be found in the NHibernate.Impl namespace within the AbstractSessionImpl class. Comparing the GetNamedQuery() method source to the CreateQuery() method source, located in the same class, would reveal many similarities.

Next, create a general-purpose method that can take the name of the query as an argument and return the results. Listing 2-24 displays this method added to the NHibernateInventory class.

LISTING 2-24: Example Using the GetNamedQuery() method

public IList ExecuteNamedQuery(string QueryName)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    IQuery query = Session.GetNamedQuery(QueryName);
    return query.List();
  }
}

Generated SQL:

select guitar0_.TYPE as col_0_0_,
       SUM(inventory1_.COST) as col_1_0_
from GUITAR guitar0_,
     INVENTORY inventory1_
where guitar0_.ID=inventory1_.TYPEID
group by guitar0_.TYPE

Before implementing the ExecuteNamedQuery() in the GuitarStore WPF program, open the Program.cs file found in the NHibernate.GuitarStore console application project and add the following code in Listing 2-25.

LISTING 2-25: Testing the ExecuteNamedQuery() method from the console

IList guitarTypeSUM = nhi.ExecuteNamedQuery(“GuitarValueByTypeHQL”);
Console.WriteLine(“ExecuteNamedQuery(GuitarValueByTypeHQL): ” +
                  guitarTypeSUM15.Count.ToString() + “ results”);

Rather than try to implement the previous method directly in the GuitarStore WPF program, test it first to ensure that it works. This way, you are removing the implementation layer from any debugging efforts required if problems occur.

The next step is to drag a Button control from the Toolbox and add it to the GuitarStore WPF window. Listing 2-26 shows the addition of the Button and the Click event, buttonSUM_Click, which is called when the button is selected.

LISTING 2-26: Adding the Sum button, which calls the ExecuteNamedQuery() method

<Button Content=“Sum” Height=“23” HorizontalAlignment=“Left”
        Margin=“499,137,0,0” Name=“buttonSUM” VerticalAlignment=“Top”
        Width=“92” Click=“buttonSUM_Click” />

Finally, add the code to the buttonSUM_Click() method created in the MainWindow.xaml.cs file that uses the ExecuteNamedQuery() method and binds the results to the DataGrid. Listing 2-27 shows the contents of the buttonSUM_Click() method.

LISTING 2-27: Using the button SUM_Click() method to run the executeNamedQuery() method

private void buttonSUM_Click(object sender, RoutedEventArgs e)
{
   NHibernateInventory nhi = new NHibernateInventory();
   List<string> fields = new List<string>
   {
     “Guitar Type”, “Total Value”
   };
   IList GuitarInventory = nhi.ExecuteNamedQuery(“GuitarValueByTypeHQL”);
   dataGridInventory.ItemsSource =
                    BuildDataTable(fields, GuitarInventory).DefaultView;

   SetDatabaseRoundTripImage();
}

Figure 2-9 is an example of the data returned from the ExecuteNameQuery(), which utilizes NHibernate's GetNamedQuery() method.

FIGURE 2-9

Implementing Aggregate Database Functions with GetNamedQuery

NHibernate supports the most common aggregate database functions, but not all of them. The following list shows aggregates supported by NHibernate:

• AVG
• SUM
• MIN
• MAX
• COUNT

In this section, aggregate database functions will be implemented via the GetNamedQuery() method. However, it is possible to use them directly within an HQL statement or ICriteria, which is discussed in the next chapter. You will perform the following steps in this section:

1. Add AVG, MIN, MAX, and COUNT queries to the Query.hbm.xml file.
2. Add AVG, MIN, MAX, and COUNT buttons to the GuitarStore WPF program.
3. Reuse the general-purpose method, ExecuteNamedQuery(), to execute each of the aggregate database functions.

First, add a few more named queries to the Query.hbm.xml file to retrieve the average cost of the guitars by type, the least expensive guitar by type, the most expensive guitar by type, and the quantity on hand by guitar type. The HQL queries that use AVG, MIN, MAX, and COUNT are shown in Listing 2-28.

LISTING 2-28: Example of HQL aggregate functions

  <query name=“GuitarAVGValueByTypeHQL”>
    <![CDATA[
      select g.Type, ROUND(AVG(i.Cost), 2)
      from Guitar g, Inventory i
      where g.Id = i.TypeId
      group by g.Type
      ]]>
  </query>
  <query name=“GuitarMINValueByTypeHQL”>
    <![CDATA[
      select g.Type, MIN(i.Cost)
      from Guitar g, Inventory i
      where g.Id = i.TypeId
      group by g.Type
      ]]>
  </query>
  <query name=“GuitarMAXValueByTypeHQL”>
    <![CDATA[
      select g.Type, MAX(i.Cost)
      from Guitar g, Inventory i
      where g.Id = i.TypeId
      group by g.Type
      ]]>
  </query>
  <query name=“GuitarCOUNTByTypeHQL”>
    <![CDATA[
      select g.Type, COUNT(DISTINCT i.Model)
      from Guitar g, Inventory i
      where g.Id = i.TypeId
      group by g.Type
      ]]>
  </query>

The ExecuteNamedQuery() method is used to execute the previously listed HQL named queries. No additional method needs to be added to the NHibernateInventory class to execute them. The NHibernate-generated SQL for the named HQL queries in Listing 2-28 are shown in Listing 2-29.

LISTING 2-29: NHibernate-generated aggregate database function SQL query

Average:

select guitar0_.TYPE as col_0_0_,
       round(AVG(inventory1_.COST), 2) as col_1_0_
from GUITAR guitar0_,
     INVENTORY inventory1_
where guitar0_.ID=inventory1_.TYPEID
group by guitar0_.TYPE

Minimum:

select guitar0_.TYPE as col_0_0_,
       MIN(inventory1_.COST) as col_1_0_
from GUITAR guitar0_,
     INVENTORY inventory1_
where guitar0_.ID=inventory1_.TYPEID
group by guitar0_.TYPE

Maximum:

select guitar0_.TYPE as col_0_0_,
       MAX(inventory1_.COST) as col_1_0_
from GUITAR guitar0_,
     INVENTORY inventory1_
where guitar0_.ID=inventory1_.TYPEID
group by guitar0_.TYPE

Count:

select guitar0_.TYPE as col_0_0_,
       count(distinct inventory1_.MODEL)as col_1_0_
from GUITAR guitar0_,
     INVENTORY inventory1_
where guitar0_.ID=inventory1_.TYPEID
group by guitar0_.TYPE

Next, add four Button controls (Average, Minimum, Maximum, and Count) to the GuitarStore WPF program. The XAML is shown in Listing 2-30.

LISTING 2-30: Aggregate Database function buttons

<Button Content=“Average” Height=“23” HorizontalAlignment=“Left”
        Margin=“499,166,0,0” Name=“buttonAverage” VerticalAlignment=“Top”
        Width=“92” Click=“buttonAverage_Click” />
<Button Content=“Minimum” Height=“23” HorizontalAlignment=“Left”
        Margin=“499,195,0,0” Name=“buttonMinimum” VerticalAlignment=“Top”
        Width=“92” Click=“buttonMinimum_Click” />
<Button Content=“Maximum” Height=“23” HorizontalAlignment=“Left”
        Margin=“499,224,0,0” Name=“buttonMaximum” VerticalAlignment=“Top”
        Width=“92” Click=“buttonMaximum_Click” />
<Button Content=“Count” Height=“23” HorizontalAlignment=“Left”
        Margin=“499,253,0,0” Name=“buttonCount” VerticalAlignment=“Top”
        Width=“92” Click=“buttonCount_Click” />

Note that each of the buttons has a Click event and an associated method that is called when clicked. The content of the methods are identical other than the DataGrid column heading and the value of the named query to execute — for example, GetCountByTypeHQL. Listing 2-31 shows the code within the buttonMaximum_Click() method found within the MainWindow.xaml.cs file of the GuitarStore project.

LISTING 2-31: Calling an aggregate database funtion from GuitarStore WPF

private void buttonMaximum_Click(object sender, RoutedEventArgs e)
{
  NHibernateInventory nhi = new NHibernateInventory();
  List<string> fields = new List<string>
  {
    “Guitar Type”, “Maximum Value”
  };

  IList GuitarInventory = nhi.ExecuteNamedQuery(“GuitarMAXValueByTypeHQL”);
  dataGridInventory.ItemsSource =
                   BuildDataTable(fields, GuitarInventory).DefaultView;

  SetDatabaseRoundTripImage();
}

When the Maximum button is clicked, the ExecuteNamedQuery() method receives the GuitarMAXValueByTypeHQL named query as a parameter, and then the results are bound to the DataGrid of the GuitarStore WPF program. The result is a WPF window that resembles Figure 2-10.

FIGURE 2-10

UNDERSTANDING DETACHEDQUERY

In every example up until now, the query being created has been explicitly attached to a Session. Using a detached query enables you to create the query without being initially associated to a Session. Once the query has been created and is ready to be executed, it can then be bound to a Session and run.

The syntax for accessing and executing a DetachedQuery is shown in Listing 2-32.

LISTING 2-32: Example of a DetachedQuery

using NHibernate.Impl;

public IList ExecuteDetachedQuery(string searchParameter)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    string hqlQuery = “select Builder, Model, Price, Id ” +
                      “from Inventory ” +
                      “where Model like :search ” +
                      “order by Builder”;
    IDetachedQuery detachedQuery = new DetachedQuery(hqlQuery)
                                  .SetString(“search”, searchParameter);

    IQuery executableQuery = detachedQuery.GetExecutableQuery(Session);
    return executableQuery.List();
  }
}

Generated SQL:

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_
from INVENTORY inventory0_
where inventory0_.MODEL like @p0
order by inventory0_.BUILDER;
@p0 = ‘%L%’ [Type: String (4000)]

The DetachedQuery class is found within the NHibernate.Impl namespace, so the using NHibernate.Impl; directive is added to the NHibernateInventory class.

To implement the preceding search functionality in the GuitarStore WPF program, the following actions are required:

1. Add a TextBox control with a Label control to the GuitarStore WPF window.
2. Add a Button control to the GuitarStore WPF window that uses the ExecuteDetachedQuery() method and binds the results to the DataGrid.

Listing 2-33 shows the XAML code that adds the TextBox, Button, and Label controls to the MainWindow.xaml file located in the GuitarStore WPF project.

LISTING 2-33: Search controls for the GuitarStore WPF program

<TextBox Height=“23” HorizontalAlignment=“Left” Margin=“12,21,0,0”
         Name=“textBoxSearch” VerticalAlignment=“Top” Width=“195” />
<Label Content=“Search:” Height=“28” HorizontalAlignment=“Left”
       Margin=“14,4,0,0” Name=“labelSearch” VerticalAlignment=“Top” />
<Button Content=“Search” Height=“23” HorizontalAlignment=“Left”
        Margin=“499,282,0,0” Name=“buttonSearch” VerticalAlignment=“Top”
        Width=“92” Click=“buttonSearch_Click”/>

Lastly, add the logic to the buttonSearch_Click() method found in the MainWindow.xaml.cs file of the GuitarStore project, as shown in Listing 2-34. This code captures the search criteria from the TextBox, passes it to the ExecuteDetachedQuery() method, and then binds the results to the DataGrid.

LISTING 2-34: Calling the DetachedQuery from the GuitarStore WPF program

private void buttonSearch_Click(object sender, RoutedEventArgs e)
{
  NHibernateInventory nhi = new NHibernateInventory();
  List<string> fields = new List<string>
  {
    “Builder”, “Model”, “Price”, “Id”
  };

  IList GuitarInventory =
        nhi.ExecuteDetachedQuery(“%” + textBoxSearch.Text + “%”);
  dataGridInventory.ItemsSource =
                   BuildDataTable(fields, GuitarInventory).DefaultView;

  if (GuitarInventory != null && GuitarInventory.Count > 0)
  {
    dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden;
  }

  SetDatabaseRoundTripImage();
}

The detached functionality also enables you to pass the query around to different methods and modify it in whatever way the requirements dictate. For example, Listing 2-35 modifies the method shown in the previous listing to add an AND to the HQL query and call another method, AddPriceLevel(), that modifies the query.

LISTING 2-35: Extended DetachedQuery example

public IList ExecuteDetachedQuery(string searchParameter)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    string hqlQuery = “select Builder, Model, Price, Id ” +
                      “from Inventory ” +
                      “where Model like :search ” +
                      “AND Cost > :cost ”;

    IDetachedQuery detachedQuery = new DetachedQuery(hqlQuery)
                                   .SetString(“search”, searchParameter);
    AddPriceLevel(detachedQuery);

    IQuery executableQuery = detachedQuery.GetExecutableQuery(Session);
    return executableQuery.List();
  }
}

private void AddPriceLevel(IDetachedQuery query)
{
    query.SetDecimal(“cost”, 1000);
}

Generated SQL:

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_
from INVENTORY inventory0_
where (inventory0_.MODEL like @p0) and
       inventory0_.COST>@p1
order by inventory0_.BUILDER;
@p0 = '%L%' [Type: String (4000)],
@p1 = 1000 [Type: Decimal (0)]

The AddPriceLevel() method receives the DetachedQuery as a parameter and modifies it. Then the GetExecutableQuery() method is called to get the IQuery interface associated with the Session. Finally, the List() method is used to retrieve the results.

When a user enters some search criteria into the search textbox and clicks the Search Button, the DetachedQuery is executed. The results have been restricted to return only guitars that cost greater than €1,000. Figure 2-11 shows the GuitarStore WPF window.

FIGURE 2-11

WORKING WITH DETACHEDNAMEDQUERY

Named queries and detached queries have both been discussed already in this chapter. As you can probably guess, DetachedNamedQuery enables you to select a named query and modify it without it being associated to a Session.

Similar to how named queries were added previously, another query is added to the Query.hbm.xml file, as shown in Listing 2-36.

LISTING 2-36: A named query for DetachedNamedQuery example

<query name=“InventoryHQLSearch”>
  <![CDATA[
    from Inventory where Model like :search
  ]]>
</query>

Listing 2-37 shows how to implement the DetachedNamedQuery in the NHibernateInventory class.

LISTING 2-37: Implementing a DetachedNamedQuery

public IList<T> ExecuteDetachedNamedQuery<T>
                                    (string searchParameter, string QueryName)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    IDetachedQuery detachedQuery = new DetachedNamedQuery(QueryName)
                                   .SetString(“search”, searchParameter);

    IQuery executableQuery = detachedQuery.GetExecutableQuery(Session);
    return executableQuery.List<T>();
  }
}

Generated SQL where class is Inventory:

select inventory0_.ID as ID1_,
       inventory0_.TYPEID as TYPEID1_,
       inventory0_.BUILDER as BUILDER1_,
       inventory0_.MODEL as MODEL1_,
       inventory0_.QOH as QOH1_,
       inventory0_.COST as COST1_,
       inventory0_.PRICE as PRICE1_,
       inventory0_.RECEIVED as RECEIVED1_
from INVENTORY inventory0_
where inventory0_.MODEL like @p0;
@p0 = ‘%L%’ [Type: String (4000)]

Notice that this example uses a strongly typed generic result set. It's possible to strongly type the DetachedQuery too. Refer to Figure 2-1, which shows the different IQuery interfaces and their execution methods. You can see that GetExecutableQuery() supports both the List() and List<T>() execution methods.

By strongly typing the result set, accessing the properties of the class is simple and clear. Listing 2-32 demonstrates the implementation of the DetachedQuery, which receives a projected List(), and the DetachedNamedQuery, which receives a strongly typed List<T>().

To implement the DetachedNamedQuery() in the GuitarStore WPF program, modify the existing buttonSearch_Click() method of the MainWindow.xaml.cs file. Change the buttonSearch_Click() method to resemble the code shown in Listing 2-38.

LISTING 2-38: Using a DetachedNamedQuery() from the GuitarStore WPF

private void buttonSearch_Click(object sender, RoutedEventArgs e)
{
   NHibernateInventory nhi = new NHibernateInventory();
   IList<Inventory> GuitarInventory =
       nhi.ExecuteDetachedNamedQuery<Inventory>(“%" + textBoxSearch.Text + “%”,
                                               “InventoryHQLSearch”);

   dataGridInventory.ItemsSource = GuitarInventory;

   if (GuitarInventory != null && GuitarInventory.Count > 0)
   {
     dataGridInventory.Columns[0].Visibility = System.Windows.Visibility.Hidden;
     dataGridInventory.Columns[1].Visibility = System.Windows.Visibility.Hidden;
     dataGridInventory.Columns[8].Visibility = System.Windows.Visibility.Hidden;
   }

   labelPaging.Content = “”;
   buttonNext.IsEnabled = false;

   SetDatabaseRoundTripImage();
}

UNDERSTANDING FUTURES

As previously noted, you can implement the batching of queries for execution using a single round-trip to the database — if your DBMS supports this. However, if your program needs to support batching because it must function with different DBMSs, some of which can batch queries and some of which can't, then using NHibernate's Futures functionality is the solution. The reason it's possible is because of implementation of the Future<T>() and FutureValue<T>() execution methods, found in both the IQuery and ICriteria APIs.

The implementation methods for Future<T>() and FutureValue<T>() can be found in the AbstractQueryImpl class for IQuery and the CriteriaImpl class for ICriteria. In both execution methods there is a check for MultipleQuery support. If the current database driver does not support multiple queries, a normal List<T> is returned, as shown in Listing 2-39. If the database driver being used does support multiple queries, then it batches the queries as expected. Recall that this differs from using the MultiQuery interface, whereby an error is returned.

LISTING 2-39: Example of the IQuery Future<T>() implementation

public IEnumerable<T> Future<T>()
{
    if (!session.Factory.ConnectionProvider.Driver.SupportMultiQueries)
    {
       return List<T>();
    }
    session.FutureQueryBatch.Add<T>(this);
    return session.FutureQueryBatch.GetEnumerator<T>();
}

A common implementation of query batching is used with paging. It makes a lot of sense to combine the retrieval of a data result with a query that counts the total number of possible rows. This count gives the user some idea of how many possible results there are. However, there is no restriction on the different types of queries that can be grouped together.

Implementing paging with Futures in the GuitarStore solution only requires changing the GetInventoryPaging() method of the NHibenrateInventory class to use Future() methods instead of CreateMultiQuery() methods. Listing 2-40 shows the updated GetInventoryPaging() using Future() methods.

LISTING 2-40: Paging Using Futures

public int GetInventoryPaging(int MaxResult, int FirstResult, out IList resultSet)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
    string hqlQuery = “select Builder, Model, Price, Id ” +
                      “from Inventory order by Builder”;
    IQuery query = Session.CreateQuery(hqlQuery)
                          .SetMaxResults(MaxResult)
                          .SetFirstResult(FirstResult);
    var count = Session.CreateQuery(“select count(*) from Inventory”)
                       .FutureValue<long>();

    resultSet = query.Future<object>().ToList();
    int totalCount = (int)count.Value;
    return totalCount;
  }
}

Generated SQL:

select count(*) as col_0_0_
from INVENTORY inventory0_;
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 inven;
@p0 = 25 [Type: Int32 (0)].BUILDER;

To get the count, the FutureValue<T>() execution method is used. It is strongly typed to a long data type and then cast to an int when returned. The Future<T>() execution method requires a strongly typed class to be returned. However, as shown in the GuitarStore WPF program, the HQL query passed to this method is projected and requests only a subset of the Inventory class. Therefore, it is strongly typed to an object instead of the object class. That's kind of a trick, if you ask me, but a good trick.

Although paging is perhaps the most common use of Futures, there is another less common but very useful purpose for this functionality. Because Futures wait to execute until the data retrieved from the query is actually used, you can batch queries together at points in the program and execute the queries only when the data within one of queries is actually needed. For example, as a computer program matures, it is often possible to become more aware of what data is being retrieved and when the retrieval of this data is necessary. For example, a usage pattern may be discovered whereby a user typically wants a specific piece of information when the computer program is started, perhaps the combined value of all inventory.

Instead of simply executing the “always required” queries, such as the population of the DataGrid, which is one round-trip to the database, and then executing a “probable” query later, which is another round-trip to the database, queries with a high probability of being executed can be batched together with “always required” queries, just in case the data is used in the future. The expectation is that the additional “probable” query being batched and executed with the “always required” query results in better overall performance than running each one separately. To implement this concept in the GuitarStore solution, the following actions must be performed:

1. Add a named query that calculates the total value of all guitars in inventory.
2. Add the execution of this named query to the GetInventoryPaging() method using the Future() method and an additional return parameter.
3. Add two class variables to the GuitarStore WPF project (datetime and decimal).
4. Modify PopulateDataGrid() to use the new modified GetInventoryPaging() method and store the inventory value.
5. Add a Button control named Total Value to the GuitarStore WPF project to display the stored value.

First, open the Query.hbm.xml file located in the NHibernate.GuitarStore project. Add a new named query to the file as shown in Listing 2-41.

LISTING 2-41: Named query to select total inventory value

<query name=“GuitarTotalInventoryValueHQL”>
  <![CDATA[
    select SUM(Price) from Inventory
    ]]>
</query>

Next, modify the GetInventoryPaging() method found in the NHibernateInventory class so that it uses the NHibernate GetNamedQuery() method to execute the GuitarTotalInventoryValueHQL named query, as shown in Listing 2-42. Also, use the FutureValue<T>() method so that it is batched along with the other queries.

LISTING 2-42: Additonal batched query to use if necessary

public int GetInventoryPaging(int MaxResult, int FirstResult,
                              out IList resultSet, out decimal totalInventory)
{
  using (ITransaction transaction = Session.BeginTransaction())
  {
     string hqlQuery = “select Builder, Model, Price, Id ” +
                       “from Inventory order by Builder”;
     IQuery query = Session.CreateQuery(hqlQuery)
                           .SetMaxResults(MaxResult)
                           .SetFirstResult(FirstResult);
     var count = Session.CreateQuery(“select count(*) from Inventory”)
                        .FutureValue<long>();

     var invTotal = Session.GetNamedQuery(“GuitarTotalInventoryValueHQL”)
                           .FutureValue<decimal>();

     resultSet = query.Future<object>().ToList();
     int totalCount = (int)count.Value;
     totalInventory = invTotal.Value;

     return totalCount;
   }
 }

Generated SQL (all 3 run using 1 database round-trip):

select count(*) as col_0_0_ from INVENTORY inventory0_;
select SUM(inventory0_.PRICE) as col_0_0_ from INVENTORY inventory0_;
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 inven;
@p0 = 25 [Type: Int32 (0)].BUILDER;

Next, in the MainWindow.xaml.cs file found in the GuitarStore WPF project, modify the PopulateDataGrid() method to include the code displayed in Listing 2-43. Notice that two class variables are created.

LISTING 2-43: PopulateDataGrid() using three batched HQL queries

decimal totalInventoryValue;
private void PopulateDataGrid()
{
   NHibernateInventory nhi = new NHibernateInventory();
   List<string> fields = new List<string>
   {
      “Builder”, “Model”, “Price”, “Id”
   };
   IList GuitarInventory = null;
   int inventoryCount = nhi.GetInventoryPaging(MaxResult, FirstResult,
                           out GuitarInventory, out totalInventoryValue);
   dataGridInventory.ItemsSource =
                           BuildDataTable(fields, GuitarInventory).DefaultView;

   if (GuitarInventory != null)
   {
      dataGridInventory.Columns[3].Visibility = System.Windows.Visibility.Hidden;
   }

   labelPaging.Content = “Retrieved ” + FirstResult.ToString() +
                     “ to ” + (FirstResult + GuitarInventory.Count).ToString() +
                     “ of ” + inventoryCount.ToString();

   totalCount = inventoryCount;
   SetDatabaseRoundTripImage();

}

Next, add a Button control to the MainWindow.xaml file as shown in Listing 2-44.

LISTING 2-44: Total Value button on the GuitarStore MainWindow

<Button Content=“Total Value” Height=“23” HorizontalAlignment=“Left”
        Margin=“499,311,0,0” Name=“buttonTotalValue” VerticalAlignment=“Top”
        Width=“92” Click=“buttonTotalValue_Click” />

Finally, add the code to the buttonTotalValue_Click() method that displays the total value of the guitar inventory via the labelMessage control. Listing 2-45 shows the contents of the buttonTotalValue_Click() method.

LISTING 2-45: The buttonTotalValue_Click() method

private void buttonTotalValue_Click(object sender, RoutedEventArgs e)
{
    labelMessage.Content = “Total value of inventory is: ” +
                           totalInventoryValue.ToString();
}

Running the GuitarStore WPF program and pressing the Total Value Button displays the total value of guitar inventory, as shown in Figure 2-12.

FIGURE 2-12

SUMMARY

HQL and the IQuery interface provide some very strong capabilities — arguably the strongest capabilities of any in the NHibernate library. Reduction of database round-trips, storage of static name queries, and runtime modification of queries are but a few examples of this powerful API. You can build any query and use the dot (.) notation to take the complex SQL joins out of the equation, and just let IQuery and HQL do all the work for you. It is absolutely impressive.