Two questions:

1) Is IsTransactionInProgress suppose to return false in this case:

using (TransactionScope ts = new TransactionScope())
{
       using (MyAdapter adapter = new MyAdapter())
       {
             // Returns false
             bool transacted = ts.IsTransactionInProgress;
       }
}

2) I know that if I call StartTransaction, then the adapter will keep the connection open between calls, even if I don't pass in keepConnectionOpen = true. However, if I don't call StartTransaction, and it participates in an ambient transaction (as above), will it keep the connection open? My hope is that it does not.

Thanks, Brian

nottinbe wrote:

1) Is IsTransactionInProgress suppose to return false in this case:

using (TransactionScope ts = new TransactionScope())
{
       using (MyAdapter adapter = new MyAdapter())
       {
             // Returns false
             bool transacted = ts.IsTransactionInProgress;
       }
}

I think you mean adapter.IsTransactionInProgress. And yes. if the adapter's involded transaction doesn't have any pending actions to commit/rollback. The IsTransactionInProgress will return false, otherwise, true.

nottinbe wrote:

2) I know that if I call StartTransaction, then the adapter will keep the connection open between calls, even if I don't pass in keepConnectionOpen = true. However, if I don't call StartTransaction, and it participates in an ambient transaction (as above), will it keep the connection open? My hope is that it does not.

If no adapter.StartTransaction is called and you don't pass keepConnectionOpen = true, then the adapter.Save.. actions will start a transaction internally, this transaction will be closed after the Save... routine finishes.

If an adapter.StartTransaction is called, the connection will be alive as long as the transaction lives (until Commit or Rollback).

If you don't want open connections for a long time and you plan to use TransactionScope, then don't start adapter.StartTransaction. After all, all the actions will be performed by DTC when the ts.Complete() is called.

I think you mean adapter.IsTransactionInProgress. And yes. if the adapter's involded transaction doesn't have any pending actions to commit/rollback. The IsTransactionInProgress will return false, otherwise, true.

Yes, i meant adapter.IsTransactionInProgress. I was just a little surprised at first because if the adapter is created within a TransactionScope, it will operate within a Transaction, yet IsTransactionInProgress always returns false. If you want to really know if it will be in a transaction you'd need to check both IsTransactionInProgress and InSystemTransaction.

Even though I was surprised by it, I do rely on this functionality now, so I just wanted to make sure this was how it was intended, and how it will stay.

If you don't want open connections for a long time and you plan to use TransactionScope, then don't start adapter.StartTransaction. After all, all the actions will be performed by DTC when the ts.Complete() is called.

That's not necessarily true. It changed in VS2008. You can open and close multiple connections within a TransactionScope, and as long as they are the same ConnectionString, there will be no promotion. It is necessary however to ensure that one connection is closed before the next was open, which is why I wanted to verify that the adapter will close the connection after each operation, even within a TransactionScope.

In a much simplified example, we are now doing basically this:

using (TransactionScope ts = new TransactionScope())
{
     using (MyAdapter adapter = new MyAdapter())
     {
            adapter.SaveEntity(someEntity);

            using (SqlConnection con = OpenSqlConnection())
            {
                   ExecuteACommandWithConnection(con);
            }
     }

     using (MyAdapter adapter = new MyAdapter())
     {
             adapter.SaveEntity(another);
     }

      using (SqlConnection con = OpenSqlConnection())
      {
              ExecuteAnotherCommandWithConnection(con);
      }

      ts.Complete();
} 

And since the connection is closed after each operation, and the same ConnectionString is used for each connection, there is never any promotion to DTC.

Thanks, Brian