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.

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