如何从SQL Server存储过程中获取返回值?

1.Database platform: SqlServer

1。数据库平台:SqlServer

2.Data Access: nHibernate 1.2

2。1.2数据访问:nHibernate

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

现在我们需要nHibernate来访问存储过程，像这样:

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.

我知道我可以通过IQuery获得用户列表，我也想获得默认的返回值“1234”。

Question:

问题:

How to get this default return value?
如何获得这个默认的返回值?
If can't get it directly , can we get the value by output parameter?
如果不能直接得到它，我们可以通过输出参数得到值吗?

4 个解决方案

#1

this is how i do it:

我就是这样做的:

in my .hbm.xml

在我.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

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

#2

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 -

NHibernate不允许您以这种方式使用存储过程。但是它确实允许使用普通的老ADO进行调用。净API。NHibernate文档说，如果你想使用这些程序，你必须通过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();
}

You can find more details here -

#3

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.

我不知道nHibernate是怎么做的，但在ADO。NET，您将使用带有方向属性的参数设置为“Return”。在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.

OTOH通常使用输出参数返回实际有用的值，并保留错误代码的返回值，或者忽略。

#4

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

我以前做过这个(不是在nHibernate)。

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

在检索输出参数之前，必须完全处理整个记录集。

Another discussion

另一个讨论

#1