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

Question

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");
}

you need to have an Instance of SQLTransaction object for starters.. try a google search for C# SQLTransaction, if you don't wnat to take the time to do the search then look here at this StackOverFlow Posting also look at the Related Links on the right side of this page for examples as well , http://stackoverflow.com/questions/4688993/how-to-handle-sql-transaction-in-this-scenario — MethodMan, Dec 28 '12 at 20:31
`BeginTransaction()` returns a transaction. http://msdn.microsoft.com/en-us/library/86773566.aspx — Chace Fields, Dec 28 '12 at 20:33
can you formulate your question in a readable code block as well? — MethodMan, Dec 28 '12 at 20:35
Possible duplicate of [Performing an Oracle Transaction using C# and ODP.NET](http://stackoverflow.com/questions/18931026/performing-an-oracle-transaction-using-c-sharp-and-odp-net) — Saul, Apr 17 '17 at 21:42

score 3 · Answer 1 · answered Apr 20 '15 at 08:11

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.

score 2 · Answer 2 · answered Dec 28 '12 at 20:38

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();

score 2 · Answer 3 · answered Jun 01 '15 at 23:02

2

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.

answered Jun 01 '15 at 23:02

drowa

682
5
13

TransactionScope runs transaction in serializable mode by default, you need to set the transaction isolation level to Dirty Read other wise you will run into performance and table locks issues – Sameh Jan 13 '21 at 17:51

score 0 · Answer 4 · answered Jan 13 '21 at 17:49

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();
   }
}

score -1 · Answer 5 · edited Feb 16 '13 at 11:15

-1

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.

edited Feb 16 '13 at 11:15

qben

813
10
22

answered Feb 16 '13 at 10:44

Jonathan Edwards

31
4

Sadly, you still have to explicitly set the transaction. While not very intuitive, [Microsoft states this is by design](https://connect.microsoft.com/VisualStudio/feedback/details/283583/sqlconnection-createcommand-doesnt-set-the-transaction-property-when-the-connection-is-actively-in-a-transaction). It is trivial to confirm this with a quick console app, though it's just as easy to look at the [SqlConnection.CreateCommand source](http://referencesource.microsoft.com/System.Data/R/6d218ea47afc988a.html). – Dan Lyons Sep 02 '14 at 22:48
Yes. I discovered this to be true after working on a migration from Oracle 11 to SQL Server. With the Oracle driver you didn't need to explicitly set the transaction on the command. Not so with SQL Server 2012! – Jonathan Edwards Jan 18 '16 at 09:02

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

5 Answers5