Introduction
Surprise Surprise.
When i started to google for the above not many relavent pages turned up. I had to frame a lot of different phrases to get the right content. It took me a week. So here it is for everyone under one place.
My first project with .Net couple of years back had Oracle as the backend database ver 8. All i had to do was use the native provider by Microsoft System.Data.Oracleclient. With the examples provided on Microsoft website it was easy to code data access applicatoins. However a lot has changed since then for the good, On the native providers side
Though you could use OleDB and ODBC providers to connect to Oracle. Tthe native providers are considered to be the better in performance. Oracle provides .NET classes for accessing an Oracle database.
These are derived from ADO.NET base classes and thus are highly compatible with everything else. This library is sometimes referred to as ODP.NET and is found in the Oracle.DataAccess.Client namespace. Similarly, Microsoft provides .NET classes for accessing an Oracle database.
These are just the default classes provided with the .NET framework and are found in namespace System.Data.OracleClient. Both of these namespaces go through the Oracle client (OCI interface). Thus, performance should be comparable. I don't see how much difference there can be if you are using only the basic data types such as integers, numbers, and strings.
Oracle claims that ODP.NET is more stable and better performing than the Microsoft provided classes. The debate goes on which of the two providers are better suited for the job.
Here are some more links
But at our workplace we decided to go for Oracle's ODP.NET
On the library to use the native provider (System.Data.SqlClient/OracleClient) side
The helper classes were introduced to access the backend data sources. SqlHelper.cs was the first to be used to access SqlServer database. These helper classes reduced the number of lines you code to access database when compared to using the API (System.Data.SqlClient) directly. Then came OracleHelper.cs which did the same with System.Data.OracleClient
Then evolved the patterns which is changing the way code is written and makes use of maximum reusability. One of the many features it offers. The Microsoft Enterprise Library came out and with it came the Data Access Block. A much simpler way to access data independent of the provider. You could customise the blocks to access data from different data provider / databases.
Enough of stories and lets get into some techy issues.
As i said before i had to access Oracle database from .net applications. Since i have used Enterprise Library Data Access Block with SqlServer database. I wanted to do the same for Oracle as well. That's when the trouble started.
For ODP.Net there wasn't any Data Access Block provided by Oracle. So i decided to customize the Microsoft Enterprise Library to be used with Oracle. But the blocks were desinged to be used with System.Data.OracleClient which is the Microsoft's provider for Oracle. To use Oracle providers (ODP.Net) which uses the following namespaces Oracle.DataAccess.Client; Oracle.DataAccess.Types; you need to tinker with Enterprise Library code in a few places to get it working.
On googling i couldn't find the right stuff as there were different versions of the Ent. Library.
The OracleHelper.cs, then Microsoft Ent. Library ver 1.0, then 1.1, then 2.0, 3.0 and now 3.1. I decided to update the library and post it here for everyone.
Only a few lines need to be changed to get it working for Oracle ODP.Net I took the latest version of the library ver 3.1 released May 2007 and decided to customize it to be used with Oracle ODP.Net. I could have used library ver 2.0 but as i said before there are new features added to latest version 3.1 and the one feature i was interested in was the rolling flat file listener which can be used while logging.
The two main reasons to update the existing library
I. To replace System.Data.OracleClient with Oracle.DataAccess.Client; and Oracle.DataAccess.Types;
II. To add a few lines of code to cater the special requirement of returning Ref Cursor by Oracle St.Procedure
III.Sample code to test the new library dll I. We shall tinker the Ent Lib a bit to get it working with odp.net
I. To replace System.Data.OracleClient with Oracle.DataAccess.Client; and Oracle.DataAccess.Types
- Download and install the latest version of Microsoft Enterprise Library ver 3.1 Can be found here:- http://msdn2.microsoft.com/en-us/library/aa480453.aspx
- Download and install the Oracle ODP.Net from Oracle website Your DLL file should be in :-
C:\oracle\product\11.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll - When it prompts you to install the source do so by using the checkbox.
If you didn't then run the msi on the following path C:\Program Files\Microsoft Enterprise Library 3.1 - May 2007\src - The code for the library gets stored on the following path C:\EntLib3Src\App Blocks
Take a backup of the original src folder in case you need them later - C:\EntLib3Src\App Blocks\Src
Open the solution file EnterpriseLibrary.sln And get to the data project under Data Access Application Block - Add Oracle.DataAccess.dll Reference to the Data Project. Your DLL file should be in :- C:\oracle\product\11.1.0\client_1\odp.net\bin\2.x\Oracle.DataAccess.dll
- Search and replace the following [ Instead you could download and use the updated DLL thats attached to this article]
- File :- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
File :- C:\EntLib3Src\App Blocks\Src\Data\DatabaseConfigurationView.cs
File :- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDataReaderWrapper.cs
Find :- using System.Data.OracleClient;
Replace with:- using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; - File :- C:\EntLib3Src\App Blocks\Src\Data\Configuration\DbProviderMapping.cs Class:- DbProviderMapping
Find :- System.Data.OracleClient
Replace with:- Oracle.DataAccess.Client - File :- C:\EntLib3Src\App Blocks\Src\Data\Configuration\Manageability\
ConnectionStringsManageabilityProvider.cs
Method:- AddAdministrativeTemplateDirectives
Find :- System.Data.OracleClient
Replace with:- Oracle.DataAccess.Client - File :- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
Method:- AddParameter
Find :- public void AddParameter(OracleCommand command, string name, OracleType oracleType, int size,
ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
Replace with:- public void AddParameter(OracleCommand command, string name, OracleDbType oracleType, int size,
ParameterDirection direction, bool nullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
Reason:- OracleType replaced with OracleDbType as the third parameter as that the type name in the odp.net API
- File:- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
Remove:- [OraclePermission(SecurityAction.Demand)] -
Haven't got a clue what that does if someone does please brief on feedback session - File:- C:\EntLib3Src\App Blocks\Src\Data\Oracle\OracleDatabase.cs
Find:- OracleType.Raw
Replace with:- OracleDbType.Raw
Find:- param.OracleType
Replace with:- param.OracleDbType
Find:- OracleType.Cursor
Replace with:- OracleDbType.RefCursor
Find:- parameter.OracleType
Replace with:- parameter.OracleDbType - Compile now and if you get an error do the following Warning as Error : XML comment on - Remove the highlighted errorr content / replace it with approp comment Hopefully it should compile fine now.
Now the DLL that was generated by compiling the above project can be used against both SqlServer and Oracle [ODP.Net]
II. To add a few lines of code to cater the special requirement of returning Ref Cursor by Oracle St.Procedure Whats this Ref Cursor Issue
Sql Server can return a recordset (a set of records) directly via a sql query inside the stored procedure. Unfortunately in Oracle you have to use a out parameter to store the records and retrive it using the .net code. The people who developed the Microsoft Enterprise Library as usual to make things easier for the developer made the library to return a dataset by default even without attaching a ref cursor out parameter.
In doing so they hard coded the output ref cursor name to 'cur_out' which shall be used as default. If you are writing a new stored procedure in Oracle then you can name the ref cursor to be 'cur_out' and the code shall work fine. But with the already existing stored procedure where you might have given different names for out ref cursor the code wouldn't work.
This is an existing issue on Microsoft Enterprise Library and is listed on the todo list for the team. Here is a work around to make the existing stored procedures which has different name than 'cur_out' for the ref coursors to work
- File: - C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\Database.cs
Add:
Cursor Name public abstract void AddCursorOutParameter(
DbCommand command, string CursorName);
- File: - C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\GenericDatabase.cs
Add:-
public override void AddCursorOutParameter(DbCommand command,
string RefCursorName) {
- File: - C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\Sql\SqlDatabase.cs
Add:-
public override void AddCursorOutParameter(DbCommand command,
string RefCursorName) {
- File: - C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\Oracle\OracleDatabase.cs
Add:-
public override void AddCursorOutParameter(DbCommand command,
string RefCursorName)
{
AddParameter(command as OracleCommand, RefCursorName,
OracleDbType.RefCursor, 0, ParameterDirection.Output, true,
0, 0, String.Empty, DataRowVersion.Default, Convert.DBNull);
}
Thats the major part now done.
You should find your DLL file on the following folder: C:\EntLib3Src\App Blocks\Src\Data\bin\Debug\Microsoft.Practices.EnterpriseLibrary.Data.dll with the latest timestamp on it.
III.Sample Quick start code for the new dll library
- Configuation Section
- Quick Start Code
- Configuation Section We shall start with the configuration section.
Let's assume that we need to use both SqlServer database with System.Data.SqlClient and Oracle database with Oracle.DataAccess.Client. Add the following into your web.config/app.config file on .net projects. Now in your application all you need to do to access your default database is to use the following line of code
Database db = DatabaseFactory.CreateDatabase();
If you need to access the Oracle databse which is not set as default database use the following line of code
Database db = DatabaseFactory.CreateDatabase("OracleSource");
III b)Quick Start Code
With Enterprise Library installation comes a set of quick start samples to get you started and the sample for data access block can be found under
C:\EntLib3Src\Quick Starts\Data\CS\DataAccessQuickStart
Create a table in Oracle of the following schema:
| Column Name ID | Pk | Null? | Data Type | Default Histogram |
| DAABCUSTOMERID | 1 | N | INTEGER | No |
| DAABCONTACTNAME | 2 | Y | VARCHAR2 (100) | No |
| DAABADDRESS | 3 | Y | VARCHAR2 (100) | No |
| DAABCITY | 4 | Y | VARCHAR2 (100) | No |
| DAABCOUNTRY | 5 | Y | VARCHAR2 (100) | No |
| DAABPOSTCODE | 6 | Y | VARCHAR2 (10) | No |
Oracle Sql Query
Demonstrates retrieving multiple rows of data using a DataReader
The first example GetCustomerList() is to run a sql query direct. The only change you need to do on the method is use Database db = DatabaseFactory.CreateDatabase("OracleSource"); obviously you need to update the query to fetch data from Oracle database
CREATE OR REPLACE PACKAGE SSM_CS.PKG_DAABCUSTOMER
AS
TYPE CURSOR_RESULTS IS REF CURSOR;
PROCEDURE P_DAABCustomerByPostcode (
I_Reference IN VARCHAR2,
O_Results OUT CURSOR_RESULTS
);
END PKG_DAABCUSTOMER;
CREATE OR REPLACE PACKAGE BODY SSM_CS.PKG_DAABCUSTOMER
AS
PROCEDURE P_DAABCustomerByPostcode (
I_Reference IN VARCHAR2,
O_Results OUT CURSOR_RESULTS
) IS
BEGIN
OPEN O_Results
FOR
Select DAABCUSTOMERID CustomerID,
DAABCONTACTNAME Name,
DAABADDRESS Address,
DAABCITY City ,
DAABCOUNTRY Country,
DAABPOSTCODE PostalCode
From DAABCUSTOMER
WHERE
DAABCUSTOMER.DAABPOSTCODE = I_Reference;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'Unexpected error:' || sqlerrm);
END P_DAABCustomerByPostcode;
END PKG_DAABCUSTOMER;
The .net method to get the details of the customers from the same postcode is as below
public DataSet GetCustomerInPostcode(string PostCode)
{
Database db = DatabaseFactory.CreateDatabase("OracleSource");
string sqlCommand = "PKG_DAABCUSTOMER.P_DAABCustomerByPostcode";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "DAABPOSTCODE", DbType.String, PostCode);
db.AddCursorOutParameter(dbCommand, "O_Results");
DataSet productsDataSet = null;
productsDataSet = db.ExecuteDataSet(dbCommand);
return productsDataSet;
}
Oracle Stored Procedure - Demonstrates retrieving a single item
CREATE OR REPLACE PACKAGE SSM_CS.PKG_DAABCUSTOMER
AS
TYPE CURSOR_RESULTS IS REF CURSOR;
PROCEDURE P_DAABCustomerName (
I_Reference IN INT,
O_CustomerName OUT VARCHAR2
);
END PKG_DAABCUSTOMER;
CREATE OR REPLACE PACKAGE BODY SSM_CS.PKG_DAABCUSTOMER
AS
PROCEDURE P_DAABCustomerName (
I_Reference IN INT,
O_CustomerName OUT VARCHAR2
) IS
V_CUSTOMER DAABCUSTOMER%ROWTYPE;
BEGIN
Select *
INTO
V_CUSTOMER
From
DAABCUSTOMER
WHERE
DAABCUSTOMER.DAABCUSTOMERID = I_Reference;
O_CustomerName := V_CUSTOMER.DAABCONTACTNAME;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'Unexpected error:' || sqlerrm);
END P_DAABCustomerName;
END PKG_DAABCUSTOMER;
The .net method to get the customer name passing customer id
public string GetCustomerName(int customerID)
{
Database db = DatabaseFactory.CreateDatabase("OracleSource");
string sqlCommand = "PKG_DAABCUSTOMER.P_DAABCustomerName";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "I_Reference", DbType.Int32, customerID);
db.AddOutParameter(dbCommand, "O_CustomerName", DbType.String, 100);
db.ExecuteNonQuery(dbCommand);
string results = string.Format(CultureInfo.CurrentCulture,
"Customer Name: {0} ", db.GetParameterValue(dbCommand,
"O_CustomerName")
);
return results;
}
Oracle Stored Procedure - Demonstrates Populating DataSet with Multiple REF Cursors
CREATE OR REPLACE PACKAGE SSM_CS.PKG_DAABCUSTOMER
AS
TYPE CURSOR_RESULTS IS REF CURSOR;
PROCEDURE P_DAABMultipleCursor (
I_Reference IN VARCHAR2,
O_PostcodePresent OUT CURSOR_RESULTS,
O_PostcodeNotPresent OUT CURSOR_RESULTS
);
END PKG_DAABCUSTOMER;
CREATE OR REPLACE PACKAGE BODY SSM_CS.PKG_DAABCUSTOMER
AS
PROCEDURE P_DAABMultipleCursor (
I_Reference IN VARCHAR2,
O_PostcodePresent OUT CURSOR_RESULTS,
O_PostcodeNotPresent OUT CURSOR_RESULTS
) IS
BEGIN
OPEN O_PostcodePresent
FOR
Select DAABCUSTOMERID CustomerID,
DAABCONTACTNAME Name,
DAABADDRESS Address,
DAABCITY City ,
DAABCOUNTRY Country,
DAABPOSTCODE PostalCode
From DAABCUSTOMER
WHERE
DAABCUSTOMER.DAABPOSTCODE = I_Reference;
OPEN O_PostcodeNotPresent
FOR
Select DAABCUSTOMERID CustomerID,
DAABCONTACTNAME Name,
DAABADDRESS Address,
DAABCITY City ,
DAABCOUNTRY Country,
DAABPOSTCODE PostalCode
From DAABCUSTOMER
WHERE
DAABCUSTOMER.DAABPOSTCODE != I_Reference;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20000, 'Unexpected error:' || sqlerrm);
END P_DAABMultipleCursor;
END PKG_DAABCUSTOMER;
public DataSet GetMultipleTables(string PostCode)
{
Database db = DatabaseFactory.CreateDatabase("OracleSource");
string sqlCommand = "PKG_DAABCUSTOMER.P_DAABMultipleCursor";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
db.AddInParameter(dbCommand, "DAABPOSTCODE", DbType.String, PostCode);
db.AddCursorOutParameter(dbCommand, "O_PostcodePresent");
db.AddCursorOutParameter(dbCommand, "O_PostcodeNotPresent");
DataSet custDataSet = null;
custDataSet = db.ExecuteDataSet(dbCommand);
DataTable dtWithPC = custDataSet.Tables[0];
DataTable dtWithoutPC = custDataSet.Tables[1];
return oDataSet;
}
Conclusion
It's been four months since I wrote this article here. A hack to make Microsoft Enterprise Library work on Oracle Native Providers (odp.net). I have learned a lot since then and mostly due to the feedback I got from the codeproject users and some of the research I have been doing. Here it is for those who are looking for the same wisdom.
If you have Oracle Server Version 9+ and Oracle Client Version - ODAC 11g Beta 2 11.1.0.6.10 (can be downloaded from the Oracle website) ( ODAC – Oracle Data Access Connectivity ) then go ahead and use the library with this article. Remember ODAC is still in beta.
If not please do not use this library as you will end up with more problems and issues as myself and some of the readers here did.
In fact I used the above library to connect to Oracle8i and everything worked fine until I was told by Oracle Forum Moderator that when the beta is released it would only support servers Oracle 9+. So I had to track back and think about using Microsoft OracleClient provider for the Enterprise Library. In spite of getting help from Microsoft directly we couldn't sort some of the issues I had.
The Oracle versions we had to use for our projects.
Client: 10 g
Server: 8.1.7
So with the only option left I decided to use the odp.net native providers that came with 10g and ditch Enterprise Library for Oracle Idea. Here is how to talk to Oracle from .NET
When you install Oracle Client you get a DLL file OracleAccess.dll which is the native provider.
You can get your samples project from C:\oracle\product\10.2.0\db_1\odp.net\samples (depends on the installation folder you have chosen)
The same would be the case for other versions of Oracle client and server.
Look out for my next article where I shall post my code for the above project.
Before I Conclude
As soon as I had my article posted there was a negative feedback for bad formatting. That was even before I could set the formatting right in 10 mins. It's easy to criticize but really hard to take. Especially when you take time to put together for the benefit of others.
At the same time I would like to thank these guys who left a positive note. Rilov, Litvinovo and Giniwong. You guys have imparted a new habit in me now. Whenever I read something useful on the net I leave a positive feedback. It makes a big difference. Thanks again.
Updated Feb 4 2010 - By Martin A
