Oracle Data Provider for .NET

Valid for Environment: ODP.NET 9.2.0.1 or above with NET 1.1 or above on Oracle 9i Database or above

Oracle has been investing a lot in connectivity between Oracle Database and .NET. Oracle provided ODP.NET (Oracle Provider for .NET) to have connectivity between Oracle Database and .NET. Well, I have not gone into its details but it is believed that it is better to use ODP.NET to connect to Oracle database from .Net environment. May be since both are Oracle products they may have optimized ODP.NET for better performance, may be…

Anyways, let us see what we have got in it for Multiple Inserts in one network roundtrip. ODP.NET provides us OracleCommand object which is quite similar to SQLCommand Object. OracleCommand object supports taking arrays as parameters. The only thing is while using array one needs to provide ArrayBindCount, which informs ODP.NET the number of records to expect and process from the array. Simply put, the code is exactly same as if we are calling a stored proc by providing two simple parameters, just that, rather than providing simple value to a parameter, we need to specify an array of values. And along with that we specify ArrayBindCount same as Array Length, to enable ODP.NET to do multiple inserts. I am sure the code below will help you to understand this better:

Create the simple stored proc sp_InsertByODPNET in oracle database similar to that of sp_BatchInsert in the code above and follow the .NET Code below :

private void btnOracleODPNET_Click(object sender, System.EventArgs e)

{

int[] arrPersonId = {Convert.ToInt32(txtPersonId1.Text.Trim()), Convert.ToInt32(txtPersonId2.Text.Trim()), Convert.ToInt32(txtPersonId3.Text.Trim()), Convert.ToInt32(txtPersonId3.Text.Trim())};

 

string[] arrPersonName = {txtPersonName1.Text.Trim(), txtPersonName2.Text.Trim(), txtPersonName3.Text.Trim(), txtPersonId4.Text.Trim()};

 

// You need to put the Namespace "using Oracle.DataAccess.Client;" to compile this code

OracleConnection connection = new OracleConnection(oracleConnectionString);

 

OracleCommand command = new OracleCommand("sp_InsertByODPNET", connection);

command.CommandType = CommandType.StoredProcedure;

 

// We need to tell the ODP.NET the number of rows to process

//and that we can do using "ArrayBindCount" property of OracleCommand Object

command.ArrayBindCount = arrPersonId.Length;

 

// For specifying Oracle Data Types for Parameters you need to use "Oracle.DataAccess.Types" namespace.

command.Parameters.Add("@PersonId", OracleDbType.Int16);

command.Parameters[0].Value = arrPersonId;

 

command.Parameters.Add("@PersonName", OracleDbType.Varchar2, 100);

command.Parameters[1].Value = arrPersonName;

 

connection.Open();

int recordsInserted = command.ExecuteNonQuery();

connection.Close();

 

MessageBox.Show("Number of records affected : " + recordsInserted.ToString());

}

 

Couple of people have raised issues against this approach as they encountered Memory Leak while using ODP.NET along with .NET. But also, I have heard that the issues are resolved with recent version of ODP.NET and .NET and patches. You may like to do your research before adopting this approach.

 

------reference the  Neeraj Saluja