How to get return value from SQL Server stored procedure in nHibernate?

Question

1. Database Database: SqlServer

2. Data Access: nHibernate 1.2

Now we need to access the storage procedure using nHibernate, for example:

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

I know that I can get the list of users by IQuery, And I want to get the default return value of "1234".

Question:

• How to get this default value?
• If you cannot get it directly, can we get the value by the output parameter?

Answer 1

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 file

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

        return query[0];
    }

Answer 2

NHibernate . ADO.NET API. NHibernate Documentation , , session.Connection. -

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();
}

-

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

Answer 3

, " " , - . . /.

, nHibernate -, ADO.NET Direction, "Return". , nHibernate.

OTOH, OUTPUT RETURN .

Answer 4

( nHibernate).

.