How to use a single SqlTransaction for multiple SqlConnections in .NET?

Question

1. I have SQL Server 2000, it doesn't support MultipleActiveResults.
2. I have to do multiple inserts, and it's done with one connection per insertion.
3. I want to begin a transaction before all insertions and finish it after all insertions.
4. How do I do it?

Answer 1

You didn't specify if you're using .NET 2.0, but I'll make that assumption. C# 3.0 sample code is listed below:

using (var tx = new TransactionScope()) {
    // Execute multiple DB statements inside here
    ts.Complete();
}

If any of the DB statements fail, the ts.complete will never be reached and the transaction will be rolled back automatically at the end of the using statement. However, one caveat to this approach is that you'll end up leveraging the DTC if you use multiple connections, which means slower performance.

Edited to change SqlTransaction to TransactionScope

An example of forcing a single SQL connection to avoid using the DTC:

using (var tx = new TransactionScope())
using (var db = new SqlConnection(connString)) {
    // Command 1
    using (var cmd = db.CreateCommand()) {
        cmd.CommandText = "select...";
        using (var reader = cmd.ExecuteReader()) {
           // Process results or store them somewhere for later
        }
    }

    // Command 2
    using (var cmd = db.CreateCommand()) {
        cmd.CommandText = "select...";
        using (var reader = cmd.ExecuteReader()) {
           // Process results or store them somewhere for later
        }
    }

    // Command 3
    using (var cmd = db.CreateCommand()) {
        cmd.CommandText = "select...";
        using (var reader = cmd.ExecuteReader()) {
           // Process results or store them somewhere for later
        }
    }
    ts.Complete();
}

Answer 2

I don't think a transaction can span multiple connections.

What's the reasoning for doing the multiple inserts in separate connections? I would think you'd want them in a single connection normally.

Answer 3

Transactions are created on a connection and have no scope beyond the connection they are created on so you can't do what you are asking. Refactor so that you can execute the inserts sequentially on a connection rather than simultaneously or implement a different mechanism to achieve rollback (e.g. a table of inserted keys that can be used as a look up to delete inserted records if an error is encountered later in the process)

Answer 4

What is the reason you don't use one connection and multiple commands (actually one command recreated in loop)? Maybe this solution will work for you:

   public static void CommandExecNonQuery(SqlCommand cmd, 
       string query, SqlParameter[] prms)
    {
        cmd.CommandText = query;
        cmd.Parameters.AddRange(prms);
        cmd.ExecuteNonQuery();
        cmd.Parameters.Clear();
    }

    static void Main(string[] args)
    {
        string insertQuery = 
           @"INSERT TESTTABLE (COLUMN1, COLUMN2) " + 
             "VALUES(@ParamCol1, @ParamCol2)";
        using (SqlConnection connection = 
          new SqlConnection(connectionString))
        {
            using (SqlCommand command = 
              connection.CreateCommand())
            {
                SqlTransaction transaction = null;
                try
                {
                    // BeginTransaction() Requires Open Connection
                    connection.Open();

                    transaction = connection.BeginTransaction();

                    // Assign Transaction to Command
                    command.Transaction = transaction;
                    for (int i = 0; i < 100; i++)
                        CommandExecNonQuery(command, insertQuery, 
                          new SqlParameter[] { 
                            new SqlParameter("@ParamCol1", i), 
                            new SqlParameter("@ParamCol2", i.ToString()) });
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }

Also see
Sql Server Transactions - ADO.NET 2.0 - Commit and Rollback - Using Statement - IDisposable