C# 如何从 NHibernate 调用没有结果的存储过程？

How do I call a stored procedure from NHibernate that has no result?

提问by thatismatt

I have a stored procedure that logs some data, how can I call this with NHibernate?

我有一个记录一些数据的存储过程，我如何用 NHibernate 调用它？

So far I have:

到目前为止，我有：

ISession session = ....
IQuery query = session.CreateQuery("exec LogData @Time=:time @Data=:data");
query.SetDateTime("time", time);
query.SetString("data", data);
query.?????;

What should the method ?????be? Or am doing something more fundamentally wrong?

方法应该?????是什么？还是在做一些更根本的错误？

采纳答案by thatismatt

This seems to be a limitation of NHibernate, from NHibernate Documentation:

这似乎是 NHibernate 的限制，来自NHibernate 文档：

The procedure must return a result set. NHibernate will use IDbCommand.ExecuteReader() to obtain the results.

该过程必须返回一个结果集。NHibernate 将使用 IDbCommand.ExecuteReader() 来获取结果。

回答by yfeldblum

NHibernate allows you to do object-oriented programming and takes care of fetching the objects from and saving the objects to the database behind the scenes.

NHibernate 允许您进行面向对象的编程，并负责在后台从数据库中获取对象并将对象保存到数据库中。

NHibernate does not provide you with an easy API for simply executing stored procedures, because that doesn't seem to have much to do with object-oriented programming, whether fetching objects or saving them.

NHibernate 没有为您提供一个简单的 API 来简单地执行存储过程，因为这似乎与面向对象编程没有太大关系，无论是获取对象还是保存对象。

So you are doing something fundamentally wrong in attempting to use NHibernate directly to execute highly procedural code. If you want to use NHibernate, you have to tell it how executing this stored procedure behind the scenes will magically help with fetching objects from and saving objects to the database.

因此，在尝试直接使用 NHibernate 执行高度程序化的代码时，您正在做一些根本性的错误。如果你想使用 NHibernate，你必须告诉它在幕后执行这个存储过程将如何神奇地帮助从数据库中获取对象并将对象保存到数据库中。

You can:

你可以：

• Use ADO.NET directly, opening a new IDbConnectionor getting the ISession's connection, creating an IDbCommand, etc. Do this if you need a one-off approach to executing stored procedures.
• Create an NHibernate listener and configure it in the Configuration, to execute this stored procedure when certain other events are sent through the NHibernate pipeline. Only do this if this stored procedure should actually be executed every time and only when these events occur.

• 直接使用 ADO.NET，打开新连接IDbConnection或获取ISession连接，创建连接IDbCommand等。如果您需要一次性方法来执行存储过程，请执行此操作。
• 创建一个 NHibernate 侦听器并在 中配置它Configuration，以便在通过 NHibernate 管道发送某些其他事件时执行此存储过程。仅当此存储过程实际上每次都应执行且仅在这些事件发生时才执行此操作。

回答by Sathish Naga

ExecuteUpdate on SQL Query should help you.

SQL Query 上的 ExecuteUpdate 应该可以帮助你。

Sample:

样本：

ISession session = ....
IQuery query = session.CreateSQLQuery("exec LogData @Time=:time, @Data=:data");
query.SetDateTime("time", time);
query.SetString("data", data);
query.ExecuteUpdate();

回答by Pete Nelson

You can use UniqueResult to execute a stored proc that doesn't return anything. I'm using the following to call a stored proc that either inserts or updates a record to track users currently logged in to our ASP.NET MVC site.

您可以使用 UniqueResult 来执行不返回任何内容的存储过程。我正在使用以下代码调用存储过程，该过程插入或更新记录以跟踪当前登录到我们的 ASP.NET MVC 站点的用户。

IQuery query = session.GetNamedQuery("UserSession_Save");
query.SetInt32("UserID", userID);
query.SetString("CookieID", cookieID);
query.SetString("Controller", controller);
query.SetString("Action", action);

query.UniqueResult();

回答by katrash

In general, calling a procedure that does some other chores and return a result set at the end is not different than making a SELECTquery. Therefore, in the answers above, when executing the query in the last step you need to call

通常，调用执行其他一些杂务并在最后返回结果集的过程与进行SELECT查询没有什么不同。因此，在上面的答案中，在最后一步执行查询时，您需要调用

query.List<T>();

query.List<T>();

where Tis a POCO object that is defined in your code.

T在您的代码中定义的 POCO 对象在哪里。

回答by Mayur Pawar

A Stored Procedure must return a result set. The first parameter of a procedure must be an OUT that returns a result set. This is done by using a SYS_REFCURSOR type in Oracle 9i or later.Even if you do not want to return any result set you must declare first parameter in stored procedure as CURSOR_NAME OUT SYS_REFCURSOR.

存储过程必须返回结果集。过程的第一个参数必须是返回结果集的 OUT。这是通过在 Oracle 9i 或更高版本中使用 SYS_REFCURSOR 类型来完成的。即使您不想返回任何结果集，您也必须将存储过程中的第一个参数声明为 CURSOR_NAME OUT SYS_REFCURSOR。

回答by Ankush Band

Do following solutions:

执行以下解决方案：

public void Test(TestEntity TestEntity)
        {           
  IQuery query = NHSession.CreateSQLQuery("exec LogData :Time, :Data");
            query.SetParameter("Time", TestEntity.Time);
            query.SetParameter("Data", TestEntity.Data);
            object obj = query.UniqueResult();
        }