在一个连接中执行多个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]，并在CommandText中传递sql(#)，每次执行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和SqlServer)。由于目前还没有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);
}
}

通过这种方式，我们对DB进行一次访问，并获得受此调用影响的返回行数。并且查询也可以参数化。同样，更好地开发抽象层，因此，您可以调用类似parameterizer.CreateParameter(10)的东西，它将向集合添加参数，并在sql语句中生成:1, :2, :3, etc.(oracle)和@1, @2, @3, etc.(sql server)。

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