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:
exec SearchStaff :LastName, :FirstName
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:
exec StaffCount :LastName, :FirstName
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:
EXEC InsertStaff ?,?,? EXEC UpdateStaff ?,?,?,? EXEC DeleteStaff ?
- 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.