Каков наилучший подход при выполнении нескольких операторов UPDATE в одном соединении?

Question

Как правильно сделать следующее обновление:

using (OracleConnection conn = new OracleConnection())
using (selCmd)
{

    string sql1 = "update Table1 set name = joe where id = 10;"
    string sql2 = "update Table2 set country = usa where region = americas;"
    string sql3 = "update Table3 set weather = sunny where state = CA;"
    string sql4 = "update Table4 set engine = v8 where maker = benz;"

    cmdUpdate.CommandText = sql(#);
    cmdUpdate.Connection = conn;
    recs = cmdUpdate.ExecuteNonQuery();
}

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

Я думаю, перебрать массив элементов [sql1,sql2,sql3,sql4] и передать sql(#) в CommandText и выполнять ExecuteNonQuery каждый раз.

Answer 1

Если я правильно помню, можно объединить несколько операторов SQL в одну строку, разделенную точкой с запятой (;). В противном случае нет ничего плохого в выполнении нескольких ExecuteNonQuery() звонки.

string sql1 = "BEGIN update Table1 set name = 'joe' where id = 10;",
       sql2 = "update Table2 set country = 'usa' where region = 'americas';",
       sql3 = "update Table3 set weather = 'sunny' where state = 'CA';",
       sql4 = "update Table4 set engine = 'v8' where maker = 'benz'; END;";

string sql = string.Format("{0}{1}{2}{3}",sql1,sql2,sql3,sql4);

using (OracleConnection conn = new OracleConnection())
using (OracleCommand cmdUpdate = new OracleCommand(sql, conn))
{
    conn.Open();
    recs = cmdUpdate.ExecuteNonQuery();
}

Answer 2

Недавно я столкнулся с этой проблемой в каком-то старом коде. Мы динамически строим цепочку вызовов SQL (с поддержкой Oracle и Sql Server). Поскольку в настоящее время нет производственной реализации Oracle, никто не проверял работу Oracle, и ошибки клиентов не поступали. Я нашел код, который создает цепочку команд, а затем для Oracle он использует String.Split(';'), Затем он использует цикл для выполнения каждого оператора в транзакции: rowsAffecter += ExecuteNonQuery....

Мне не нравится эта идея, потому что без параметризации это опасный подход, так как некоторые данные могут содержать ;, Но даже если параметризация на месте...

... одна из проблем создания анонимного блока для Oracle ("begin... end;") в том, что ExecuteNonQuery не будет возвращать количество строк (возвращает -1), что иногда необходимо, чтобы судить, было ли что-то обновлено или нет.

чтобы решить эту проблему, я сделал это

private string AppendOracleCountOrNothing(StringBuilder sql)
{
    if (_myProvider == Providers.Oracle)
        sql.AppendLine("rowCnt := rowCnt + SQL%ROWCOUNT;");
}


public void SomeMethod()
{
    var longSqlChain = new StringBuilder(2000);

    longSqlChain.Append("Insert into table...;");
    AppendOracleCountOrNothing(longSqlChain);
    if (someCondition)
    {
        longSqlChain.AppendLine("Update anotherTable...;");
        AppendOracleCountOrNothing(longSqlChain);
    } 

    // may be, add some more sql to longSqlChain here....

    int rowsAffected;
    if (_myProvider == Providers.Oracle)
    {
        longSqlChain.Insert(0, @"DECLARE
            rowCnt number(10) := 0
            BEGIN
            ").AppendLine(@":1 := rowCnt;
            END;");
        // Now, here we have some abstract wrappers that hide provider specific code. 
        // But the idea is to prepare provider specific output parameter and then parse its value
        IDataParameter p = ParameterWrapper.PrepareParameter(":1", 0, ParameterDirection.Output, myProvider); // note IDataParameter 
        SqlExecWrapper.ExecuteNonQuery(_myProvider, CommandType.Text, sql, new[]{p});
        rowsAffected = p.GetParameterValue(); // GetParameterValue is an extension on IDataParameter 
    }
    else // sql server
    {
        rowsAffected = SqlExecWrapper.ExecuteNonQuery(_myProvider, CommandType.Text, sql, null);
    }
}

Таким образом, мы совершаем одну поездку в БД и получаем количество возвращаемых строк, затронутых этим вызовом. и запросы также могут быть параметризованы. Опять же, лучше разработать слой абстракции, чтобы можно было назвать что-то вроде parameterizer.CreateParameter(10), который добавит параметр в коллекцию и сгенерирует :1, :2, :3, etc. (оракул) и @1, @2, @3, etc. (сервер SQL), в вашем заявлении SQL.

Answer 3

Другой подход заключается в создании простого метода расширения (ExecuteMultipleNonQuery), который просто разбивает строку на все точки с запятой и выполняет каждый оператор в цикле:

public static class DbCommandExtensions {

    public static void ExecuteMultipleNonQuery(this IDbCommand dbCommand)
    {
        var sqlStatementArray = dbCommand.CommandText.Split(new string[] {";"}, StringSplitOptions.RemoveEmptyEntries);
        foreach (string sqlStatement in sqlStatementArray)
        {
            dbCommand.CommandText = sqlStatement;
            dbCommand.ExecuteNonQuery();
        }
    }

}