Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using ADO.Net transactions within a TransactionScope?

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.

like image 582
larryq Avatar asked Nov 23 '09 22:11

larryq


People also ask

How do you use transactions in Ado net?

Performing a Transaction Using a Single Connection In ADO.NET, you control transactions with the Connection object. You can initiate a local transaction with the BeginTransaction method. Once you have begun a transaction, you can enlist a command in that transaction with the Transaction property of a Command object.

Which namespace has TransactionScope class defined in Ado net?

TransactionScope is a class of System Namespace. It can also be termed as Transactions Namespace. The TransactionScope class supports transactions from code blocks and that is why it plays a key role in the . NET development framework.

What is an ambient transaction?

The ambient transaction is the transaction within which your code executes. You can obtain a reference to the ambient transaction by calling the static Transaction. Current property of the Transaction class.


1 Answers

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.

Enlist 'true'. true indicates that the SQL Server connection pooler automatically enlists the connection in the creation thread's current transaction context.

like image 50
micahtan Avatar answered Dec 10 '22 04:12

micahtan