Have you ever had a time when you were working with an ICriteria to return a specific group of data and found that it just wasn't returning what you wanted and you weren't sure why. In the past when I've had to troubleshoot troublesome SQL queries I would open up SQL Server Profiler to see what might be happening behind the scenes that could be messing up what is being returned. This can be a little bit troublesome when dealing with NHibernate. NHibernate generates the SQL in such a fashion that it becomes rather difficult and time consuming to make these queries readable after looking at their output withing SQL Profiler. Here is an example of a query from profiler, generated by NHibernate:

SELECT this_.id as id49_2_, this_.part_number as part2_49_2_, this_.quantity as quantity49_2_, this_.manufacturer as manufact4_49_2_, this_.part_name as part5_49_2_, this_.description as descript6_49_2_, this_.requested_date as requested7_49_2_, this_.notes as notes49_2_, this_.status as status49_2_, this_.cancelled_date as cancelled10_49_2_, this_.location_user_id as location11_49_2_, this_.customer_id as customer12_49_2_, locationus3_.person_id as person1_40_0_, locationus3_.name_first as name2_40_0_, locationus3_.name_middle as name3_40_0_, locationus3_.name_last as name4_40_0_, locationus3_.name_suffix as name5_40_0_, locationus3_.phone_number as phone6_40_0_, locationus3_.fax_number as fax7_40_0_, locationus3_.email_address as email8_40_0_, locationus3_.job_title as job9_40_0_, locationus3_1_.password as password41_0_, locationus3_1_.is_active as is3_41_0_, locationus3_1_.username as username41_0_, locationus3_1_.location_id as location5_41_0_, locationus3_1_.shopping_cart_id as shopping6_41_0_, customer1_.customer_id as customer1_72_1_, customer1_.name as name72_1_, customer1_.created_by as created3_72_1_, customer1_.dt_created as dt4_72_1_, customer1_.po_prefix as po5_72_1_, customer1_.is_active as is6_72_1_, customer1_.po_required as po7_72_1_, customer1_.enforce_order_multiples as enforce8_72_1_, customer1_.order_alert_recipient_id as order9_72_1_, customer1_.order_alert_dollar_amount as order10_72_1_, customer1_.show_on_hand as show11_72_1_, customer1_.is_central_billing as is12_72_1_, customer1_.display_promatch_number as display13_72_1_, customer1_.site_id as site14_72_1_ FROM quote_requests this_ left outer join people locationus3_ on this_.location_user_id=locationus3_.person_id left outer join dbs_users locationus3_1_ on locationus3_.person_id=locationus3_1_.person_id inner join customers customer1_ on this_.customer_id=customer1_.customer_id WHERE customer1_.site_id = @p0 and (this_.status = @p1 or (this_.status = @p2 and this_.cancelled_date < @p3)) and this_.status = @p4

My question was, what in the world is this query actually doing? In comes Instant SQL Formatter. Once you past the above generated SQL into Instant SQL Formatter and press 'Format SQL' it gives you this:

SELECT this_.id                             AS id49_2_,
       this_.part_number                    AS part2_49_2_,
       this_.quantity                       AS quantity49_2_,
       this_.manufacturer                   AS manufact4_49_2_,
       this_.part_name                      AS part5_49_2_,
       this_.DESCRIPTION                    AS descript6_49_2_,
       this_.requested_date                 AS requested7_49_2_,
       this_.notes                          AS notes49_2_,
       this_.status                         AS status49_2_,
       this_.cancelled_date                 AS cancelled10_49_2_,
       this_.location_user_id               AS location11_49_2_,
       this_.customer_id                    AS customer12_49_2_,
       locationus3_.person_id               AS person1_40_0_,
       locationus3_.name_first              AS name2_40_0_,
       locationus3_.name_middle             AS name3_40_0_,
       locationus3_.name_last               AS name4_40_0_,
       locationus3_.name_suffix             AS name5_40_0_,
       locationus3_.phone_number            AS phone6_40_0_,
       locationus3_.fax_number              AS fax7_40_0_,
       locationus3_.email_address           AS email8_40_0_,
       locationus3_.job_title               AS job9_40_0_,
       locationus3_1_.password              AS password41_0_,
       locationus3_1_.is_active             AS is3_41_0_,
       locationus3_1_.username              AS username41_0_,
       locationus3_1_.location_id           AS location5_41_0_,
       locationus3_1_.shopping_cart_id      AS shopping6_41_0_,
       customer1_.customer_id               AS customer1_72_1_,
       customer1_.name                      AS name72_1_,
       customer1_.created_by                AS created3_72_1_,
       customer1_.dt_created                AS dt4_72_1_,
       customer1_.po_prefix                 AS po5_72_1_,
       customer1_.is_active                 AS is6_72_1_,
       customer1_.po_required               AS po7_72_1_,
       customer1_.enforce_order_multiples   AS enforce8_72_1_,
       customer1_.order_alert_recipient_id  AS order9_72_1_,
       customer1_.order_alert_dollar_amount AS order10_72_1_,
       customer1_.show_on_hand              AS show11_72_1_,
       customer1_.is_central_billing        AS is12_72_1_,
       customer1_.display_promatch_number   AS display13_72_1_,
       customer1_.site_id                   AS site14_72_1_
FROM   quote_requests this_
       LEFT OUTER JOIN people locationus3_
         ON this_.location_user_id = locationus3_.person_id
       LEFT OUTER JOIN dbs_users locationus3_1_
         ON locationus3_.person_id = locationus3_1_.person_id
       INNER JOIN customers customer1_
         ON this_.customer_id = customer1_.customer_id
WHERE  customer1_.site_id = @p0
       AND (this_.status = @p1
             OR (this_.status = @p2
                 AND this_.cancelled_date < @p3))
       AND this_.status = @p4

Then, all you have to do is DECLARE your parameters, set their values and run the query and view the output.

 This is going to save me a ton of time during development and troubleshooting troublesome queries.

Man time flies! It's almost August, and that means the Summer of NHibernate is coming to an end! Well maybe not. But in case it does, you have to take the time to download and watch the series of videos covering all aspects of NHibernate. (code samples too!)

1. Setup and Basic Usage Pattern
2. Exploring Query Methods and Syntaxes
3. Exploring Query methods and Syntaxes (cont.)
4. Exploring INSERT, UPDATE, and DELETE Semantics
5. Introducing Transactions and Concurrency
6. Modeling Foreign-Key Relationships in NHibernate

If you know nothing about NHibernate - watch them. If you think you know everything about NHibernate - watch them.

Recently I ran into a situation with NHibernate where I needed to call a stored procedure. Ok. That should be easy right? Well, not so much. I mean it wasn't rocket science, but it didn't feel like a first class citizen in the NHibernate framework, and maybe it's not supposed to be...I don't know.

So I googled it, of course, and I found a few related blog posts referencing the different ways of going about this. The problems started when my stored procedure needed to return multiple rows and not a scalar value. By problem I mean I had to do another Google search. :)

I found the following solution:

public IList<IPityTheFoo> FindByFoo(string foo)
        {
            return HibernateTemplate.SessionFactory.GetCurrentSession()
                .GetNamedQuery("StoredProcedureName")
                .SetParameter("Foo", foo)
                .List<IPityTheFoo>();
        }

The general idea here is that you need to call GetNamedQuery which expects the name of the query as defined by your Hibernate Mapping File:

<sql-query name="StoredProcedureName">
        <return class="IPityTheFoo">
            <return-property name="Foo" column="Foo"/>
        </return>
        exec [dbo].[StoredProcedureName] ?
    </sql-query>

The Spring.NET HibernateTemplate exposes the current session through the SessionFactory.GetCurrentSession(). To return the collection of your objects simpoly call List<T>().

I've been working with NHibernate and Spring.NET recently on a project and have had to struggle with learning these new technologies of which I had no experience with before. I think I've reached that point where I 'get it' and now I'm able to implement real business value, which is the general purpose of all frameworks.

Taking these frameworks a step further, S#arp Architecture, pronounced 'Sharp' Architecture, makes working with NHibernate, Spring.NET, and ASP.NET MVC even easier! One thing S#arp Architecture provides is a Generic DAO which enables your custom DAO's (Customer, Order, etc.) to remain very light, as they only need provide implementation code when doing more than Save, Update, or Delete. The example below shows a CustomDAO which can Save, Update, SaveOrUpdate, Delete, etc... The only "special" method is the FindByCountry method.

S#arp Architecture Generic DAO in action.

 1 public class CustomerDao : GenericDaoWithTypedId<Customer, string>, ICustomerDao
 2     {
 3         public List<Customer> FindByCountry(string countryName)
 4         {
 5             ICriteria criteria = Session.CreateCriteria(typeof(Customer))
 6                 .Add(Expression.Eq("Country", countryName));
 7 
 8             return criteria.List<Customer>() as List<Customer>;
 9         }
10     }