I am evaluating FluentNHibernate (FNH), to see if it is suitable for a project I am working on. Disappointingly, FNH does not support Store procedures of the box. Of course, FNH is under the BSD licence, so I am sure those who are confident enough can implement this for the rest of us! This post will show how I got FNH to work with stored procedures, and can hopefully be followed as a working example.
FNH extends NHibernate, and automagically generates XML mapping files for your objects. Unfortunately, to get stored procedures to work, you need to take a step backwards, and create good old fashioned hbm.xml files, doing the mappings manually.
Firstly , let us look at the results of the stored procedure that we want to map.
| ID | enDescription | cyDescription | IsActive |
| 1 | Swansea | Abertawe | True |
| 2 | Cardiff | Caerdydd | True |
| 3 | Newport | Cas Newydd | False |
The class that will use this data is called lookup.
The code for this class is:
1 namespace Entities {
2 public class Lookup {
3 public virtual int Id { get; set; }
4 public virtual string EnDescription { get; set; }
5 public virtual string CyDescription { get; set; }
6 public virtual bool IsActive { get; set; }
7 }
8 }
This object will be used to populate a simple drop down list, so that a user can select their county.
When I started using FluentHNibernate, I wanted to totally avoid using XML mappings, so I skipped chapters 3 and 6 of Hibernate in Action.
Let’s move on to creating the mapping file.
IMPORTANT: When you add the mapping file to your project, make sure you set the Build Action to Embedded Resource!
I have created a Lookup.hbm.xml file, and the source is below:
1 <?xml version="1.0" encoding="utf-8" ?>
2 <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
3 namespace="Entities">
4 <class name="Lookup" table="dbo.sp_GetLookups" >
5 <id name="Id" column="Id">
6 <generator class="native" />
7 </id>
8 <property name="EnDescription" column="enDescription" />
9 <property name="CyDescription" column="cyDescription" />
10 <property name="IsActive" column="IsActive" />
11 <loader query-ref="dbo.sp_GetLookups"/>
12 </class>
13
14 <sql-query name="dbo.sp_GetLookups" >
15 <return alias="dbo.sp_GetLookups" class="Lookup">
16 <return-property name="Id" column="Id"/>
17 <return-property name="EnDescription" column="enDescription"/>
18 <return-property name="CyDescription" column="cyDescription"/>
19 <return-property name="IsActive" column="IsActive"/>
20 </return>
21 exec dbo.sp_GetLookups
22 </sql-query>
23 </hibernate-mapping>
To put it quite simply, lines 5 to 13 map my Lookup class to the columns in the stored procedure, while lines 16 to 20 map the results from the stored procedure my lookup class. Line 22 names the stored procedure. I am not sure if this is the best way to achieve the mappings, so any feedback would be appreciated.
Once your object is nicely mapped, you then need to update your fluent configuration. All you need to do is tell FNH to load hbmMappings from the current assembly. See the snippet below:
1 .Mappings(m => {
2 m.HbmMappings.AddFromAssembly(Assembly.GetExecutingAssembly());
3 m.FluentMappings.AddFromAssembly(Assembly.GetExecutingAssembly());
4 })
To retrieve the list of lookups, I do the following, which populates my results variable with a list of all my lookups.
1 var sessionfactory = CreateSessionFactory();
2 var session = sessionfactory.OpenSession();
3 var results = session.GetNamedQuery("dbo.sp_GetLookups").List();
And that is it, the results variable now contains the list of lookups that I can use to populate my list control.