7

I created a sqlconnection, CN1. Then this CN1 is opened. Later in the code there is a transactionscope. If I execute a sql command on this CN1 connection, is this within transaction?

Code looks like this;

SqlConnection cn1 = new SqlConnection();
cn1.Open(); //connection opened when there is no ambient transaction.
...

using(TransactionScope scope = new TransactionScope())
{
  SqlCommand cmd; //a typical sql command.

  ...

  cmd.ExecuteNonQuery(); //Is this command within transaction?
  ...
}
Oded
  • 489,969
  • 99
  • 883
  • 1,009
mkus
  • 3,357
  • 6
  • 37
  • 45

2 Answers2

9

It is a MUST to open the connection within the TransactionScope to ensure that the connection is enrolled in the transaction.

This is found in the comment just above the connection.Open in this MSDN example.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • 2
    is there any solution to solve this problem within transactionscope other than open sql connection in the transactionoption?Can Enlist methods be helpful about this? – mkus Dec 29 '09 at 20:21
  • 3
    mkus, yes you can use `SqlConnection` instance method `EnlistTransaction( Transaction.Current );` to enlist an already-open connection in a transaction scope. So saying it *MUST* be opened within the TransactionScope is not true, although that's how the automated mechanism is supposed to be used. Also, opening the connection inside the scope doesn't even guarantee the connection will be enlisted, because the connection string could specify "Enlist=false". Typically, that option isn't specified, but it's something to be aware of and to check. See http://stackoverflow.com/a/2886326/88409 – Triynko Nov 13 '13 at 07:09
5
  1. No, command is not executed in transaction
  2. Open connection inside the scope or use EnlistTransaction method of SqlConnection instance. See my answer in different thread.
Community
  • 1
  • 1
Michal Levý
  • 33,064
  • 4
  • 68
  • 86