Best way to insert Bulk data – Array Data Binding | Code Revisited

Finding out ways to insert bulk data into the Oracle Database table, I came across this effective way to insert bulk data into the Oracle table. Of course, there are many ways to insert, but as a programmer, the main objective is performance, aka time consumption.

Array Binding in Oracle

It is the easiest way to Insert/Update bulk data with more parameters, which you can easily build in C#.

After setting up an Object of OracleConnection class and opening connection of Oracle database.

You can setup Oracle Connection as:

OracleConnection conn = new OracleConnection(“UserId=XXAAXX, Password=12XX12; Data Source=Ora”);

conn.Open();

You can create an instance of the OracleCommand with the association of connection object.

OracleCommand cmd = conn.CreateCommand();

Then, you can simply enter the SQL command for Insert/Update using the 

CommandText option.

conn.CommandText = “INSERT INTO STUDENT_DETAILS VALUES (:rollNo, :firstName, ;lastName)”

Create arrays of values to insert into the columns in the table.

Add Parameter by associating it with the command instance.

cmd.Parameters.Add(“rollNo”, OracleDbType.Integer);

cmd.Parameters.Add(“firstName”, OracleDbType.VarChar);

cmd.Parameters.Add(“lastName”, OracleDbType.VarChar);

Set those parameters as:

cmd[“rollNo”].Value = rollNumArr;

cmd[“firstName"].Value = firstNameArr;

cmd[“lastName”].Value = lastNameArr;

Given that, you need to have all the values set up in the array to insert into the table.

After setting up the connection and parameter, its time for the execution.

Before going forward, you need to know the count of records to insert so that you can insert all the records in one go. For that you can bind the count of any of the Array input.

cmd.ArrayBindCount = rollNumArr.Count;

Now, its time for the execution. To be on a safer you can use the try catch block to execute, as sometime it leads to oracle exception of invalid parameter, or invalid number.

cmd.ExecuteArray();

Note: If this doesn’t work, mention the count of Array input inside the brackets like cmd.ExecuteArray(5); if the count is 5.

It is important to dispose the command, before closing the connection. You can simply enter the dispose() with the command object.

cmd.Dispose();

Finally, you need to close the connection.

conn.Close();

With the help of Array Binding, you can insert data in Bulk in a much faster way than any other methods. It can insert more than 1 lakh records within seconds.

If you have any questions or any suggestion to this article, tell us in the comment section.