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

Question

I have a stored procedure that logs some data, how can I call this with 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?

score 56 · Answer 1 · edited Dec 12 '11 at 22:27

56

ExecuteUpdate on SQL Query should help you.

Sample:

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

edited Dec 12 '11 at 22:27

Jesse MacVicar

546
1
7
14

answered Jul 07 '09 at 19:45

Sathish Naga

1,366
2
10
18

1

how should I obtain the return value? – nicks Oct 27 '15 at 07:35

score 15 · Accepted Answer · edited Nov 17 '15 at 17:07

15

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

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

edited Nov 17 '15 at 17:07

Gonzalo.-

12,512
5
50
82

answered Jul 07 '09 at 11:09

thatismatt

9,832
10
42
54

5

I would not call it a "limitation", more like "by design". – mxmissile Jul 07 '09 at 19:47

yfeldblum · Answer 3 · 2009-07-07T17:43:10.890

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

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.

You can:

Use ADO.NET directly, opening a new IDbConnection or 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.

score 10 · Answer 4 · answered Jun 06 '11 at 14:38

10

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.

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

query.UniqueResult();

answered Jun 06 '11 at 14:38

Pete Nelson

1,328
1
13
23

Both worked for me after I made sure the stored procedure with the parameters is built correctly. – Ronen Festinger May 05 '17 at 14:41

score 0 · Answer 5 · answered Feb 20 '16 at 18:39

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

query.List<T>();

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

score 0 · Answer 6 · answered Jul 08 '16 at 11:31

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

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

6 Answers6

Linked