Bulk insert/update

[mkashyap]

I am new to dotConnect and have downloaded a trial version to test it out to see if it fits our needs. We are looking at dotConnect for Oracle.

What is the best way to do a bulk insert or update? Do we have to write a PL/SQL package to do that or is there another way. There is a way to do this with ORacle dataaccess dll by setting the insert statement as the command text of the command object and passing the insert values as arrays.

When I try something similar with dotConnect, it does not seem to work. I get a ORA-01484: arrays can only be bound to PL/SQL statements.

Here is what I am trying to do:

Dim arCompID() As String = {"T1", "T2", "T3"}
Dim arDesc() As String = {"Test1", "Test2", "Test3"}

Dim prmComp As New OracleParameter
prmComp.OracleDbType = OracleDbType.VarChar
prmComp.ParameterName = "p1"
prmComp.Value = arCompID

Dim prmDesc As New OracleParameter
prmDesc.OracleDbType = OracleDbType.VarChar
prmDesc.ParameterName = "p2"
prmDesc.Value = arDesc

cmd.Parameters.Add(prmComp)
cmd.Parameters.Add(prmDesc)
cmd.CommandType = CommandType.Text
cmd.CommandText = "Insert into F21.fadm_company (company_id, description) values (:p1, :p2)"
cmd.ExecuteNonQuery()

Any help is appreciated. We are trying to use dotConnect in the direct mode and not use Oracle client.

Thanks,

[Shalex]

Please call the ExecuteArray method. For detailed information, please refer to http://www.devart.com/dotconnect/oracle ... yBind.html .

[mkashyap]

Thanks for your response. It worked.

One question on how .ExecuteArray works---this method takes an input parameter which is essentially the number of records that needs to be inserted. Does it hit the database that many times or is it that once it reaches the database, it executes the statements that many times?

Thanks.

[Shalex]

The main advantage of using array binding is that only one connection to the server is used to execute several queries, which speeds up queries execution compared to consecutive invocation. During the ExecuteArray invocation, query and parameters reach the database once, and actual execution is performed at the database side.

[ptzedakis]

Hello Shalex,

If we don't use ExecuteArray(int iters) and we need to insert a number of records in an ORACLE table, we would use an OracleTransaction if we wanted to make sure that either ALL records have been inserted or NO records at all. Then according to whether the command successfully commit or rollback, we would be notified and act accordingly.

If we do use ExecuteArray(int iters) to insert many rows to a table, is it guaranteed that ALL records will be inserted? What happens if during OracleCommand.ExecuteArray(int iters) an OracleException is thrown in our application? How do we ensure that in such case NO records have been written to the ORACLE db ?

Thank you for your help.

[StanislavK]

The error occurred while inserting multiple rows will cancel the whole operation because the OracleCommand.ExecuteArray function uses array DML operations.