Click here to Skip to main content
15,867,704 members
Articles / Programming Languages / C#

Microsoft Enterprise Library Data Access Block [DAAB] on Oracle Provider [ODP.NET]

Rate me:
Please Sign up or sign in to vote.
4.88/5 (32 votes)
4 Feb 2010CPOL11 min read 360.2K   4.3K   106   64
Microsoft Enterprise Library Data Access Block [DAAB] on Oracle Provider [ODP.NET]

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

  1. 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
  2. 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
  3. 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
  4. 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
  5. 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
  6. Search and replace the following [ Instead you could download and use the updated DLL thats attached to this article]
    1. 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;
    2. File :- C:\EntLib3Src\App Blocks\Src\Data\Configuration\DbProviderMapping.cs Class:- DbProviderMapping
      Find :- System.Data.OracleClient
      Replace with:- Oracle.DataAccess.Client
    3. File :- C:\EntLib3Src\App Blocks\Src\Data\Configuration\Manageability\
      ConnectionStringsManageabilityProvider.cs
      Method:- AddAdministrativeTemplateDirectives
      Find :- System.Data.OracleClient
      Replace with:- Oracle.DataAccess.Client
    4. 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

    5. 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
    6. 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
    7. 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

  1. File: - C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\Database.cs
    Add:
    C#
    //Update to add cursor as output parameter due to cur_out issue /// 
    /// Method added to oversome ref cursor issue - 'cur_out' hardcoded /// 
    ///Command 
    Cursor Name public abstract void AddCursorOutParameter(
        DbCommand command, string CursorName);
  2. File: - C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\GenericDatabase.cs
    Add:-
    C#
    /// Does nothing in Generic Database. Only here for compliance with 
    /// DBCommandWrapper /// 
    /// Db Command 
    /// Ref Cursor for Oracle 
    public override void AddCursorOutParameter(DbCommand command, 
        string RefCursorName) { // Do Nothing }
  3. File: - C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\Sql\SqlDatabase.cs
    Add:-
    C#
    /// Does nothing in SQL Server. Only here for compliance with 
    /// DBCommandWrapper /// 
    /// Db Command 
    /// Ref Cursor for Oracle 
    public override void AddCursorOutParameter(DbCommand command, 
        string RefCursorName) { // Do Nothing }
  4. File: - C:\Program Files\Microsoft Enterprise Library January 2006\Src\Data\Oracle\OracleDatabase.cs
    Add:-
    C#
    /// Added to over come the issue with Ref Cursor and 'Cur_Out' /// 
    /// The name of the Reference Cursor 
    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

  1. Configuation Section
  2. Quick Start Code
  3. 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

C#
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

C#
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 IDPkNull?Data TypeDefault Histogram
DAABCUSTOMERID1NINTEGERNo
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

SQL
CREATE OR REPLACE PACKAGE SSM_CS.PKG_DAABCUSTOMER             
AS
--/ Application                    :DEMO DAAB OF MICROSOFT ENT LIBRARY
--/ Author                         :Rain Man
TYPE CURSOR_RESULTS IS REF CURSOR;
PROCEDURE P_DAABCustomerByPostcode  (
    I_Reference IN  VARCHAR2,
    O_Results   OUT CURSOR_RESULTS
);
END PKG_DAABCUSTOMER;

SQL
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

C#
public DataSet GetCustomerInPostcode(string PostCode)
{
    // Create the Database object.
    // The database service is determined through configuration.
    Database db = DatabaseFactory.CreateDatabase("OracleSource");
    string sqlCommand = "PKG_DAABCUSTOMER.P_DAABCustomerByPostcode";
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
    
    // Retrieve products from the specified category.
    db.AddInParameter(dbCommand, "DAABPOSTCODE", DbType.String, PostCode);
    // The name O_Results could be anything need not be the same as 
    // the one in stored procedure
    db.AddCursorOutParameter(dbCommand, "O_Results");

    // DataSet that will hold the returned results                  
    DataSet productsDataSet = null;
    productsDataSet = db.ExecuteDataSet(dbCommand);

    return productsDataSet;
}

Oracle Stored Procedure - Demonstrates retrieving a single item

SQL
CREATE OR REPLACE PACKAGE SSM_CS.PKG_DAABCUSTOMER
AS
--/ Application                    :DEMO DAAB OF MICROSOFT ENT LIBRARY 
--/ Author                         :RAIN MAN
TYPE CURSOR_RESULTS IS REF CURSOR;



PROCEDURE P_DAABCustomerName     (
    I_Reference         IN      INT,
    O_CustomerName      OUT     VARCHAR2
     );
END PKG_DAABCUSTOMER;

SQL
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

C#
public string GetCustomerName(int customerID)
{
    // Create the Database object, using the default database service. The
    // default database service is determined through configuration.
    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

SQL
CREATE OR REPLACE PACKAGE SSM_CS.PKG_DAABCUSTOMER
AS
--/ Application                    :DEMO DAAB OF MICROSOFT ENT LIBRARY
--/ Author                         :RAIN MAN
--/Notes                       : Silly scenario just to create two cursors 
--/                              in the procedure
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;

SQL
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;

C#
public DataSet GetMultipleTables(string PostCode)
{
    // Create the Database object, using the default database service. The
    // default database service is determined through configuration.        
    Database db = DatabaseFactory.CreateDatabase("OracleSource");
    string sqlCommand = "PKG_DAABCUSTOMER.P_DAABMultipleCursor";
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    // Retrieve products from the specified category.
    db.AddInParameter(dbCommand, "DAABPOSTCODE", DbType.String, PostCode);
    db.AddCursorOutParameter(dbCommand, "O_PostcodePresent");
    db.AddCursorOutParameter(dbCommand, "O_PostcodeNotPresent");
    // DataSet that will hold the returned results                  
    DataSet custDataSet = null;
    custDataSet = db.ExecuteDataSet(dbCommand);
    //DataTable with Postcode
    DataTable dtWithPC = custDataSet.Tables[0];
    DataTable dtWithoutPC = custDataSet.Tables[1];
   // **** Code to attach the first datatable - dtWithPC to a grid/list

   // **** Code to attach the second datatable - dtWithoutPC to a grid/list
   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

 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer
United Kingdom United Kingdom
Software Developer

Comments and Discussions

 
QuestionCannot get .CreateConnection or .ExecuteDataSet(DbCommand) to work Pin
mungthewicked8-Apr-11 9:24
mungthewicked8-Apr-11 9:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.