NHibernate 1.2 Beta: Stored Procedure Sample

The NHibernate 1.2 beta has been available for download since the end of September.  Two of the big features are support for .NET generic collections and support for stored procedures.  I decided to give these features a test drive and I posted my code here.  (FYI, it’s written using Visual Studio 2005 Team Suite and uses VSTS unit tests.) 

The sample uses some very simple mappings: a Restaurant and a collection (“bag“) of Tables.  And I’m using the “component“ mapping to represent the Address of the Restaurant. 

First problem:  “Test method NHibernate1Dot2Sample.UnitTests.RestaurantFixture.CreateRestaurant threw exception:  NHibernate.InvalidProxyTypeException: Type ‘NHibernate1Dot2Sample.Business.DomainModel.Restaurant’ cannot be specified as proxy: method get_Name should be virtual.”

Now that’s totally weird because the Name property is just a simple string. 

Answer: add default-lazy=“true“ to the “hibernate-mapping“ element.  I guess NHibernate was trying to lazy load the Name property but wasn’t having any luck because it’s a scalar.

Then I found a new mapping called “database-object“ that lets you create/drop objects in the database.  This is a great new feature that’s a lot better than the hack I’ve used to use — adding SQL VIEW create scripts as named queries (”sql-query”) in the mapping files and then having a custom SchemaExport method that manually runs all those special queries using the ISession’s DbConnection. 

The following sample “database-object“ mapping creates a stored procedure called RestaurantTable_GetById:
<database-object><br /> <create><br /> CREATE PROCEDURE RestaurantTable_GetById<br /> @tableId int<br /> AS<br /> SELECT Id, TableNumber, Covers, RestaurantId<br /> FROM<br /> RestaurantTable<br /> WHERE<br /> Id = @tableId<br /> </create><br /> <drop><br /> DROP PROCEDURE RestaurantTable_GetById<br /> </drop><br /> </database-object><br />

My next issue was how to initialize dependent objects when querying with a stored procedure.  For example, getting a restaurnt Table object by Id and initializing the table.Restaurant property.  The answer is to use the “return-property/return-column“ mapping.  In the sample below, the “return-property“ for the Restaurant property feeds the return-column into the key of the “many-to-one“ association from Table to Restaurant.

The following sample hooks the restaurant Table class to the RestaurantTable_GetById stored procedure:
<sql-query name=”GetTableById”><br /> <return class=”Table”><br /> <return-property name=”Id” column=”Id”/><br /> <return-property name=”Number” column=”TableNumber”/><br /> <return-property name=”Covers” column=”Covers”/><br /> <return-property name=”Restaurant” ><br /> <return-column name=”RestaurantId”/><br /> </return-property><br /> </return><br /> exec RestaurantTable_GetById :tableId<br /> </sql-query><br />

-Ben