Nhibernate stored procedures output parameters

[Asier]

Hello

Is true that Nhibernate doesn’t support stored procedures output
parameters?
Thanks.

[Diego Mijelshon]

Yes. And for good reasons.

   Diego

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Mohamed Meligy]

No offense, but, specifically in the ALT.NET world, and more specifically in NHibernate, isn't it too frequent to hear "this is not supported" / "this is not the way it works" / ... followed by the other sentence "FOR GOOD REASONS" or something equivalent?

Was just thinking ...

Regards,

--
Mohamed Meligy
Senior Developer, Team Lead Backup (.Net Technologies - TDG - Applications)
Injazat Data Systems
P.O. Box: 8230 Abu Dhabi, UAE.

Phone:  +971 2 6992700
Direct:   +971 2 4045385
Mobile:  +971 50 2623624, +971 55 2017 621

E-mail: eng.m...@gmail.com
Weblog: http://weblogs.asp.net/meligy

[Fabio Maulo]

Mohamed, try to complete the answer with more context instead say what Diego should explain ;)

btw I'm having some doubts about Diego's answer basically because this test should fail

[Test]

public void ScalarStoredProcedure()

{

ISession s = OpenSession();

IQuery namedQuery = s.GetNamedQuery("simpleScalar");

namedQuery.SetInt64("number", 43L);

IList list = namedQuery.List();

object[] o = (object[])list[0];

Assert.AreEqual(o[0], "getAll");

Assert.AreEqual(o[1], 43L);

s.Close();

}

but with this mapping

<sql-query name="simpleScalar">

<return-scalar column="name" type="string"/>

<return-scalar column="value" type="long"/>

exec simpleScalar :number

</sql-query>

it is working

2010/1/30 Mohamed Meligy <eng.m...@gmail.com>

--
Fabio Maulo

[Diego Mijelshon]

Maybe. Originally, my answer was going to be just "yes", but that might give him/her the idea that support for output parameters is something missing.

Anyway, to bring this back on topic: one of NHibernate major features (and selling points) is being able to create DBMS-agnostic code, although it does have some degree of support for SQL and stored procedures, which helps when working with legacy databases.

But if the NH devs wasted their time in supporting every possible ADO.NET construct, they'd be sending the message that it's OK to build an architecture based on stored procedures in 2010.

   Diego

P.S.: of course that last paragraph represents my personal opinion on this matter.

[Diego Mijelshon]

That a SP returning a single value, not an output parameter...

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

   Diego

[Fabio Maulo]

Yes you are right... now is clear which is the good reason ;)  perhaps the same because we avoid the usage of SP LOL

2010/1/30 Diego Mijelshon <di...@mijelshon.com.ar>

--
Fabio Maulo

[Diego Mijelshon]

In fact, that same section specifies a workaround for using stored procedures with output parameters:

"[...]If you still want to use these procedures you have to execute them via session.Connection."

So, it's as easy as:

  var command = session.Connection.CreateCommand();

  var parameter = command.CreateParameter();

  parameter.Direction = ParameterDirection.Output;

  parameter.DbType = ...

  parameter.ParameterName = ...

  command.Parameters.Add(parameter);

  transaction.Enlist(command);

  command.ExecuteNonQuery();

It's the kind of thing you build a small helper for. It definitely doesn't belong in the NHibernate codebase.

   Diego

2010/1/30 Fabio Maulo <fabio...@gmail.com>

[Asier]

Hi to all.

The reason for my question was that, I am working with a legacy DB
(IBM DB2). There is an access restriction, and we can only use stored
procedures. I thought NHibernate was a good approach for this but
maybe no.

[Fabio Maulo]

you can do it using CreateSQLQuery (probably but I'm not so sure).

Legacy-DB + strongly DBA restriction = legacy DAL = big PITA

2010/2/1 Asier <asierro...@gmail.com>

--

You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

--
Fabio Maulo

[Mohamed Meligy]

I hate reading (and hence writing) those kinds of replies, but since Fabio already mentioned it as:

Legacy-DB + strongly DBA restriction = legacy DAL = big PITA

I think his post is into more details about the same point really (worth reading):
http://fabiomaulo.blogspot.com/2009/06/database-eliot-ness-of-it.html

However, in the case when restriction means SPROCs with OUT parameters, I think you have more options than just "legacy DAL".

LLBLGen Pro ORM for example tries pretty much to make SPROCs a relatively easy task.
Also, LINQ To SQL works with output parameters, but of course we still have the DB2 restriction so don't count this one.
Entity Framework (.NET 3.5) has some workarounds to work with output parameters (sample), Entity Framework (.NET 4.0. pre-release) works naitvely as I understand.

The point I'm trying to make is that yes, it's great if you support what you believe is "right",but to expect that everyone who does not follow this would naturally suffer and this would be the normal way of the world, is not an accurate point of view, although so common in ALT.NET kind of lists (which I really like and respect, same as I like and respect all the guys in this thread).

Regards,

--
Mohamed Meligy
Senior Developer, Team Lead Backup (.Net Technologies - TDG - Applications)
Injazat Data Systems
P.O. Box: 8230 Abu Dhabi, UAE.

Phone:  +971 2 6992700
Direct:   +971 2 4045385
Mobile:  +971 50 2623624, +971 55 2017 621

E-mail: eng.m...@gmail.com
Weblog: http://weblogs.asp.net/meligy

[Fabio Maulo]

again, Mohamed ? another ALT.NET spirit lesson ?

The way to solve an issue right now is different than the way to solve the same issue in the future.

If your intention is improve NH functionalities and increase features a good place for your proposal is our issue tracker

http://jira.nhforge.org/ 

If you have a patch it will be always welcome (never if the patch does not have a test).

If you want talk about some special proposal our development-list is open: 

http://groups.google.com/group/nhibernate-development

2010/2/1 Mohamed Meligy <eng.m...@gmail.com>

--
Fabio Maulo

[devonl]

Sorry to come in so late to the game, but I thought it worth while to
take a contrarian view and state that NH actually does support stored
procs with output parameters-- maybe not in the way you imagine.

Putting all arguments aside about whether, philosophically, this is a
"good practice," you can use standard ADO.NET commands that are
wrapped in NH sessions and transactions like so:

using (ITransaction transaction = Session.BeginTransaction())
{
// build an ADO command
IDbCommand command = new SqlCommand();

// give it the connection we're using from NH
command.Connection = Session.Connection;

// tell the NH transaction to use the ADO command
transaction.Enlist(command);

// set some command properties
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.spYourStoreProcNameGoesHere";

// set input params
var param = new SqlParameter("@InputParam", SqlDbType.Int);
param.Value = inputParam;
command.Parameters.Add(param);

//... add any other input params

// set up the output param
var outputParam = new SqlParameter("@OutputParam", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
command.Parameters.Add(outputParam);

// execute the command
command.ExecuteNonQuery();

// grab the return value casting it appropriately
return (int)((SqlParameter)command.Parameters
["@OutputParam"]).Value);
}

Now, it _is_ true that using the ADO commands this way can create some
issues. Notably, if you're using caching, and the stored proc changes
data in any way in the database, then the cache is invalid and you'll
need to handle that. However, wrapping the ADO command in the NH
session does give you access to the overall NH ISession
infrastructure, including any interceptors that you have registered.

Sorry for the long post, but as you can see, NH does support the use
of stored procs that return output parameters.

Although I also agree that stored procs are evil, sometimes we do what
we want, but most of the time we do what we must. As an example, I
have just taken up a new position with a company that has over 500K
lines of code in stored procs. We _must_ continue to use stored procs
as we begin our transition to more current best practices for software
architecture and development that we _want_.

Regards,

-devon