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:
To start with create a simple table in database with the code below :
CREATE TABLE Person
(
PersonId INT PRIMARY KEY,
PersonName VARCHAR(100)
)
Create the simple stored proc sp_InsertByODPNET in oracle database 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.
Key Notes:
1. Use the latest possible version of ODP.NET, as it usually has the bug fixes from all the previous releases.
2. Do not forget to set command.ArrayBindCount.
References:
1. For latest on Oracle’s ODP.NET refer to its details on Oracle site at URL: http://www.oracle.com/technology/tech/windows/odpnet/index.html
|
| Author: Sebastian 13 Jun 2008 | Member Level: Gold Points : 1 |
I was looking for the details based on this topic.Thanks for sharing it with me.
|