Bulk Insert using ODP.NET

Introduction

This article explains the bulk insert concept from the front end.

Background

The pre-requisite for this is ODP.NET which can be downloaded from this link.

Using the Code

First you have to add the OracleAccess to your project reference.

Select Oracle.DataAccess from .NET components.

Import

Import the Oracle.DataAccess in your class file.

// import oracle Dataacces in your class file
using Oracle.DataAccess.Client;

After importing the Oracle.DataAccess.Client, create the connection string.

Connection Details

// create the connection string
// Give your Database details in the connection string
OracleConnection objConn = 
    new OracleConnection("data source=***;user id=***;password=***");

Bulk Insert

To bulk insert from the front end itself, you have to create an array object and add the values into it. The array will be passed as the parameter to the procedure, which will contain the insert query.

//creating an array
string strEmpName = new string[0];
string strEmpAge = new string[0];

First, the array size is initialized to length 0. Before adding a value into an array, the size of the array should be resized.

// Resizing the array
public void Resize_Array(int arrCount)
{
    Array.Resize(ref strEmpName , arrCount + 1);
    Array.Resize(ref strEmpAge, arrCount + 1);        
}

To add the values into the array:

// Add values into array
Resize_Array(1);
strEmpName[0] ="sam";
strEmpAge[0]="25";

Resize_Array(2);
strEmpName[1] ="john";
strEmpAge[2]="25";

Backend Procedure

Create the procedure in Oracle:

// Create a procedure in your back end
create or replace PROCEDURE Sample_Bulk_Insert
(
  INS_EMP_NAME       IN   VARCHAR2,
  INS_EMP_AGE        IN   NUMBER,
)
is
begin

INSERT INTO EMP(NAME,AGE)
VALUES (INS_EMP_NAME, INS_EMP_AGE);

 COMMIT;
end;

After creating the procedure, call the procedure from your code and pass the array as the parameter to the procedure.

Calling Procedure

// create an Oracle command object
OracleCommand objCom = new OracleCommand();

// set the command type as stored procedure
objCom.CommandType = CommandType.StoredProcedure;
objCom.CommandText = "Sample_Bulk_Insert";

// add the parameter you need to pass to the procedure
OracleParameter param1 =objCom.Parameters.Add("INS_EMP_NAME",strEmpName);
OracleParameter param2 =objCom.Parameters.Add("INS_EMP_AG",strEmpAge);

param1.Size=200;
param2.Size=10;

/* The array length has to be passed as the Arraycount so that ODP.NET will execute 
the Oracle procedure from the backend, the number of times mentioned in arraycount. 
This loop is done by Oracle itself. So in a single connection 100's of records 
can be inserted.
*/
objCom.ArrayBindCount = strEmpName.Length;

objCom.ExecuteNonQuery();