Getting return values from an SP in NHibernate

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!

Advertisements
Explore posts in the same categories: nhibernate

Tags: ,

You can comment below, or link to this permanent URL from your own site.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: