Non Transactional Execution Within a TransactionScope

Question

I am executing multiple stored procedures from within a TransactionScope. Is there any way to execute a SqlCommand (while still within the scope of the using statement) but remove it from the transaction?

There are SqlCommands executed before and after it that do need to be within the one transaction.

Answer 1

You'd have to open another connection and execute your statement there. But beware: that statement will not see any changes from the uncommitted transaction.

Answer 2

You need to create a new scope and specify the Supress option.

Answer 3

You can create a new SqlConnection instance with the 'Enlist' connection parameter set to 'false'

SqlConnectionStringBuilder csBuilder = new SqlConnectionStringBuilder(existingCon.ConnectionString);
csBuilder.Enlist = false;
SqlConnection newConnection = new SqlConnection(csBuilder.ConnectionString);     
SqlCommand commandNotInTheTx = newConnection.CreateCommand();

Answer 4

IIRC, transaction enlistment requires connections to be members of the same connection pool. If you use a slightly different connection string for the second connection (perhaps by using a different Application Name), that might allow it to bypass enlistment.

Just be careful you don't accidentally start using distributed transactions and DTC.