Oracle Managed ODP.NET

Note:This documentation/guide is based on Managed ODP.NET v11.2.0.3.50 Beta

Download Managed ODP.NET from Oracle Technology Network.

Finally Oracle has released a pure .NET based client for connecting and working with Oracle databases from your CLR world without the dependencies lying around in the GC ignorant world. With a reference added to the Oracle.ManagedDataAccess.dll which weighs a mere 6+ MB’s (against 150+ MB’s previously) you are ready to get started with you day-to-day interaction to the 10g R2 or higher Oracle Databases.

It is not without its quirks though, but for me the advantages have outweighed the issues and I have started using them in my current development even though it is still in Beta.

Let’s get started with accomplishing something than me doing the happy dance any longer.

1. To start off, get the package from the download link I have provided earlier.
2. Open your .NET project (should be based on .NET Framework 4 or higher).
3. Provide the connection information in the configration file (web.config, app.config)
4. Extract and attach Oracle.ManagedDataAccess.dll from the package you downloaded.
5. Write your data access code
6. Profit?

Configuration

With Managed ODP.NET now you would be able to provide the connection configurations as part of the application configuration settings. For a detailed information on all possible configuration settings check out the documentation on OTN.

Generally you would simply add the needed connection string as

1
2
3

<connectionStrings>
	<add name="MyDB" connectionString="Data Source=oracle;User Id=scott;Password=tiger;" />
</connectionStrings>

and use that in your application. But since the Managed ODP.NET is self contained, Oracle is providing 3 options for resolving the TNS “oracle” in the above connection string;

1. you can either replace the TNS name with the full descriptor. Example:

1

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myservername)(PORT=1521))(CONNECT_DATA=(FAILOVER_MODE=(TYPE=select)(METHOD=basic))(SERVER=dedicated)(SERVICE_NAME=oracle)))

2. under the oracle.manageddataaccess.client configuration section provide settings for the TNS_ADMIN variable

1
2
3
4
5
6
7
8
9

<configuration>
  <oracle.manageddataaccess.client>
    <version number="*">
      <settings>
        <setting name="TNS_ADMIN" value="C:\oracle\network\admin"/>
      </settings>
    </version>
  </oracle.manageddataaccess.client>
</configuration>

3. under the oracle.manageddataaccess.client provide the dataSource description separately

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

<configuration>
	<oracle.manageddataaccess.client>
		<version number="*">
			<dataSources>
				<dataSource alias="oracle" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=sales-server)......)))"/>
				<dataSource alias="oralcetest" descriptor="(DESCRIPTION= ......)))"/>
			</dataSources>
		</version>
	</oracle.manageddataaccess.client>
</configuration>

Note: The oracle.manageddataaccess.client section needs to be declared in the configSections as follows:

1
2
3
4
5

<configuration>
	<configSections>
		<section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess" />
	</configSections>
</configuration>

Code / Migration

If you have previous experience with writing code for ODP.NET, mostly the only change you are going to do is remove the filthy old ODP.NET reference, add the new managed ODP.NET reference and change the namespace on the top of your code files from using Oracle.DataAccess.Client; using Oracle.DataAccess.Types; to using Oracle.ManagedDataAccess.Client; using Oracle.ManagedDataAccess.Types; and it should work just fine.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

// C#
using System;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

namespace Connect
{
  class Program
  {
    static void Main(string[] args)
    {
      try
      {
        // Please replace the connection string attribute settings
        string constr = "user id=scott;password=tiger;data source=oracle";

        OracleConnection con = new OracleConnection(constr);
        con.Open();
        Console.WriteLine("Connected to Oracle Database {0}", con.ServerVersion);
        con.Dispose();

        Console.WriteLine("Press RETURN to exit.");
        Console.ReadLine();
      }
      catch (Exception ex)
      {
        Console.WriteLine("Error : {0}", ex);
      }
    }
  }
}

Sample from http://www.oracle.com/technetwork/database/windows/downloads/odpmbetainstall-1696475.html

Limitations

There are limitations on the Managed ODP.NET which may or may not affect you, please refer to the [documentation on the same](http://www.oracle.com/technetwork/database/windows/downloads/odpmbetainstall-1696475.html#Known Issues).

Conclusion

Overall, the Managed ODP.NET seems to be a great step for Oracle in the way to support Microsoft .NET developer community. It is also great for server administrators as they do not have to face various issues with version compatibility as was with the native ODP.NET / Wrapper version.