NHibernate and Stored Procedures in C#

I was recently trying and failing to set up NHibernate (v1.2) in an ASP.NET project. The aim was to execute a stored procedure and return the results, but it took several iterations for me to end up with a working solution. In this post I am simply trying to put the required code in one place, in the hope that the snippets may be useful in guiding someone else through the same process. As it is kind’ve the first time I have had to play with NHibernate, there is a good chance that this solution is sub-optimal and, as such, I am open to suggestions on how it could be improved!

There are four code snippets that I required:

  • The stored procedure that I wanted to execute
  • The C# class representation of the results of the procedure
  • The XML mapping file that allows NHibernate to map from C# to the procedure and back again
  • The C# code used to run the stored procedure

The Stored Procedure

The procedure was designed to take a UserId and, from this, go and grab some profile data for that user. Simple, right? We just need to do a join first, because the user’s site ID (the one we have access to) is not the same as the user’s forum ID.

I’d like to make a shout out to Format SQL for its help with, well, formatting the above SQL!


The C# Class

This is just the class representation of the results we expect to get from the stored procedure. NHibernate requires a virtual property for each column of data, and these properties must be called the same as the column headers. You will also need to ensure that there is a public or protected parameterless constructor.


The NHibernate Mapping File

This is the XML I wrote in order to make NHibernate a) aware of the stored procedure, and b) aware of the expected results of the procedure.


Calling the Stored Procedure

Finally, to bring it all together, the C# code that I used in order to execute the stored procedure!

There are a number of ‘Set’ methods (i.e. SetInt32) that allow you specify values for any parameters in the procedure. The AliasToBean method is then required to map the returned scalars (as specified in the XML) to the correct C# class.