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:


	
	  
	  
	  
	  
	
	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 ?

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.

Advertisements

8 thoughts on “Getting to grips with NHibernate: Stored Procedures Redux

  1. Thanks for this, but where would the named query xml go in my mapping file? Does it have to be in the related mapping file to the collection you’re returning (Staff.hbm)? As from the usage of it it doesn’t seem at all tied to any one specific instance.

    Thanks,

    Dan

  2. I believe that there is no constraint in having the named queries in the same mapping file as the collection your are returning I added them to Staff.hbm.xml because it made most sense to stick them there however for other cases it may be worth having a separate mapping file to store named queries i.e. StoredProcedures.hbm.xml

  3. I suggest you should make it complete, you started a very, very good post. Ive already seen this example above that you gave me and to be honest its not pretty clear. Yours is much better. Please make a sample of update, insert and delete versions. It will be very helpful for nHibernate newbies like me! Thanks in advance! 🙂

  4. Hey Michael, thanks for the post. Is there any way that an insert/update/delete stored proc can be executed with the SET NOCOUNT ON statement? I’ve been testing calling an update stored proc from NHib where the SET NOCOUNT ON is used and NHib complains that there was an unexpected row count from the update:

    Batch update returned unexpected row count from update; actual row count: -1; expected: 1

    Sure enough, removing the SET NOCOUNT ON means that NHib is happy, is it just the case that this is the only way to do it?

Comments are closed.