I have a process that performs several database operations. Such a process is split into 3 classes depending on which table will be updated. CLASS1 performs one insertion to TABLE1; CLASS2 and CLASS3 perform several insertions to TABLE2 and TABLE3.

I want to use transactions and I wonder if they can be nested.
My main process calls a method in CLASS1; from CLASS1 is called a method in CLASS2; from CLASS2 is called a method in CLASS3.

Can I define a transaction in every class such that TRANSACTION3 is within TRANSACTION2 and TRANSACTION2 is within TRANSACTION1?

I am sending you a test project, please specify if its functionality is similar to the one you want to be implemented.

Thank you for your fast response and the sample.
I see that I must "share" the connection among the classes.

Let's look, for example, at Class Table2. It calls method Insert of Class Table3 and if insertion in Class Table3 fails then the transaction is rolled back and control is returned to Class Table2 which executes a Commint for trans2. I suppose I must add code to avoid this situation because trans3 failed and trans2 should not commit. Am I right ?

StanislavK wrote:As PgSqlTransaction objects represent database transaction, you should share the same connection to use nested transactions.

Actually, if Trans3 fails, Trans2 will roll back too on the server side. To ensure the same behaviour in the application, please remove all catch blocks except the one in the outer transaction.

Removing catch blocks from the outer transaction means that the application will detect a failure in the last nested transaction (Trans3 in this case) and will perform a rollback here. Such a rollback will affect Trans2 and Trans1 without the need to issue a rollback on each of them. Is this correct?

The catch blocks need to be removed from inner transactions and left in the outer one. In this case, if Trans3 fails, all three transactions are rolled back on the server side, and the exception thrown is caught in Trans1 only.

StanislavK wrote:The catch blocks need to be removed from inner transactions and left in the outer one. In this case, if Trans3 fails, all three transactions are rolled back on the server side, and the exception thrown is caught in Trans1 only.

I am sorry, I misunderstood the concept. So, the catch blocks to be removed are, in the example, those in classes Table2 and Table3. If trans3 fails then trans2 automatically fails, and if trans2 has failed then trans1 will automatically fail too. What if trans3 succeeds and trans2 fails?

What if I want to rollback trans3 if the ExecuteNonQuery() returns zero records processed?
Or, lets say that trans3 succeded but the ExecuteNonQuery in trans2 returned zero records processed and I want to rollback everything. Is my scenario logical?

I have investigated the situation. Several PgSqlTransaction objects sharing the same connection represent the same transaction on the server side, thus they do not behave exactly as nested transactions. To use nested or distributed transactions, please apply the System.Transactions.TransactionScope objects:http://msdn.microsoft.com/ru-ru/library ... scope.aspx

StanislavK wrote:I have investigated the situation. Several PgSqlTransaction objects sharing the same connection represent the same transaction on the server side, thus they do not behave exactly as nested transactions. To use nested or distributed transactions, please apply the System.Transactions.TransactionScope objects:http://msdn.microsoft.com/ru-ru/library ... scope.aspx

I have read what you suggested.
Does System.Transactions.TransactionScope class support PostgreSQL or any other database?
If so then it has nothing to do with dotConnect for PostgreSQL. Is this correct?

The TransactionScope class only provides a mechanism to make a code block transactional, it does not have ability to connect and operate PostgreSQL or any other database by itself. dotConnect for PostgreSQL supports TransactionScope, making the commands executed inside the TransactionScope block transactional.

Is there any special configuration somewhere? I just added TransactionScope to my code and it seems that nothing happens because, in spite of an error gotten in a CATCH block, the record is inserted anyway. I send the code for your review.

There is an error in the line "objCarritoEspecs.CopiaEspecs(Me.Platillo.ToString, Me.Clave.ToString, pgConn)" just above the line that completes the transaction which is caught by the CATCH block. I know it because the CATCH block has code to display any problem. I suppose that, as soon as an exception is thrown, the flow of the code goes to the catch block and the complete transaction line should not be executed; nevertheless, the record is inserted. I even commented the objTransScope.Complete() line so it never executes and the record is inserted anyway.

Another test I made was to throw an exception (purposely) just above the objTransScope.Complete() line. I also added a display message (after the thrown exception) that is not executed and this proves that the code is routed to the CATCH block.

Why is the record added anyway?
I made sure the DTC service is running, in fact, it starts automatically.