Unit Of Work side effects could be fixed with TransactionScope, but it is not available with MySql.Data

Question

I'm using NHibernate with MySql.Data ADO connector.

I have Dao classes with CRUD methods: Create, Update, Delete.

These methods open their own NHibernate transactions and commit them.

Now I am changing my design to use Unit Of Work pattern. The session and transaction would be opened and commited in an upper level, not in the Dao methods. So I have to remove the commits from the Dao classes.

I see several problems with this approach:

• I was catching the database specific exceptions at the commit() point. Now the commit is done in a layer above. So I will have to add all that database layer specific code to the outer layer? Things like catching FK violation or NH specific exceptions.
• I will get all the possible exceptions at the same time, having to discern from which concrete Dao class they come, and implementing code to treat them.
• I will not be able to abort the operation if one of the steps fails, because I will not know it until the final commit is done. I know that the transaction rollback will prevent data inconsistences, but it seems a performance waste to run all the following code just because I don't know that the previous lines caused an error.

I don't like this consecuences. I wish I coud use nested transactions in a transaction scope, so I could do the commit locally, but it seems that MySql.Data connector does not support them. I tried and I got exceptions:

System.InvalidOperationException : Nested transactions are not supported

Is there any workaround that allows me to get the possible exceptions in insert, update or delete operations in the moment they are done? Would session.Flush() do it? Or is there any way to use TransactionScope with MySql.Data?

Sorry if the question seems naive, but I have been googling for a while and I did not find any clear solution. About the transaction scope not working with MySql.Data, all the information I got seems a little old, and I'm not sure if it really can't be done by now.

Answer 1

Finally I decided to use Flush() any time I would have used nested transaction Commit(). It seems to work OK, I get the exceptions at that moment and I am able to treat them locally.

I know that NHibernate best practices include not to use Flush() so liberally, but I didn't find a better solution as far as nested transactions are not available with MySql and NHibernate, so this seems to be the lesser evil.