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;

        } 

Answer 1

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.

Answer 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