I was taking a refresher MCSD test today to get ready to take one of the tests and came upon a question that is wrong.  But it does infer that there is some confusion about how transactions are propogated to commands…or may be evidence that it is a bug.  For example:

SqlConnection conn = null;
SqlTransaction tx = null;
try
{
  // Create a new Connection
  conn = new SqlConnection("Server=.;Database=Northwind;Integrated Security=true;");

  // Open the Connection
  conn.Open();

  // Start a Transaction and create a new Command
  tx = conn.BeginTransaction();
  using (SqlCommand cmd = new SqlCommand())
  {
    // Set the Connection to the command
    cmd.Connection = conn;

    // NOTE: I do not explicitly set the TX to the Command
    //cmd.Transaction = tx;

    // Insert new values and execute it 
    // (within the transaction)
    cmd.CommandText = @"INSERT INTO Customers (CustomerID, CompanyName) 
                        VALUES ('ZZZZY', 'My New Company');";
    cmd.ExecuteNonQuery();

    // Insert new values, but the table name is wrong
    cmd.CommandText = @"INSERT INTO Companies (CompanyID) VALUES('ANother Company')";
    cmd.ExecuteNonQuery();

    // We should never get here since the query is wrong
    tx.Commit();
  }
}
catch (Exception ex)
{
  // Rollback the tx if error'd
  if (tx != null) tx.Rollback();
}
finally
{
  tx.Dispose();
  // Close the connection just in case
  if (conn != null)
  {
    conn.Close();
    conn.Dispose();
  }
}

This code fails because I do not explicitly set the transaction to the command.  Unfortuately, you must set the connection and the transaction.  This seems like a bug because you cannot execute a command on the connection (that has an pending transaction) without throwing an error.

The practice test asked me to specify a single missing line of code, so I could either set the command’s connection property or it’s transaction property, but not both.  I suspect that there is confusion inside of MS about what is the expected behavior.  But for now, I will just continue to set both and know that the test is wrong…