0

For a project I'm writing a bunch of queries, which are in it's own separately, but they need to be executed in a transaction.

I'm using a TransactionScope

Consider the following code:

// connection = new SqlConnection(...); 
// connection.Open();

using(var transactionScope = new TransactionScope())
{
    // execute first query on connection
    using(var sqlCommand = new SqlCommand("DELETE THIS", connection))
    {
        // sqlCommand.Parameters....
        sqlCommand.ExecuteNonQuery();
    }

    // second query
    using(var sqlCommand = new SqlCommand("INSERT THAT", connection))
    {
        // sqlCommand.Parameters....
        sqlCommand.ExecuteNonQuery();
    }

    transactionScope.Complete();
}

Now how does the sqlCommand know that somewhere in previous code I have a transactionScope?

Anemoia
  • 7,928
  • 7
  • 46
  • 71
  • 1
    The link in your question has the answer to your question. And transaction is not the TransactionScope you created. – paparazzo Feb 25 '14 at 13:04
  • 1
    @Blam is right. Here is the text _Upon instantiating a TransactionScope by the new statement, the transaction manager determines which transaction to participate in. Once determined, the scope always participates in that transaction._ – Rodion Feb 25 '14 at 13:27
  • This question is not a duplicate. Answer: In this code example, the `SqlCommand` does **not** know it belongs to the `TransactionScope`, and actually will **not** execute within the transaction. Declaring a `TransactionScope` does not automatically initiate a transaction on the connection. Rather, the `SqlConnection` is designed to automatically "enlist" itself in the `TransactionScope`, but _only if it is opened within that scope_. To use `TransactionScope` with a `SqlConnection`, you would typically open the scope, then open the DB connection, then execute the SQL commands. – Gooseberry May 01 '18 at 10:06
  • If the connection was already open outside the scope, you can still manually bind the connection to the `TransactionScope` by calling `connection.EnlistTransaction(Transaction.Current)`. Relevant documentation is in the article for Distributed Transactions (ADO.NET). – Gooseberry May 01 '18 at 10:07

0 Answers0