Using ADO.Net transactions within a TransactionScope?

Question

Hi everyone,

I've been reading about TransactionScope and had a question about its interoperability with ADO.Net transactions. Right now we have some data access methods that each call a stored proc and begin and commit their own individual transactions. Straighforward, boilerplate stuff, the methods look like this:

sqlCommand = //create SQLCommand with Stored proc
SqlTransaction myTransaction = myConnection.BeginTransaction();
sqlCommand.Transaction = sqlTransaction;
sqlCommand.Execute();
sqlTransaction.Commit();

I've simplified things a bit, but you get the idea.

I now need to call two of these class methods consecutively and commit or roll them back as a team from my business layer. I can't modify the data methods, so I was thinking of putting the two calls in a TransactionScope block.

If I do this, what sort of parameters should I use when creating the TransactionScope? I've already tried this using the TransactionScopeOption.RequiresNew option and things seem to work, but that's just me experimenting and I'm not sure if it's the way to go. (I'll note here that these are SQL transactions exclusively, running on the same SQL server.)

I see that TransactionScope has constructor options to deal with COM+ transactions. Because I'm using ADO.Net transactions, is that relevant here? Thanks for the advice.

Answer 1

The TransactionScope documentation here is a good place to start.

Basically it makes handling transactions at the ADO level unnecessary. Connections will automatically take into account existing ambient transactions (the default).

Keep in mind that you can also change the way connections enlist in ambient transactions via the connection string. This link will provide more detail.