insert multiple rows with parameters in odp.net

Question

I want to use "insert all" statement in oracle with OracleParameter. I must insert variable number rows only in one execution. How can I do this? Here some codes:

string queryInsertRecipients = "INSERT INTO YS_ES_TO(EMAILID,EMAILTO) VALUES(:pEMAILID,:pEMAILTO)";
        OracleConnection connection = OracleConnectionOpen("csEmailManagement");
        OracleCommand command = new OracleCommand();
        OracleParameter[] toParameters = new OracleParameter[2];
      for (int i = 0; i < emailMessageList.Length; i++)
        {
            toParameters[0] = command.Parameters.Add("pEMAILID", OracleDbType.Int32, emailId, ParameterDirection.Input);
            toParameters[1] = command.Parameters.Add("pEMAILTO", OracleDbType.Varchar2, emailMessageList[i], ParameterDirection.Input);
            command.CommandText = queryInsertRecipients;
            command.Connection = connection;

        }

I don't think that's possible since you can have only one parameter with the same name per command. Anyway it doesn't sound like a good idea ... Always remember premature optimization is the root of all evil. — Dimitar Dimitrov
– Dimitar Dimitrov, Commented May 9, 2013 at 13:57
I don't know, just a prediction. If there is a better idea, I can accept it. — csjoseph
– csjoseph, Commented May 9, 2013 at 18:10

D Stanley · Accepted Answer · 2013-05-09 14:27:56Z

3

You may be able to do it by dynamically generating parameters:

OracleConnection connection = OracleConnectionOpen("csEmailManagement");
OracleCommand command = new OracleCommand();

// Start query string
string query = "INSERT ALL ";
for (int i = 0; i < emailMessageList.Length; i++)
{
    query = string.Format("{0} INTO YS_ES_TO(EMAILID,EMAILTO) VALUES (:{1}, :{2})",
                          query,
                          "pEMAILID_"+i,
                          "pEMAILTO_"+i);

    command.Parameters.Add("pEMAILID_"+i, 
                           OracleDbType.Int32, 
                           emailId, 
                           ParameterDirection.Input);
    command.Parameters.Add("pEMAILTO_"+i, 
                           OracleDbType.Varchar2, 
                           emailMessageList[i], 
                           ParameterDirection.Input);
} 
command.CommandText = query;
command.Connection = connection;

Be aware of the following things, however:

There is some limit on the number of parameters you can have, however it seems to be dependent on the total amount of data passed (64K?) versus the actual number of parameters
My guess would be that if one insert fails (bad data/overflow/etc.) that ALL will fail
You may be just as well to create the command and connection outside of the loop and just change the parameter values each time. Most of the time overhead in SQL is setting up the connection. Executing the command generally doesn't have much overhead.

Bottom line: I wouldn't do this for thousands of rows. I would either do it in a loop or look at some other ETL tools, since I'm assuming the source values are coming from some other database, and it will be more efficient to use an ETL tool (which will probably loop anyways) than to insert all of the data in one swoop.

edited May 9, 2013 at 14:27

answered May 9, 2013 at 14:21

D Stanley

153k12 gold badges190 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

csjoseph Over a year ago

thanks for code and you are right about insert all statement and parameters. I will do this with stored procedure.

Nada N. Hantouli Over a year ago

Hey great work! but just a note, if you want to use the INSERT ALL, you need to do it this way: INSERT ALL INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM') INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft') INTO customers (customer_id, customer_name, city) VALUES (999999, 'Anderson Construction', 'New York') SELECT * FROM dual;

Nada N. Hantouli Over a year ago

So, I tried your solution with 5 parameters, and the only change i did was to remove the "INSERT ALL "

Community · Accepted Answer · 2017-05-23 12:06:01Z

2

I'd consider to use binding arrays and Store procedures.

Please, take a look on my answer on this question for examples: C# 2010, ODP.net, call stored procedure passing array

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered May 12, 2013 at 5:39

evgenyl

8,1572 gold badges29 silver badges32 bronze badges

Collectives™ on Stack Overflow

insert multiple rows with parameters in odp.net

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related