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

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