NHibernate-从映射的存储过程返回输出参数 [英] NHibernate - Returning an output parameter from a mapped Stored Procedure

问题描述

我有一个通过NHibernate映射的存储过程. Sproc返回结果集和输出参数，基本上是总记录数，然后是结果集本身.像这样:

I have a Stored Procedure that I have mapped via NHibernate. The Sproc returns both a resultset, and an output paramter, basically a count of the total records followed by the resultset itself. Something like this:

CREATE PROCEDURE [dbo].[mySproc]
(
    @StartRecord            INT = 1,
    @EndRecord              INT = 10,
    @TotalRecords       INT OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON

    // Do a count
    SELECT @TotalRecords = COUNT(DISTINCT x.Id)
    FROM Blah ...snip

    // Perform a query with paging
    SELECT
        x.Id,
        x.Column1
    FROM ...some really complex query that uses paging
END

我正在通过Sproc实现分页，因此需要获取记录总数.查询非常复杂，跨多个表的连接可能很慢，因此需要直接编写T-SQL进行仔细的调整和优化.

I'm implementing paging via a Sproc and thus need to get the total number of records. The query is quite complex with joins across several tables, potentially quite slow, and thus needs the care, fine tuning and optimisation from writing T-SQL directly.

我的问题是我无法获得Sproc(@TotalRecords)中的OUTPUT参数.我已经像这样映射了该Sproc:

My problem is that I can't get at the OUTPUT parameter in the Sproc (@TotalRecords). I have mapped this Sproc like so:

<class name="MyLibrary.SomeClass, MyLibrary">

    <id name="Id" type="Int32" />
    <property name="Column1" type="String" length="50" not-null="false" />

</class>

<sql-query name="mySproc">
    <return class="MyLibrary.SomeClass, MyLibrary">
        <return-property name="Id" column="Id"/>
        <return-property name="Column1" column="Column1"/>
    </return>

    EXEC mySproc
        @StartRecord = :startRecord,
        @EndRecord = :endRecord,
        @TotalRecords = 0

</sql-query>

我可以这样获得结果集:

And I can get at the resultset like so:

IList<SomeClass> records = Session.GetNamedQuery("mySproc")
    .SetParameter("startRecord", 1, NHibernateUtil.Int32)
    .SetParameter("endRecord", 20, NHibernateUtil.Int32)
    .List<SomeClass>();

但是如何获取@TotalRecords输出参数?

But how do I get the @TotalRecords output parameter?

推荐答案

似乎无法直接完成:

• http://osdir.com/ml/nhusers/2010-01/msg00794.html
• http://www.richter-web.info/Wordpress/?p= 132

• http://osdir.com/ml/nhusers/2010-01/msg00794.html
• http://www.richter-web.info/Wordpress/?p=132

这篇关于NHibernate-从映射的存储过程返回输出参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！