SqlCommand.Dispose() before SqlTransaction.Commit()?

Question

would it work to dispose a command assigned to a transaction before the transaction is committed? I tested the following code myself, and it seems to have worked fine, but this is a rather small example, so I'm looking for confirmation if someone positively knows.

internal static void TestTransaction()
{
    try
    {
        Program.dbConnection.Open();
        using (SqlTransaction transaction = Program.dbConnection.BeginTransaction())
        {
            Boolean doRollback = false;
            for (int i = 0; i < 10; i++)
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO [testdb].[dbo].[transactiontest] (testvalcol) VALUES (@index)", Program.dbConnection, transaction))
                {
                    cmd.Parameters.AddWithValue("@index", i);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException)
                    {
                        doRollback = true;
                        break;
                    }
                }
            }
            if (doRollback)
                transaction.Rollback();
            else
                transaction.Commit();
        }
    }
    finally
    {
        Program.dbConnection.Close();
    }
}

You are only disposing of the Command object because it's wrapped around a using(){} the only true way to test what you are talking about is to try it without the using and see the results but if the Transaction is tied to the command as well as the connection.. then you will have catastrophic Errors.. — MethodMan, Feb 15 '12 at 17:03

Gert Arnold · Accepted Answer · 2012-02-17T18:06:56.700

The connection, transaction and command objects are just vehicles to send commands to a database. Once a command is executed the database has received it. Whatever you do with the command object afterwards, dispose it, burn it, or shoot it to the moon, this fact does not change. (It can only be rolled back).

You can create and dispose as many commands as you like within the scope of one SqlConnection (with or without SqlTransaction). And you can start and dispose as many transactions as you like within one SqlConnection. To demonstrate this, see:

using (var conn = new SqlConnection(@"server=(local)\sql2008;database=Junk;Integrated Security=SSPI"))
{
  conn.Open();
  // Repeat this block as often as you like...
  using (var tran = conn.BeginTransaction())
  {
    using (var cmd = new SqlCommand("INSERT INTO Mess VALUES ('mess1')", conn, tran))
    {
      cmd.ExecuteNonQuery(); // Shows in Sql profiler
    }
    tran.Commit(); // Commits
  }
  using (var cmd = new SqlCommand("INSERT INTO Mess VALUES ('mess2')", conn))
  {
    cmd.ExecuteNonQuery(); // Executes and commits (implicit transaction).
  }
}

Of course, for healthy code you need to dispose of all objects in the correct order. Disposing a command after disposing a SqlConnection may cause the connection object to stay in memory.

why should the disposal in the wrong order cause the connection to stay in memory? I would expect it stays at least until the last command/transaction is disposed. — eFloh, Feb 15 '12 at 20:03
@eFloh Because it keeps a reference to the underlying connection of the connection pool. It could not cut that reference because it still had an undisposed command. — Gert Arnold, Feb 15 '12 at 20:04
ok, we do mean the same. is will be disposed when all commands are disposed, even when they are disposed after the end of the connection's using. I first understood your text above to mean it will never be disposed in this case... — eFloh, Feb 16 '12 at 15:02
@eFloh I knew that a few weeks ago there was a post about this. Finally found it: http://stackoverflow.com/questions/8842115/running-out-of-connections-in-pool — Gert Arnold, Feb 16 '12 at 15:29

score 1 · Answer 2 · answered Feb 15 '12 at 17:02

1

Yes, it's probably safe. The using() is closing the Command, not the Connection.

But you should put that Connection in another using() block or in a try/finally construct.

answered Feb 15 '12 at 17:02

H H

263,252
30
330
514

score 1 · Answer 3 · answered Feb 15 '12 at 20:01

Confirmed, this works very well and (at least here at our company) is even considered the correct approach.

create connection
create transaction
create command(s), use the transaction, execute
dispose command(s)
commit transaction
dispose connection

SqlCommand.Dispose() before SqlTransaction.Commit()?

3 Answers3

Linked