How to get the return value from a SQL Server Stored procedure into nHibernate?

Question

1.Database platform: SqlServer

2.Data Access: nHibernate 1.2

Now we need access the store procedure by nHibernate,like this:

ALTER PROCEDURE TestProc() 
 AS 
  BEGIN 
    Select * From User 
    Return 1234 
  END

I know I can get the User List by IQuery, And I want to get the default return value "1234" too.

Question:

1. How to get this default return value?
2. If can't get it directly , can we get the value by output parameter?

Answer 1

First of all, that's not called a "default return value" anywhere I've ever seen. It's just the return value. It's usually used to return a success / error status.

I don't know how nHibernate does things, but in ADO.NET, you'd use a parameter with the Direction property set to "Return". Maybe there's an equivalent in nHibernate.

OTOH, it would be more usual to use an OUTPUT parameter to return an actual useful value, and keep the RETURN value for error codes, or for being ignored.

Answer 2

I've done this before (not in nHibernate).

You must completely process the entire recordset before retrieving the output parameters.

Another discussion

Answer 3

NHibernate does not let you use stored procedures in this manner. But it does allow a way to make calls using the plain old ADO.NET API. The NHibernate Documentation says that if you want to use these procedures you have to execute them via session.Connection. Here's an example -

ISession session = sessionFactory.GetSession();

using(ITransaction transaction = session.BeginTransaction())
{
   IDbCommand command = new SqlCommand();
   command.Connection = session.Connection;

   // Enlist IDbCommand into the NHibernate transaction
   transaction.Enlist(command);

   command.CommandType = CommandType.StoredProcedure;
   command.CommandText = "dbo.SetUserInfo";

   // Set input parameters
   var parm = new SqlParameter("@UserID", SqlDbType.Int);
   parm.Value = 12345;
   command.Parameters.Add(parm); 

   // Set output parameter
   var outputParameter = new SqlParameter("@Quantity", SqlDbType.Int);
   outputParameter.Direction = ParameterDirection.Output;
   command.Parameters.Add(outputParameter); 

   // Set a return value
   var returnParameter = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
   returnParameter.Direction = ParameterDirection.ReturnValue;
   command.Parameters.Add(returnParameter);

   // Execute the stored procedure
   command.ExecuteNonQuery();
}

You can find more details here -

http://refactoringaspnet.blogspot.com/2009/06/how-to-use-legacy-stored-procedures-in.html

Answer 4

this is how i do it:

in my .hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="DocumentManagement.Data"  namespace="DocumentManagement.Data.Repositories" >

<sql-query name="GetDocument">    
<return class="DocumentManagement.Core.Models.PhysicalDocument, DocumentManagement.Core">      
    <return-property column="DocId" name="Id" />      
    <return-property column="Filepath" name="Filepath" />
    <return-property column="Filename" name="Filename" />
</return>
exec Investor_GetDocumentById :userId, :docId
</sql-query>

</hibernate-mapping>

in my repository.cs

    public PhysicalDocument GetDocumentPath(int userId, int docId)
    {
        var query = Session.GetNamedQuery("GetDocument")
            .SetInt32("userId", userId)
            .SetInt32("docId", docId).List<PhysicalDocument>();

        return query[0];
    }