Best way to UPDATE multiple rows in oracle database using C#

Question

I have a variable list of IDs. I want to update the "Status" column for each of those IDs in an Oracle database. I've searched and have come across the following options:

OracleDataAdapter - Don't have a DataTable, only IDs
for loop which contains UPDATE statement (could be thousands of UPDATEs)
Stored Procedure - Hoping not to have to do this

Any help would be greatly appreciated. Thanks

no need to be so against stored procedures, those are not so bad :) look here for some ideas: http://stackoverflow.com/questions/2528181/update-multiple-rows-with-one-query — Davide Piras, Sep 14 '11 at 20:34
I don'T see a question... have you tried any of the options ? what did not work ? please show some code... — Yahia, Sep 14 '11 at 20:34
and as a side note, a for loop with many commands sent to the database but all of them in the same transaction is in theory not too bad either, except for the fact that you do many round trips to the server with small data transfer instead of one with more data... I like the second in general but it's arguable... — Davide Piras, Sep 14 '11 at 20:36
If it is a variable list and can get huge, a loop with a transaction is the way to go for sure. — daniloquio, Sep 14 '11 at 20:39
Thanks for the replies. I have gone with the for loop approach. — jbizzle, Sep 22 '11 at 14:57

score 0 · Answer 1 · answered Sep 14 '11 at 20:44

0

If you already know the Ids you can build an in clause and do an ExecuteScalar:

update sometable set status = 'Shipped' where sometableid in (10,17,19,20,89)

answered Sep 14 '11 at 20:44

Greg Finzer

6,714
21
80
125

score 0 · Answer 2 · answered Sep 14 '11 at 23:33

0

Check out this thread.

https://forums.oracle.com/forums/thread.jspa?threadID=638673

It talks about using parameter arrays which may be perfect for what you are trying to do.

answered Sep 14 '11 at 23:33

tsells

2,751
1
18
20

score 0 · Answer 3 · answered Sep 15 '11 at 15:38

Thanksk for the replies. I had in mind to do it like the following, I was just concerned with keeping the connection open for so many iterations.

using (OracleConnection oracleConnection = new OracleConnection(connection))
                {
                    oracleConnection.Open();

                    OracleCommand Command = new OracleCommand();
                    Command.Connection = oracleConnection;
                    Command.CommandType = CommandType.Text;
                    status = "A";
                    foreach (var memberID in MemberIDs)
                    {
                        sqlStatement = " UPDATE " + " ourdbtable" + " Set userstatus = '" + status + "' WHERE " + "memberid= " + memberID;
                        // command
                        Command.CommandText = sqlStatement;
                        Command.ExecuteNonQuery();
                    }
                }

Best way to UPDATE multiple rows in oracle database using C#

3 Answers3