title: Creating a transaction scope with new SqlConnection within existing SqlConnection using block
body: <p>I want to execute a single SELECT query followed by a sequence of UPDATE queries (all on the same table); UPDATE is implemented in a separate method which is called repeatedly. If one of the UPDATE queries fail, I want them all to fail/rollback - so I want to enlist them in a transaction. However, I'm unsure where <code>SqlConnection</code> should be opened to avoid any issues. My current implementation looks like this:</p>
  
  <pre><code>using (SqlConnection connection = new SqlConnection(connectionString))
  {
      connection.Open();
  
      // execute a single SELECT here
  
      using (TransactionScope scope = new TransactionScope())
      {
          for (int i=0; i&lt;...; i++)
          {
              Update(); // UPDATE query
          }
  
          scope.Complete();
      }
  }
  
  
  Update()
  {
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
          connection.Open();
  
          // execute a single UPDATE here
      }
  }
  </code></pre>
  
  <p>Should this work as expected in all situations?</p>
  
  <p>Is it OK to open a connection before SELECT, then open a new connection in <code>Update()</code> method? Due to connection pooling, the same connection will be used for both SELECT and UPDATE queries (<code>connectionString</code> is the same), but only UPDATE queries will be enlisted in the transaction, right? But what happens if different connection gets used in <code>Update()</code>? Will all UPDATE queries still enlist in a transaction as expected and execute atomically? </p>
  
  <p>If I understand things correctly, creating a transaction scope after closing the first connection (after the <code>using</code> block which executes SELECT) would still work, but would reduce performance, because connection would be closed and needed to be re-opened, correct? Or is in fact a new connection created for the transaction scope, and gets opened and closed every time <code>Update()</code> is called?</p>