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

1.Database platform: SqlServer

2.Data Access: nHibernate 1.2

  • T-SQL Query Gives Different Results when declaring integer vs calculating in-query
  • provider cannot be found error in python connecting to SQL Server
  • SQL QUery, Insert, Union and Join
  • Convert varchar date - how can I make it an actual DATE input rather than a Char one?
  • Remote connect to SQL Server Standard Edition from PHP/FreeBSD
  • How to set up a complex many to many relationship in entity framework 4 code first
  • 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?

    4 Solutions collect form web for “How to get the return value from a SQL Server Stored procedure into nHibernate?”

    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];
        }
    

    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

    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.

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

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

    Another discussion

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.