Snowing Code

Personal notes on software development


Getting return values from an SP in NHibernate

(Publish date: 20/12/2009)

In order to get a return value from a stored procedure in NHibernate, you’ll need to, depending on the returned value, either (for singular column returned values):

  1. Create your sp in your db.
  2. Call it using the Session.CreateSQLQuery(“exec myStoredProc”)

Or (for ‘complex’, multiple column returned values):

  1. Create your sp
  2. map it.
  3. Call it.

First and foremost, remember always to use SELECT in your SP rather than RETURN.
Now, for the first case, this would look like this:

 CREATE PROCEDURE [dbo].[GetIt]
          -- Add the parameters for the stored procedure here
          
          AS
          BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          
          -- Insert statements for procedure here
          SELECT 1342
          END

This would be then called like this:

int query = session.CreateSQLQuery("exec GetIt").List<int>()[0];

As for the second case, this would look like this:

 ALTER PROCEDURE [dbo].[GetIt]
          -- Add the parameters for the stored procedure here
          
          AS
          BEGIN
          -- SET NOCOUNT ON added to prevent extra result sets from
          -- interfering with SELECT statements.
          
          -- Insert statements for procedure here
          SELECT 1342 as Id, 'MyName' as Name
          END

The mapping would then be:

<?xml version="1.0" encoding="utf-8" ?>
          <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Examples.FirstProject"  namespace="Examples.FirstProject" >
          
          <sql-query name="GetIt">
          <return alias="result">
          <return-property column="It" name="Id" />
          <return-property column="Name" name="Name" />
          </return>
          exec GetIt
          </sql-query>
          
          </hibernate-mapping>

And finally, this would be executed like this:

IList<User> users = session.GetNamedQuery("GetIt").List<User>();

Beware that the return class referenced in the sql-query mapping should be mapped itself on its own, separately!

blog comments powered by Disqus