Using Enterprise Library 2 with Oracle ODP.NET

If you are using Enterprise Library 5 then you want this link Enterprise Library 5 with ODP.NET.

The Microsoft has discontinued the support for their Oracle client library, found in the namespace “System.Data.OracleClient“, and because of that many developers have switched to the Oracle library which they call ODP.Net, found in the namespace “Oracle.DataAccess.Client“. Oracle also claims that their client performs better. To be honest, I didn’t have performance issues with any of those libraries but I decided to follow with the ODP.NET, so let’s see how we can use it.

We first need to download the ODP.NET library. After that, find the files and add the “Oracle.DataAccess” as a referece into your project. You may be able to find it at the .Net tab from the Add Reference dialog.

Add Reference Dialog

Then we must configure the web.config file. You can use the Enterprise Library Configuration software to modify it or open the file and copy the code below:

<configSections>
    <section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data" />
  </configSections>
  <dataConfiguration defaultDatabase="FooDatabase">
    <providerMappings>
      <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase, Microsoft.Practices.EnterpriseLibrary.Data"
        name="Oracle.DataAccess.Client" />
    </providerMappings>
  </dataConfiguration>
  <connectionStrings>
    <add name="FooDatabase" connectionString="User ID=my_user;Password=password;Data Source=my_schema"
      providerName="Oracle.DataAccess.Client" />
  </connectionStrings>

Then, to call the Database I’ve created an abstract class containing a property which return the Database object.

    public abstract class DataBase
    {
        protected Database DB
        {
            get
            {
                return DatabaseFactory.CreateDatabase();
            }
        }
    }

That done,  we can extend the data classes of our application and use that property when we need. See  the example below:

    public class UserData : DataBase
    {
        internal void Delete(int userId)
        {
            DB.ExecuteNonQuery("PKG_USER.DELETE", userId);
        }

For output parameters, pass null and then use DB.GetParameterValue to get the returned value.

You can notice that in my web.config, I’ve set a provider map. One issue that came up in the company I work is that we don’t store the database configuration in the web.config, instead we have only one file in the server with the connection parameters of all applications we have and only the server administrator can see/change it. This was my issue and you may have a diffent one that the solution i had can help as well.

I end up creating my own provider mapping file. My web.config file now looks like this:

<dataConfiguration defaultDatabase="FooDatabase">
    <providerMappings>
      <add databaseType="MyNamespace.Oracle.OracleOdpDatabase, MyNamespace"
        name="Oracle.DataAccess.Client" />
    </providerMappings>
  </dataConfiguration>
<connectionStrings>
    <add name="FooDatabase" connectionString="User ID={foo_user};Password={foo_password};Data Source={foo_source}"
      providerName="Oracle.DataAccess.Client" />
  </connectionStrings>

See that my connectionString now have variables instead of the values and my providerMappings is changed to a custom one.

The class that handles my custom providerMapping is written like this:

using System.Collections.Specialized;
using System.Configuration;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;
using Oracle.DataAccess.Client;

namespace MyNamespace.Oracle
{
    [DatabaseAssembler(typeof(OracleOdpDatabaseAssembler))]
    public class OracleOdpDatabase : GenericDatabase
    {
        public OracleOdpDatabase(string connectionString, DbProviderFactory dbProviderFactory)
            : base(connectionString, dbProviderFactory)
        {
        }

        protected override void DeriveParameters(DbCommand discoveryCommand)
        {
            OracleCommandBuilder.DeriveParameters(discoveryCommand as OracleCommand);
        }
    }

    public class OracleOdpDatabaseAssembler : IDatabaseAssembler
    {
        private static NameValueCollection connectionStrings = new NameValueCollection();

        public Database Assemble(string name, ConnectionStringSettings connectionStringSettings, IConfigurationSource configurationSource)
        {
            DbProviderFactory providerFactory = DbProviderFactories.GetFactory(connectionStringSettings.ProviderName);

            // checks whether there is a connection string already or needs to read it again
            if (connectionStrings[name] == null)
                connectionStrings[name] = ReplaceConfigVariables(connectionStringSettings.ConnectionString);

            return new OracleOdpDatabase(connectionStrings[name], providerFactory);
        }
    }
}

You can see that i replaced the connection string variables before passing it to my custom provider class. I don’t show the replacing code here so we don’t lose focus.

Update: I created a sample project so you can download the source code, take a look at it and test it quickly: Download the Sample

References:

http://entlibcontrib.codeplex.com/wikipage?title=OracleDataProvider41

http://forums.oracle.com/forums/thread.jspa?threadID=399700

About these ads

10 responses to “Using Enterprise Library 2 with Oracle ODP.NET

  1. ....$விக்னேஷ்

    hey its gr8 stuff. thank u so much 4 providing here!

  2. Luis Fernando Forero

    Could you please specify to which name space does this classes and interface belong in the custom providerMapping class?. I´m geting “The type or namespace name ‘DatabaseAssembler’ could not be found (are you missing a using directive or an assembly reference?) ” message

    DatabaseAssembler
    IDatabaseAssembler
    IConfigurationSource

    • devstuffs

      @Luis Fernando,

      You need to add a reference to
      Microsoft.Practices.EnterpriseLibrary.Common.dll and Microsoft.Practices.EnterpriseLibrary.Data.dll in your project. Those dlls contains the classes you are looking for.
      Then, in your custom providerMapping, add the usings:

      using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

      for the IConfigurationSource, and:

      using Microsoft.Practices.EnterpriseLibrary.Data;
      using Microsoft.Practices.EnterpriseLibrary.Data.Configuration;

      for the DataAssembler and IDataAssembler classes.

  3. Himanshu

    I am usinh .NET FW 4.0 and I am not able to connect to Oracle DB using ODP.NEt dll in ENT lib. Every tiome I try to call Stpred Proc I get message
    ORA-06550: line 1, column 7:
    PLS-00306: wrong number or types of arguments in call to

    But when I change .net fw to use 3.5 it works fine.

    Help is much appreciated on this.

    • devstuffs

      Hey Himanshu,

      I updated the post with a sample project I created to demonstrate the Enterprise Library working with both versions of .Net Framework.

      The only thing I had to change when using the .Net Framework 4.0 and you will see in the App.Config, is that I have to registrate the Database Provider Factory from Oracle Client. This is because the ODP.NET version is older than Visual Studio 2010 and .Net Framework 4.0, so when you install the client it will only register itself for the older versions of the framework.

      What I recommend you to do, is to add this Provider Factory configuration to your machine.config (under C:\windows\Microsoft.NET\Framework\v4.0.30319), so you don’t have to do it for all your projects.

  4. Erik Spyder

    I´m geting “The type or namespace name ‘DatabaseAssembler’ could not be found (are you missing a using directive or an assembly reference?) ” error message in the downloaded sample. I’m using Enterprise Library 5.

  5. Tim

    Hey Dev,

    I’m still using the System.Data.OracleClient and it’s a big task to change to the Oracle provider now. I have an issue while adding a parameter as DBType.String to a column of type NVARCHAR. The issue is i have a separate character set defined for the national columns and while passing from .net, i get garbage values while i’m able to do it from a db editor. So i’m guessing that the DBType.String only uses the main character set for encoding and doesn’t care if we’re trying to insert into a NVARCHAR column or not. Can you back me up on this?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 79 other followers

%d bloggers like this: