Click here to Skip to main content
Click here to Skip to main content
Go to top

Bulk Insert using ODP.NET

, 7 May 2008
Rate this:
Please Sign up or sign in to vote.
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.

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

License

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

Share

About the Author

viswaInfotech
Software Developer 3i infotech ltd
India India
No Biography provided

Comments and Discussions

 
Questiongetting error object reference not set to and instance Pinmembergirish_kolte21-Sep-12 20:35 
GeneralMy vote of 1 PinmemberSyed Javed5-Dec-10 0:56 
QuestionCan we do Bulk Insert without a Procedure in the back_end? PinmemberMehdiAnis27-Oct-08 6:23 
AnswerRe: Can we do Bulk Insert without a Procedure in the back_end? PinmemberJinxter8-Jun-09 19:49 
GeneralAlign the text Pinmemberbalajikrishn8-May-08 3:42 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140916.1 | Last Updated 8 May 2008
Article Copyright 2008 by viswaInfotech
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid