CODEWITHSUNDEEP
c#ado.net
Chace Fields
Chace Fields

Reputation: 907

Does an ADO.NET transaction need to be assigned to the command object when using Connection.CreateCommand()?

I need to use transactions with a ADO.NET provider.

Below is a simple example of a connection, transaction and command being created. When I create a command using connection.CreateCommand() do I need to assign the transaction to the command? Or, is the transaction set because I'm using connection.CreateCommand() vs newing up a command object? 

var connection = Database.GetConnection();
connection.Open();

var transaction = connection.BeginTransaction();

var command = connection.CreateCommand();
command.Transaction = transaction; // Is this line needed when using connection.CreateCommand()?

*Update*

When I test the reference of both objects, they are the same. I'd assume that means connection.CreateCommand() is returning a command with the transaction assigned. Or maybe that is not a valid test.

using (var connection = Database.GetConnection())
{
    connection.Open();

    var transaction = connection.BeginTransaction();

    var command = connection.CreateCommand();

    if (object.ReferenceEquals(transaction, command.Transaction))
        Debug.WriteLine("EQUAL");
}

Upvotes: 7

Views: 7055

Answers (5)

Sameh
Sameh

Reputation: 1418

using (var connection = new SqlConnection(Database.ConnectionString))
{
   connection.Open();
   using (var trans = connection.BeginTransaction())
   {
       using (var command = trans.Connection.CreateCommand())
       { 
          command.CommandText = 'DELETE FROM TABLE_NAME WHERE ID = ?'; 
          command.Transaction = trans;
          command.ExecuteNonQuery();
       }
       trans.Commit();
   }
}

Upvotes: 0

drowa
drowa

Reputation: 732

If you use TransactionScope you do not need to attach anything to the command object.

Just take a look at the example from the documentation of TransactionScope.

Upvotes: 2

Jalal
Jalal

Reputation: 6846

You have to explicitly set the transaction for each SqlCommand instance. Here is the source code of System.Data.SqlClient.SqlConnection.cs (Line: 782) of CreateCommand:

new public SqlCommand CreateCommand() {
    return new SqlCommand(null, this);
}

As you see; It pass null for CommandText and this (itself) for SqlConnection arguments.

Upvotes: 3

Jonathan Edwards
Jonathan Edwards

Reputation: 41

If you use connection.CreateCommand, with a connection that has already started a transaction, the resulting command object will be enlisted in the transaction (the command's transaction property will be set).

If you use new Command you have to set the transaction explicitly.

Upvotes: -1

HABO
HABO

Reputation: 15841

Yes, the transaction and command need to be associated with one another.

Some redacted sample code:

// Connect to the database.
SqlConnection connection = new SqlConnection(Database.ConnectionString);
connection.Open();

// Start a transaction.
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.Transaction = connection.BeginTransaction(System.Data.IsolationLevel.Serializable, "ryan");

// Delete any previously associated targets.
command.CommandType = System.Data.CommandType.StoredProcedure;
command.CommandText = "FirstSP";
command.Parameters.AddWithValue("@Id", this.Id);
command.ExecuteNonQuery();

// Add the specified targets to the product.
command.CommandText = "SecondSP";
command.Parameters.Add("@Id", SqlDbType.Int);
foreach (int Id in Ids)
{
    command.Parameters["@Id"].Value = Id;
    command.ExecuteNonQuery();
}

// Commit the transaction.
command.Transaction.Commit();

// Houseclean.
connection.Close();

Upvotes: 2

Related Questions