Getting to grips with NHibernate: Stored Procedures Redux

Update: Due to popular demand I have included using stored procedures for insert/update/delete

I hope this post saves someone the amount of time it took me to try and run a stored procedure using NHibernate.

Selecting from a Stored Procedure

Create Stored Procedure

The first step is to create your stored procedure, if we take a basic example:

CREATE PROCEDURE SearchStaff
	(
	@LastName VARCHAR(255) = NULL,
	@FirstName VARCHAR(255) = NULL
	)
AS
	SET NOCOUNT ON

	SELECT s.*
	FROM Staff s
	WHERE (s.LastName LIKE @LastName OR @LastName IS NULL)
	AND (s.FirstName LIKE @FirstName OR @FirstName IS NULL)
	ORDER BY FirstName, LastName

	SET NOCOUNT OFF

Note the SET NOCOUNT ON/SET NOCOUNT OFF

Create a named query

In order for NHibernate to be able to run the stored procedure, we need to create a named query, in our hbm file:

<sql-query name="StaffSearching">
	<return class="Foo.Core.Staff, Foo.Core">
	  <return-property name="Id" column="Id"/>
	  <return-property name="LastName" column="LastName"/>
	  <return-property name="FirstName" column="FirstName"/>
	  <return-property name="MiddleName" column="MiddleName"/>
	</return>
	exec SearchStaff :LastName, :FirstName
</sql-query>

In this example because the stored procedure is actually returning a staff object I set the return to the Staff class, if I was only returning something like an integer value I could use the following:

<sql-query name="TotalStaff">
    <return-scalar type="Int32" column="Count"/>
    exec StaffCount :LastName, :FirstName
</sql-query>

The name does not need to be the same name as the stored procedure.

Create the NHibernate code

// session set here

IQuery searchQuery = session.GetNamedQuery("StaffSearching")

if (!string.IsNullOrEmpty(LastName))
	searchQuery.SetString("LastName", LastName);
else
    searchQuery.SetString("LastName", null);

if (!string.IsNullOrEmpty(FirstName))
    searchQuery.SetString("FirstName", FirstName);
else
    searchQuery.SetString("FirstName", null);

IList foundStaff = searchQuery.List();

Notice that the stored procedure can deal with or without filtering, so if the fields have not been set we can simply set them to a null value and NHibernate will pass the parameters as a NULL which is what we want.It’s worth noting that the above is quite a simple example and that for the above I would not use a stored procedure and instead just use NHibernates own querying objects. The case were I used a stored procedure was for a paging routine for SQL server 2000.

Using Stored Procedure for Insert, Update & Delete

Create Stored Procedures

CREATE PROCEDURE InsertStaff
	(
	@LastName VARCHAR(255),
	@FirstName VARCHAR(255),
             @EmailAddress VARCHAR(512)
	)
AS
             INSERT INTO Staff
             (
              LastName,
              FirstName,
              EmailAddress
             )
             VALUES
             (
              @LastName,
              @FirstName,
              @Email
             )

Note: SET NOCOUNT is not set for these stored procedures

I’m not going to include the SQL for the update and delete stored procedures as I don’t think it adds any value, and is the easy part.

Update NHibernate XML Mapping

We need to tell NHibernate the SQL we want to execute for our insert/update/delete, we do this inside the class element:

<class name="MCromwell.StaffIntranet.Core.Staff, MCromwell.StaffIntranet.Core" table="Staff">
    <id name="Id" column="Id" type="Int32">
      <generator class="native" />
    </id>
    <property name="LastName" column="LastName" type="String" length="255"/>
    <property name="FirstName" column="FirstName" type="String" length="255"/>
    <property name="EmailAddress" column="EmailAddress" type="String" length="512"/>

    <sql-insert>EXEC InsertStaff ?,?,?</sql-insert>
    <sql-update>EXEC UpdateStaff ?,?,?,?</sql-update>
    <sql-delete>EXEC DeleteStaff ?</sql-delete>
</class>

Caveats

• The last parameter will be the id for the update.
• The ordering of the parameters is determined by NHibernate, so the best way to find out the ordering would be to view the generated SQL, bit pants but hay ho.

Your code will remain the same, so no changes needed there.

First codeproject article posted

I just finished posting my first article to codeproject (only taken 3 years since I joined!), it covers unit testing against a database and also provides a handy little testing library that can be used to make it a little easier you can check it out at http://www.codeproject.com/KB/cs/unittestingdblib.aspx