Why are SqlConnection and SqlTransaction present in the SqlCommand constructor?

Question

I wonder what the reason for overloading the SqlCommand constructor is:

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

?

When I need to create an internal method that executes its bit using the transaction provided as an argument, I always find it sufficient to just pass SqlTransaction this method, because obviously the connection will be tran.Connection .
Does this apply to this overload? Wouldn't it be enough to go through only cmdText and transaction ?

Is it possible to execute SqlCommand for a connection by providing an SqlTransaction open to another SqlConnection ? What will it lead to?

Answer 1

This is an interesting observation because you cannot use a transaction from another Connection. 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 designed for flexibility. The CommandText, Connection, and Transaction properties (which also appear in three additional constructor overloads) are read / write. This makes the class flexible, but also prone to misuse.

Of course, everything would be much cleaner if the properties were read-only, and the constructors were used as the main means of transferring data to the object. In this case, the following constructor makes sense:

 public SqlCommand(string commandText, SqlTransaction transaction) 

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