Bulk Insert using ODP.NET






1.53/5 (5 votes)
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();
History
- 8th May, 2008: Initial post