Automatically rollback to Usage area with C # SQL Server calls?

Question

When you create “use” blocks for your SQL connection, transaction, and command, it is well known that the connection, transaction, or command that the use block is associated with is located on its own after you leave the use block.

If an exception occurs in one of these blocks, for example, in a command block - will the transaction be rolled back by itself or should developers try to capture the “use” block inside the block and add a rollback transaction operator to the transaction?

Answer 1

This is not guaranteed. The Dispose(bool) SqlTransaction will actually roll back conditionally:

 // System.Data.SqlClient.SqlTransaction protected override void Dispose(bool disposing) { if (disposing) { SNIHandle target = null; RuntimeHelpers.PrepareConstrainedRegions(); try { target = SqlInternalConnection.GetBestEffortCleanupTarget(this._connection); if (!this.IsZombied && !this.IsYukonPartialZombie) { this._internalTransaction.Dispose(); } } catch (OutOfMemoryException e) { this._connection.Abort(e); throw; } catch (StackOverflowException e2) { this._connection.Abort(e2); throw; } catch (ThreadAbortException e3) { this._connection.Abort(e3); SqlInternalConnection.BestEffortCleanup(target); throw; } } base.Dispose(disposing); } 

and if you notice, this will only happen if this._internalTransaction.Dispose(); is called this._internalTransaction.Dispose(); . The problem here is that if GetBestEffortCleanupTarget throws an exception, it will not be cleared.

In your case, if the exception is not thrown as already indicated, you will Zombied into the Zombied category, and then it actually issues a Rollback call in the _internalTransaction.Dispose() call.

Finally, if called with false , it will certainly not be deleted.

Now, if I really can't see anything, I'm a little shocked at how fragile this code is.

It is interesting to note that, in my opinion, the MSDN documentation is actually incorrect , as it indicates the Rollback() method:

A transaction can only be canceled from the standby state (after calling BeginTransaction, but before calling Commit). A transaction is rolled back if it is withdrawn before calling Commit or Rollback.

Answer 2

The transaction is automatically rolled back if you have not yet called Commit. Thus, your usage blocks may look something like this, and the transaction will be canceled if an exception is thrown before the commit.

 using (IDbConnection connection = ...) { connection.Open(); using (IDbTransaction transaction = connection.BeginTransaction()) { using (IDbCommand command = ...) { command.Connection = connection; command.Transaction = transaction; ... } ... transaction.Commit(); } }