CODEWITHSUNDEEP
c#.netoracle-databaseodp.net
Justin
Justin

Reputation: 6559

Oracle bulk updates using ODP.NET

Looking at this example to do bulk inserts, I assumed using the same logic would work for updates. I tried to the following to see if it would work, and it does not:

string sql = "update TEST set NAME=:newName where NAME=:name";

connection.Open();
OracleCommand command = connection.CreateCommand();
command.CommandText = sql;
command.CommandType = System.Data.CommandType.Text;
command.BindByName = true;

command.ArrayBindCount = 5;

string[] originalName = { "Test1", "Test2", "Test3", "Test4", "Test5" };
string[] newName = { "New Test1", "New Test2", "New Test3", "New Test4", "New Test5" };

command.Parameters.Add(":newName", OracleDbType.Varchar2, originalName, System.Data.ParameterDirection.Input);
command.Parameters.Add(":name", OracleDbType.Varchar2, newName, System.Data.ParameterDirection.Input);

command.ExecuteNonQuery();
connection.Close();

Does this not work for updates? Is there a way to easily do bulk updates similarly how the bulk inserts are performed in the example I linked?

Upvotes: 2

Views: 4716

Answers (2)

Justin
Justin

Reputation: 6559

It turns out I had my parameter names flipped. Took me forever to find it.

Upvotes: 1

Diego
Diego

Reputation: 36166

you cant pass a array as a parameter.

What you can do is loop through your array and call the update for each position of the array (whihc wouldnt really be a "bulk" insert), or you can use something like: 

WHERE NAME in ("Test1", "Test2", "Test3", "Test4", "Test5")

and so on

Upvotes: 0

Related Questions