|
In an application one of the biggest overheads of data insertion into the database is making round trips to the DB for insertion purpose. ADO.Net provides a mechanism to copy a bulk of data to SQL server using SqlBulkCopy. But for inserting bulk data into the Oracle database we need the help of ODP.NET (Oracle Data Provider for .NET) . |
|
Assuming that ODP.NET is already installed, add a reference for “Oracle.DataAccess” to your solution. The code below demonstrates how bulk copy for oracle can be achieved using Array Binding. |
try
{
//Specify the number of records to be inserted in a bulk
int numRecords = 5;
//Specify the array variables for the different columns
string[] firstNames = { "LISA", "JORDAN", "LEON", "RACHEL", "HELEN" };
string[] lastNames = { "BROWN", "HOFF", "GOODWIN", "HARRIS", "GEORGE" };
DateTime[] dobs = { Convert.ToDateTime("01/22/87"), Convert.ToDateTime("10/14/93"), Convert.ToDateTime("01/08/86"), Convert.ToDateTime("01/22/78"), Convert.ToDateTime("11/13/80") };
//Establish a connection to the Oracle server
OracleConnection ocnn =
new OracleConnection("Data Source=MyDS;UserID=MyId;Password=**");
//Generate the Query for insertion
String query = "INSERT into TBLEMPLOYEES (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DOB) values (SEQ_EMPLOYEE_ID.nextval, :first_name, :last_name, :dob)";
//Open the connection and bind the command object
ocnn.Open();
OracleCommand ocmd = ocnn.CreateCommand();
ocmd.CommandText = query;
ocmd.CommandType = CommandType.Text;
ocmd.BindByName = true;
// To use ArrayBinding, we need to set ArrayBindCount
ocmd.ArrayBindCount = numRecords;
// Instead of single values, we pass arrays of values as parameters
ocmd.Parameters.Add(":first_name", OracleDbType.Varchar2, firstNames,
ParameterDirection.Input);
ocmd.Parameters.Add(":last_name", OracleDbType.Varchar2, lastNames,
ParameterDirection.Input);
ocmd.Parameters.Add(":dob", OracleDbType.Date, dobs, ParameterDirection.Input);
int result = ocmd.ExecuteNonQuery();
ocnn.Close();
}
catch
{}
Note: The above code uses an Oracle Sequence “SEQ_EMPLOYEE_ID” to set the auto increment nature of the column EMPLOYEE_ID of the DB.