When I was writing a post about error handling when working with SqlClient, I wrote that OracleClient is not as rich as SqlClient when it comes to getting information about database exceptions. But then I realized that you can get the same functionality if you switch from built-in System.Data.OracleClient to Oracle.DataAccess.Client, which is the Oracle Data Provider for .NET (ODP.NET). It can be downloaded from the following Oracle Technology Network Web Site. One thing you need to be aware is that the installation comes with the Oracle database client, so any connection to Oracle database will be done by this client, therefore you need to remember to update tnsnames.ora file to point to the right host and database instance. This ODP.NET greatly enhances capability of Oracle Client - mostly by bringing support for Oracle bulk copy operations and array binding - plus having aforementioned additional parameters in OracleException class (OracleError collection similar to SqlError collection in SqlException class) . This functionality even outweighs SqlClient due to the fact of having array binding, which allows in one server round trip to execute a command (ie. update, insert, delete or stored proc) as many times as value of ArrayBindCount parameter in OracleCommand class. This feature allows controlling batch size and enhancing the speed as the array of row data is copied into SQL engine. Usually when a developer has to run ie. delete command against a few rows, he would take all PK ids of rows to delete and run in a loop single delete commands passing one by one all ids as a value of a parameter, which would cause n database round trips. Or he would implement PL/SQL stored proc to accept a table as an input parameter and then withing the stored proc a delete statement would be executed in some sort of loop to delete each single row with the given id. ODP.NET has made it simpler - all you need is 1 (!) DML command with parameter(s) and you need to pass an array of values. Simple and very efficient. As far as Oracle bulk copy, this is done using OracleBulkCopy class that is an equivalent of SqlBulkCopy class in SqlCleint. It also accepts as a parameter Data Reader or DataTable, so you can bulk copy data to a destination table. Below is simple console app that demonstrates how to use 2 features of ODP.NET - OracleBulkCopy class and array binding. The application uses Scott username to connect to database and also it uses EMP table from Scott schema and also EMP_TEST table which is a copy of EMP table needed to demonstrate Oracle bulk copy and created by running the following command (using where clause ensures that a created table will be empty):
ODP.NET.cs
using System;using System.Collections.Generic;using System.Linq;using Oracle.DataAccess.Client;namespace ODP{public class OdpNet
{public String ConnectionString { get; set; }public OdpNet(String connectionString){if (connectionString == null) throw new ArgumentNullException("connectionString");
ConnectionString = connectionString;
}
public void OracleBulkCopy_Test()
{try{using (var conn = new OracleConnection(ConnectionString))
{using (var cmd = conn.CreateCommand()){cmd.CommandText = "select * from Scott.EMP";conn.Open();
using (var reader = cmd.ExecuteReader()){if (reader.HasRows){using (var cpy = new OracleBulkCopy(conn))
{cpy.DestinationTableName = "Scott.EMP_TEST";cpy.WriteToServer(reader);
Console.WriteLine("Copying to EMP_TEST successfull");}
}
}
}
}
}
catch (OracleException ex){Console.WriteLine("Oracle error while copying to EMP_TEST: " + ex.Message);}
catch (Exception ex){Console.WriteLine("General error while copying to EMP_TEST: " + ex.Message);}
}
public Int32[] GetEmployeesId(){try{using (var conn = new OracleConnection(ConnectionString))
{using (var cmd = conn.CreateCommand()){cmd.CommandText = "Select EmpNo From Scott.EMP_TEST";conn.Open();
ICollection<int> empId = new List<int>();
using (var reader = cmd.ExecuteReader()){while (reader.Read())empId.Add(reader.GetInt32(0));
}
Console.WriteLine("Getting employee numbers successfull. Total found {0}",empId.Count());return empId.ToArray();}
}
}
catch (OracleException ex){Console.WriteLine("Oracle error while getting all employee numbers: " + ex.Message);}
catch (Exception ex){Console.WriteLine("General error while getting all employee numbers: " + ex.Message);}
return null;
}
public void OracleBindArray_Test(Int32[] employeesId)
{if (employeesId != null && employeesId.Length > 0)
{try{using (var conn = new OracleConnection(ConnectionString))
{using (var cmd = conn.CreateCommand()){cmd.CommandText = "Delete Scott.EMP_TEST Where empno=:1";cmd.ArrayBindCount = employeesId.Length;
var paramEmpno = new OracleParameter{OracleDbType = OracleDbType.Int32,
Value = employeesId
};
cmd.Parameters.Add(paramEmpno);
conn.Open();
var count = cmd.ExecuteNonQuery();
Console.WriteLine("Total employees deleted: {0}", count);}
}
}
catch (OracleException ex){Console.WriteLine("Oracle error while deleting employees: " + ex.Message);}
catch (Exception ex){Console.WriteLine("General error while deleting employees: " + ex.Message);}
}
}
}
}
Program.cs
using System;namespace ODP{class Program{const String ConnectionString = "Data Source = orcl; User ID = scott; Password= tiger;";
static void Main()
{var client = new OdpNet(ConnectionString);client.OracleBulkCopy_Test();
var empId = client.GetEmployeesId();
if (empId == null)
Console.WriteLine("No employees found");elseclient.OracleBindArray_Test(empId);
Console.Read();
}
}
}
The application outputs as follows:
0 comments: