Replacing Oracle.DataAccess with managed ODP.net (Oracle.ManagedDataAccess)

From an operations perspective, the problem with the ODP.net (unmanaged version) is that, it has to handle two different driver versions (one for 32 Bit and another for 64 Bit Windows boxes).

 I have been monitoring the managed version of ODP.net  for a while now.  Previous versions had some drawbacks like necessary machine.config entry etc.

Since January 2015 there is a NuGet package available, which is officialy supported by Oracle: https://www.nuget.org/packages/Oracle.ManagedDataAccess/

I have noticed that some days ago and gave it a try.  As it works fairly easy I decided to document my findings here.

Situation:  There is a punch of .Net applications using ODP.net as data access layer and they are running on different environments. Console and WinForms applications on Win 7 Boxes (32 Bit), the same applications on Terminalservers 64 Bit and a punch of Windows-Services and Webs on 64 Bit machines. So, Oracle drivers and managed ODP.net are a pain.

To show an example of how I built an unmanaged application. I took the ODP.net example from the ODP.net developer's guide: http://docs.oracle.com/cd/B14117_01/win.101/b10117/intro003.htm and enhanced it.

Simple console Application

Starting point is a simple ODP.net console application (https://github.com/fuerstd/MODP.net.git)

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 using System.Threading.Tasks;  
   
 using Oracle.DataAccess.Client;  
   
 namespace Test.MODP.net  
 {  
   class Example  
   {  
     public static OracleConnection con; // do not do this in real live  
     void Connect()  
     {  
       con = new OracleConnection();  
       con.ConnectionString = "User Id=scott;Password=TIGER;Data Source=XE";  
       con.Open();  
         
       Console.WriteLine("Connected to Oracle " + con.ServerVersion);  
     }  
     void Close()  
     {  
       con.Close();  
       con.Dispose();  
     }  
     static void Main()  
     {  
       Example example = new Example();  
       example.Connect();  
       // Reads EMPS  
       var command = new OracleCommand("select EMPNO, ENAME from EMP", con);  
       int i = 0;  
       using (var reader = command.ExecuteReader())  
       {  
         while (reader.Read())  
         {  
           Console.Out.WriteLine(String.Format("Employee: {0} Emp#: {1}", reader.GetString(1), reader.GetString(1)));  
           i++;  
         }  
         Console.WriteLine(String.Format("{0} employees read",i));  
       }  
   
       example.Close();  
       Console.WriteLine("Connection closed" );  
     }  
   }  
 }  

Running it:

So far so good.

You can find the Solution on my GitHub page:

Switch to Managed ODP.Net

Install the Oracle Managed ODP.net from: NuGet.org https://www.nuget.org/packages/Oracle.ManagedDataAccess/

Using Package Manager Console (Tools/NuGet Packag Manager/Package Manager Console), type:

 PM> Install-Package Oracle.ManagedDataAccess  

Now you have got an Oracle.Managed.DataAccess under "References"

as well as a NuGet package.config

 <?xml version="1.0" encoding="utf-8"?>  
 <packages>  
  <package id="Oracle.ManagedDataAccess" version="12.1.022" targetFramework="net45" />  
 </packages>  

and config sections in you app.config:

 <?xml version="1.0" encoding="utf-8"?>  
 <configuration>  
  <configSections>  
   <section name="oracle.manageddataaccess.client"  
    type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>  
   </configSections>  
  <system.data>  
   <DbProviderFactories>  
    <remove invariant="Oracle.ManagedDataAccess.Client"/>  
    <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"  
     type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>  
   </DbProviderFactories>  
  </system.data>  
  <runtime>  
   <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">  
    <dependentAssembly>  
     <publisherPolicy apply="no"/>  
     <assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral"/>  
     <bindingRedirect oldVersion="4.121.0.0 - 4.65535.65535.65535" newVersion="4.121.2.0"/>  
    </dependentAssembly>  
   </assemblyBinding>  
  </runtime>  
  <oracle.manageddataaccess.client>  
   <version number="*">  
    <dataSources>  
     <dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) "/>  
    </dataSources>  
   </version>  
  </oracle.manageddataaccess.client>  
 </configuration>  

Refactoring Application

Now you can use the Driver by replacing the "using" statement.

 using Oracle.DataAccess.Client;  

For better understanding ,I copied the Example.cs and named it  ExampleManaged.cs in the examplecode.

 using System;  
 using System.Collections.Generic;  
 using System.Linq;  
 using System.Text;  
 using System.Threading.Tasks;  
   
 using Oracle.ManagedDataAccess.Client;  
   
 namespace Test.MODP.net  
 {  
     
   class ExampleManaged  
   {  
     public static OracleConnection con; // do not do this in real live  
     void Connect()  
     {  
       con = new OracleConnection();  
       con.ConnectionString = "User Id=scott;Password=TIGER;Data Source=XE";  
       con.Open();  
         
       Console.WriteLine("Connected to Oracle using Managed OPD.net " + con.ServerVersion);  
     }  
     void Close()  
     {  
       con.Close();  
       con.Dispose();  
     }  
     static void Main()  
     {  
       ExampleManaged exampleManaged = new ExampleManaged();  
       exampleManaged.Connect();  
       // Reads EMPS  
       var command = new OracleCommand("select EMPNO, ENAME from EMP", con);  
       int i = 0;  
       using (var reader = command.ExecuteReader())  
       {  
         while (reader.Read())  
         {  
           Console.Out.WriteLine(String.Format("Employee: {0} Emp#: {1}", reader.GetString(1), reader.GetInt32(0)));  
           i++;  
         }  
         Console.WriteLine(String.Format("{0} employees read",i));  
       }  
   
       exampleManaged.Close();  
       Console.WriteLine("Connection closed" );  
       Console.WriteLine("STOP");  
   
     }  
   }  
 }  

That is it, no more troubles with unmanaged components in the applications:

Hope that helps.