Stored Procedures With Fluent NHibernate


Programming in Ruby tutorials and examples

Stored Procedures With Fluent NHibernate

MaxCDN Content Delivery Network

Download The Code

Ok the title of the post may be a little misleading because technically you cannot fluently map stored procedures, but you can still use the NHibernate configuration files to complement your fluent mappings with named query’s (calls on stored procedures).  A while ago I investigated how much work would be involved when making a migration from an ADO.NET to a NHibernate data access implementation whilst slowly swapping out stored procedures to use LINQ 2 NHibernate. Although this example only shows a few simple read operation’s even more complex operations are relatively straight forwards to migrate.

Here is an example of how do fluently map a class as well as map stored procedures.

Below is a method that creates an ISessionFactory  you can see the usual call to create the mappings fluently from the specified assembly, and you can see we can also add Hbm Mappings for the QuestionGroup class.

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using NHibernate;
using Domain;

namespace Data.Helpers
{
  public class SessionFactory
  {
    public ISessionFactory CreateSessionFactory(string ConnectionString)
    {
      return Fluently.Configure()
        .Database(MsSqlConfiguration.MsSql2005
            .ConnectionString(
              C => C.Is(ConnectionString)))
        .Mappings(M => M.FluentMappings.AddFromAssemblyOf<SessionFactory>())
        .Mappings(M => M.HbmMappings.AddClasses(typeof(QuestionGroup)))
        .BuildSessionFactory();
    }       
  }
}

I have a QuestionGroupMap class that takes care of mapping the properties and entities of the QuestionGroup class but I have an additional QuestionGroup.hbm file which defines two stored procedures which then can be called by NHibernate.

using Domain;
using FluentNHibernate.Mapping;

namespace Data.Mappings
{
  public class QuestionGroupMap : ClassMap<QuestionGroup>
  {
    public QuestionGroupMap()
    {
      Id(X => X.QuestionGroupId).GeneratedBy.Identity();
      Map(X => X.QuestionGroupDescription);
      Map(X => X.IsActive);

      HasMany(X => X.Questions).Cascade.All().Inverse().KeyColumnNames.Add("QuestionGroupId");
    }
  }
}
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
    <sql-query name="Select_All_Question_Groups">
        <return alias="QuestionGroup" class="Domain.QuestionGroup, Domain">
            <return-property name="QuestionGroupId" column="QuestionGroupId"></return-property>
            <return-property name="QuestionGroupDescription" column="QuestionGroupDescription"></return-property>
            <return-property name="IsActive" column="IsActive"></return-property>
        </return>
        exec Select_All_Question_Groups
    </sql-query>
    <sql-query name="Select_Question_Group">
        <return alias="QuestionGroup" class="Domain.QuestionGroup, Domain"></return>
        exec Select_Question_Group @QuestionGroupId=:QuestionGroupId
    </sql-query>
</hibernate-mapping>

Below shows how the stored procedures are then called in the data access layer (I have added the commented out LINQ 2 NHibernate implementation as well for reference). This is great because as soon as you swap out the stored procedure and use LINQ you get testability for free (see my other post ).

        
public IQueryable<QuestionGroup> SelectAll()
{
  //return from questionGroup in Session.Linq<QuestionGroup>()
  //       select questionGroup;

  return Session
    .GetNamedQuery("Select_All_Question_Groups")
    .List<QuestionGroup>().AsQueryable();
}

Download The Code (you will need VS2008 Standard / Pro / Team to run this or if your über cool use VS2010 beta).