Click here to Skip to main content
16,020,345 members
Articles / Programming Languages / SQL
Article

Bulk Insert using ODP.NET

Rate me:
Please Sign up or sign in to vote.
1.53/5 (5 votes)
7 May 2008CPOL 94.2K   19   6
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.

add reference

Select Oracle.DataAccess from .NET components.

Image 2

Import

Import the Oracle.DataAccess in your class file.

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

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

Connection Details

C#
// 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.

C#
//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.

C#
// 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:

C#
// 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:

SQL
// 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

C#
// 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer 3i infotech ltd
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionWhat are the disadvantages of this approach Pin
Mukund_mukund28-Oct-14 4:55
Mukund_mukund28-Oct-14 4:55 
Questiongetting error object reference not set to and instance Pin
girish_kolte21-Sep-12 20:35
girish_kolte21-Sep-12 20:35 
GeneralMy vote of 1 Pin
Syed J Hashmi5-Dec-10 0:56
Syed J Hashmi5-Dec-10 0:56 
very bad
QuestionCan we do Bulk Insert without a Procedure in the back_end? Pin
MehdiAnis27-Oct-08 6:23
MehdiAnis27-Oct-08 6:23 
AnswerRe: Can we do Bulk Insert without a Procedure in the back_end? Pin
Jinxter8-Jun-09 19:49
Jinxter8-Jun-09 19:49 
GeneralAlign the text Pin
balajikrishn8-May-08 3:42
balajikrishn8-May-08 3:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.