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.
using Oracle.DataAccess.Client;
After importing the Oracle.DataAccess.Client
, create the connection string.
Connection Details
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.
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.
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:
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
OracleCommand objCom = new OracleCommand();
objCom.CommandType = CommandType.StoredProcedure;
objCom.CommandText = "Sample_Bulk_Insert";
OracleParameter param1 =objCom.Parameters.Add("INS_EMP_NAME",strEmpName);
OracleParameter param2 =objCom.Parameters.Add("INS_EMP_AG",strEmpAge);
param1.Size=200;
param2.Size=10;
objCom.ArrayBindCount = strEmpName.Length;
objCom.ExecuteNonQuery();
History
- 8th May, 2008: Initial post
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.