Why both SqlConnection and SqlTransaction are present in SqlCommand constructor?

Question

I wonder, what is the reason to have this SqlCommand constructor overload:

public SqlCommand(
    string cmdText,
    SqlConnection connection,
    SqlTransaction transaction
)

?

When I need to create an internal method that does its bit using a transaction provided as an argument, I always find it sufficient to only pass an SqlTransaction to that method, because, obviously, the connection will be tran.Connection.
Doesn't the same apply to this overload? Would it not be enough to only pass cmdText and transaction?

Is it actually possible to execute an SqlCommand against a connection, providing an SqlTransaction opened against a different SqlConnection? What will this result in?

Cho

Answer 1

This is an interesting observation because you cannot use a Transaction from a different Connection. The System.Data.SqlClient.SqlCommand (4.0) has a private member called ValidateCommand that contains several validation checks, including this one:

if ((this._transaction != null) && (this._activeConnection != this._transaction.Connection))
{
    throw ADP.TransactionConnectionMismatch();
}

The overall design of the SqlCommand class is for flexibility. The CommandText, Connection and Transaction properties (which are also exposed in the three additional constructor overloads) are read/write. This makes the class flexible, but also prone to incorrect usage.

Certainly, things would be much cleaner if the properties were read-only and the constructors used as the primary means of passing data into the object. In which case the following constructor would make much more sense:

public SqlCommand(string commandText, SqlTransaction transaction)

However, I would imagine that these properties are read/write to enable drag-n-drop designer support where the object is constructed using the default constructor and properties are set in the InitializeComponent method.

Alinealinna