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?

Answer 1

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.

Answer 2

This seems to be a limitation of NHibernate, from http://nhforge.org/doc/nh/en/index.html#querysql-limits-storedprocedures:

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

Answer 3

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

Answer 4

I would post a comment, but I can't post comments, so here it is: Why would you consider the fact that NHibernate only supports calling stored procedures that return a result set to not be a "limitation". In my opinion, it is a limitation because it seems like it adds overhead to open a data reader when it is not necessary.