Как использовать одну SqlTransaction для нескольких SqlConnections в.NET?

Question

1. У меня есть SQL Server 2000, он не поддерживает MultipleActiveResults.
2. Я должен сделать несколько вставок, и это делается с одним соединением на вставку.
3. Я хочу начать транзакцию перед всеми вставками и завершить ее после всех вставок.
4. Как мне это сделать?

Answer 1

По какой причине вы не используете одно соединение и несколько команд (фактически одна команда воссоздается в цикле)? Может быть, это решение будет работать для вас:

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

Также см
Транзакции SQL Server - ADO.NET 2.0 - Фиксация и откат - Использование оператора - IDisposable

Answer 2

Вы не указали, используете ли вы.NET 2.0, но я сделаю это предположение. Пример кода C# 3.0 приведен ниже:

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

Если какой-либо из операторов БД завершится неудачно, ts.complete никогда не будет достигнут, и транзакция будет автоматически откатана в конце оператора using. Однако одним из предостережений этого подхода является то, что в конечном итоге вы будете использовать DTC, если будете использовать несколько соединений, что означает снижение производительности.

Отредактировано, чтобы изменить SqlTransaction на TransactionScope

Пример принудительного использования одного соединения SQL, чтобы избежать использования 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
        }
    }
    tx.Complete();
}

Answer 3

Транзакции создаются для соединения и не имеют никакой области, кроме той, в которой они созданы, поэтому вы не можете делать то, что просите. Выполните рефакторинг, чтобы вы могли выполнять вставки последовательно для соединения, а не одновременно или реализовывать другой механизм для достижения отката (например, таблица вставленных ключей, которую можно использовать для поиска, чтобы удалить вставленные записи, если позже ошибка возникнет в процесс)

Answer 4

Я не думаю, что транзакция может охватывать несколько соединений.

В чем причина создания нескольких вставок в отдельных соединениях? Я думаю, вы бы хотели, чтобы они были в одном соединении, как обычно.