It finally occurred to me recently that there's got to be a better way to write data access layers.  Though it's done wonders for my words per minute, it just seems horifically inefficient to have to manually set up types to encapsulate query results.  And worse than that are the magic words that are necessary to define parameters for stored procedures.  This stuff drives me nuts.  There is no reason that I should have to hard-code names and types of parameters, not to mention the general evilness of constants in general.  Sure, we all use them from time to time, but our database tables shift like Leopold Bloom's bowels after his burnt kidney breakfast, and these damn parameters have caused at least 50% of the 83,467 hours we spend on maintenance every year.

I reflected (punny!) on this madness as I inserted a snippet for a DB2 connection, command, and DataReader for the 187th time this month.  I stopped, counted to ten, and finally started researching Object Relational Mappers.  As I read through several opinions on the topics, including a novelette, I found a Stack Overflow post that I ended up spending a lot of time reading, since the answers were quite thoughtful and well-documented.  The original question's bottom line is that we don't really need entities and ORMs; we can just use stored procedure instead.  The person who asked the question did a good job presenting his point, and eloquently rebutted the wealth of responses, but I just kept thinking about the fact that at some point you still have to set up the damn DbCommand object that calls the stored procedure.  So, all apologies to the questioner, I headed over to the NHibernate website and started learning.

One of the caveats in my situation (I know we all have caveats) is that we have three enterprise databases systems: Oracle 10g, SQL Server 2005, and DB2 (ISeries AS400).  Of course, part of the obvious beauty of an ORM is that you should be able to switch between these without a ton of work.  No big deal, then.  Except that the application that I specifically wanted to attempt to use NHibernate with was for ISeries DB2.  Of the three, DB2 was the only database I'd never worked with before I started at my current position, and since it's kind of the pariah of the relational database world, I was pleasantly surprised to find that, allegedly, NHibernate supports it.  

After downloading everything I set about finding a good tutorial, which I only found on the NHibernate site via Google.  As a matter of fact, though I'm sure I'll find it to be a good reference when I get a little more seasoned, the NHibernate API Reference is quite dense, and I've found it difficult to navigate and not very intuitively laid out.  I did find several tutorials/examples, a few of which are:

• NHibernate Best Practices with ASP.NET (Billy McCafferty) - Overall, I think this will be very, very useful for me down the road.  Specifically, the project I'm working on won't have a UI layer.  And I'll admit, this article was really long and I didn't have time to read it all. 
• NHibernate By Example (Dragos Nuta, I think.  He at least wrote the documentation.) - I've used this as a reference several times already.  It's a very simple, straightforward DAL that shows basic mappings and criteria queries.
• NHibernate FAQ (Gabriel Schenker) - Several blog posts in a series that starts with setting up your system for NHibernating and progresses onward through several scenarios.  All tutorials strictly adhere to TDD.

Partly because I stumbled upon it first by some magic of Google search strings, and partly because I've been wanting to give NUnit a spin (he doesn't advocate any particular testing framework, just uses NUnit for the tutorial), I started working with Schenker's FAQ/Tutorial, and have stuck with it thus far.  I find his approach to structuring the domain and repository classes quite easy to follow and I like the way he organizes his solution.  Sure, I'm translating everything from C# to VB, but that would have been the case with any tutorial found, because VB coders are more scarce by the second, and code examples using VB are practically extinct.

Truth be told, I started this post about a month ago, and various extracurricular activities (namely, my kids) have kept me from finishing it.  But I intend to follow through with more posts as I code and learn.  So if anyone is out there trying to use NHibernate with DB2, I'm hoping I'll have more information for us all to discuss as my project evolves.