TransactionScope question - how can I keep the DTC from getting involved in this?

(I know the circumstances surrounding the DTC and promoting a transaction can be a bit mysterious to those of us not in the know, but let me show you how my company is doing things, and if you can te开发者_如何学Goll me why the DTC is getting involved, and if possible, what I can do to avoid it, I'd be grateful.)

I have code running on an ASP.Net webserver. We have one database, SQL 2008.

Our data access code looks something like this-- We have a data access layer that uses a wrapper object for SQLConnections and SQLCommands. Typical use looks like this:

void method1()
{
    objDataObject = new DataAccessLayer();
    objDataObject.Connection = SomeConnectionMethod();
    SqlCommand objCommand = DataAccessUtils.CreateCommand(SomeStoredProc);

//create some SqlParameters, add them to the objCommand, etc....

objDataObject.BeginTransaction(IsolationLevel.ReadCommitted);
objDataObject.ExecuteNonQuery(objCommand);
objDataObject.CommitTransaction();
objDataObject.CloseConnection();
}

So indeed, a very thin wrapper around SqlClient, SqlConnection etc. I want to run several stored procs in a transaction, and the wrapper class doesn't allow me access to the SqlTransaction so I can't pass it from one component to the next. This led me to use a TransactionScope:

using (TransactionScope tx1 = new TransactionScope(TransactionScope.RequiresNew))
{
  method1();
  method2();
  method3();
  tx1.Complete();
}

When I do this, the DTC gets involved, and unfortunately our webservers don't have "allow remote clients" enabled in the MSDTC settings-- getting IT to allow that will be a fight.

I'd love to avoid DTC becoming involved but can I do it? Can I leave out the transactional calls in methods1-3() and just let the TransactionScope figure it all out?

---

What does SomeConnectionMethod() do? If it doesn't return the same connection in method1(), method2() and method3(), then you have a distributed transaction. So DTC will be involved to coordinate this transaction.

So you have to return the same connection in SomeConnectionMethod() to prevent DTC to involve in your transaction.

---

Why it doesn't work:

I think even if you would share the same connection (as suggested by larryq), it wouldn't work without getting DTC involved.

You want a Transaction scope to wrap those methods:

• method1
  • Begin Tran
  • Commit Tran
• method2
  • Begin Tran
  • Commit Tran
• method3
  • Begin Tran
  • Commit Tran

In each method you begin and commit. If something goes wrong in method3 you want to rollback everything, including the already committed transactions from method1&2. To do that you need a DTC transaction that is spanning all 3 methods.

Correct me if I am wrong, but I think you can use TransactionScope and avoid the use of DTC only if you do not begin and commit transactions inside the methods.

How could it work:

Do you really need to use transactions with begin and commit? Can't you do something like:

using (var scope = new TransactionScope(TransactionScopeOption.Required)) {
   objDataObject.ExecuteNonQuery(objCommand);
}

The Scope Required instead of RequiredNew uses an existing transaction if one already exists.

Please feel free to correct me if I got something wrong.