Archive for the ‘Solutions’ Category

Connect to Oracle using FluentNHibernate

Sunday, July 10th, 2011

“Could not create the driver from NHibernate.Driver.OracleDataClientDriver, NHibernate, Version=3.1.0.4000,┬áCulture=neutral, PublicKeyToken=aa95f207798dfdb4.” Huh?

All I want to do is use FluentNHibernate to connect to an Oracle database. Is it really so hard? Apparently it’s just a little tricky. The root of the issue is actually the exception “Unable to find the requested .Net Framework Data Provider. It may not be installed.”. For someone who’s only used NHibernate against SQL Server variants, this is a little confusing.

What is a DbProviderFactory and why should I care?

A DbProviderFactory is a Microsoft addition to ADO.NET 2. It centralizes creation logic into one place and let’s you use ICommand, IConnection and other interfaces from System.Data.Common against any database you have an installed provider for. The NHibernate developers liked the idea and ran with it.

So why should you care? Because Oracle only supports this in later versions of the libraries. Unfortunately, it’s also not as simple as referencing a different version, as the user is expected to have a the full Oracle Client Tools installed on their machine. If you’re have a more recent version (10.2 release 2 or later) of Oracle client tools, this error is unlikely to occur as the installer will properly register it.

If you just want to let somebody connect to Oracle without all this existing infrastructure, you need to do something else.

Fixing the issue.

There are two ways to do this. Tell your clients to install a more recent version of the Oracle Data Access Components. This isn’t an option in all circumstances unfortunately.

Otherwise, you can use local DLLs using the following method (from Stack Overflow questions what-is-the-minimum-client-footprint-required-to-connect-c-to-an-oracle-database and what-is-the-minimal-setup-required-to-deploy-a-net-application-with-oracle-clien.

If any of these links don’t get you directly to the relevant page, Oracle may be redirecting you to the root downloads page. Look for the Database category in that case.

  1. Download the Oracle Instant Client. The x86 version is a safe bet, but use 64 bit if you know you need it. I took the Basic package because I’m dealing with possible internationalization issues – if you don’t need multiple language support, you can get the Basic Lite package. Get a package with a version number of at least 11.0.0.
  2. Extract these files into your project root. Add them to the project and set the Copy property of all of them to Copy Always.
    • oci.dll
    • orannzsb11.dll
    • oraociei11.dll
  3. Get a copy of msvcr71.dll from somewhere. I used http://www.dll-files.com/msvcr71.zip?0WDmQ0dHjP. Run a virus scan just in case. Put in your project root, add it to the project and set it to Copy Always.
  4. Download the Oracle Data Access Client XCopy version. Look for the package with the same version. For Oracle Instant Client 11.2.0.1.0, you need ODAC 11.2 Release something or other (long version number) with Xcopy deployment. Extract the file \odp.net20\bin\OraOps11w.dll and add it to the project root, the solution and with the Copy property Copy Always. Extract the file \odp.net20\odp.net\bin\2.x\oracle.dataaccess.client.dll to your project root and Add Reference to it.
  5. Almost finished.
  6. Find Oracle.DataAccess in the references list and look at it’s Version property. Make a note of it, mine was 2.112.2.0.
  7. Add the following XML to the configuration element in your App.Config or Web.Config. If the Version number of your Oracle.DataAccess reference is different, then change it in the type details.
        <system.data>
          <DbProviderFactories>
            <add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" description="Oracle Data Provider for .NET" type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, Version=2.112.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
          </DbProviderFactories>
        </system.data>
    
  8. Test that it works by using this code somewhere. It should write a reference to Oracle.DataAccess.Client and give you a valid factory at the end of it. Obviously without throwing any exceptions.
                using (DataTable providers = DbProviderFactories.GetFactoryClasses())
                {
                    Console.WriteLine("Available Data Providers:");
                    foreach (DataRow prov in providers.Rows)
                    {
                        Console.WriteLine("Name:{0}", prov["Name"]);
                        Console.WriteLine("Description:{0}", prov["Description"]);
                        Console.WriteLine("Invariant Name:{0}", prov["InvariantName"]);
                    }
                }
                
                DbProviderFactory factory = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
    
  9. Done. Ugly. But done.

You should now be able to use this kind of code perfectly happily.

var sessionFactory = Fluently.Configure()
                .Database(
                    OracleDataClientConfiguration
                        .Oracle10
                        .ConnectionString(c => c.FromConnectionStringWithKey("MyConnectionString"))
                        .ShowSql())
                .CurrentSessionContext("thread_static")
                .ProxyFactoryFactory(typeof(NHibernate.ByteCode.Castle.ProxyFactoryFactory))
                .Mappings(m => m.FluentMappings.AddFromAssemblyOf<MyORMObject>())
                .BuildSessionFactory();