Whatis is best approach on executing multiple UPDATE statement in one connection?

c#, odp.net, oracle, sql-update

Solution

If I remember correctly, it is possible to concatenate multiple SQL statements in one string separated by semi-colons (;). Otherwise, there is nothing wrong with executing multiple `ExecuteNonQuery()` calls.

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

Problem

How to properly do the following update: ``` 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(); } ``` I am aware of all or nothing if it's a transaction but I just to see how it works with correct approach. I'm thinking iterate an array of items [sql1,sql2,sql3,sql4] and pass sql(#) in the CommandText and perform ExecuteNonQuery each time.

Original source