[Sorry for posting in wrong forum before]

Hi,

I have a couple of problems and I've been banging my head against the wall. I need to do some simple operations that I think any business application can have...nothing out of the ordinary.

When I create a new customer, I have a custom method to obtain a numerator (it's not the ID and I don't want to use generator). Is it not possible to just execute a statement? Soemthing like "update x set y=y+1"? Forget even returning the result. Can't I just execute a direct SQL statement? I just don't see myself having to create a numerator class, the class mapping and do things like:

Numerator numerator = new Numerator();
int value = numerator.GetNextNumerator();
session.Update(numerator);

where GetNextNumerator gets a ID and then increments it. It's just an overkill.

My other problem is with transactions. If I have a sequence generator in the ID, TransactionScope fails with cannot open data connection. I have to use Begin/End Transaction.

Also, even if I rollback my transaction, my generator in Firebird is still updated.

I'm really close to throwing in the towel with NH and I don't want to :(

Just executing a SQL query can be done using the CreateSQLQuery method on the ISession.

I don't use this for UPDATES/INSErTS so im not quit sure how to handle returntypes (as i recall nHibernate demand an returntype) so my guess would be to use an int and perhabs it will hold the rows affected, try it out. Something like this:



Perhabs some of the cleverer heads in here can clarify why a type is neccesary.

Those are required I think because Create SQL Query is intended for, well, queries, not NonQueries.

You could also go a little deeper and grab the connection from the session (or directly from the factory ConnectionProvider) and build your own IDbCommand:



As far as the sequence being incremented even after rollback: I can't speak with authority in re Firebird, but I think that might be a DB level "issue". The same thing happens in SQL with identity columns: rollbacks produce "skipped" identifiers. But really, this should never be a problem, since the identifier (and even the relationships between identifiers) is not intended to carry any meaning.