Oracle Instant Client with ODP.NET

Connecting to an Oracle database without installing an Oracle client software package on the server/client deployment environment:

Some application deployments to clients or servers require database connections to Oracle instances. What happens when the deployment environment doesn’t have the Oracle client software loaded? What if the server configurations are not accessible, or it takes an act of congress to install anything to the environment?

In this example I am adding  ‘instant client’ support to a .net application using the ODP.NET driver but other drivers can leverage the instant client. Also, I am using the latest ODP.NET driver for the .NET 4 framework, as it supports the Entity framework =)

Required software –

• Oracle Instant Client
• Oracle ODP.NET (or your preferred Oracles driver that is supported by Instant Client)

http://www.oracle.com/technetwork/indexes/downloads/index.html

In your .net application, add the following 5 DLLs (from the Oracle download) to the root directory of your project and configure the properties of each to ‘Copy if newer’.

• ODP.NET Drivers
  • OraOps11w.dll
  • Oracle.DataAccess.dll (if this is in your ref folder, set the property to ‘Copy Locally’
• Instant Client Files (Depending on the version you downloaded, you only need one set)
  • Basic
    • oci.dll
    • orannzsbb11.dll
    • oraociei11.dll (111M)
  • Light
    • oci.dll
    • orannzsbb11.dll
    • oraociicus11.dll (~31M)

 If you are using Entities or need access to the factory in ODP.NET, you’ll need to register the provider in the web.config (*check the version of your driver to be sure it matches) –

<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=4.112.2.30, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>

 When you build/deploy your application, the 5 DLLs should be in the web app bin folder, or if you are building a client app – they should be in the same folder as the exe.

If you want to use the TNS-Less connection string format for entities, the Entity connection string may look something like this in the web config –
<add name=”PSDEntities” connectionString=”metadata=res://*/Data.PSD.csdl|res://*/Data.PSD.ssdl|res://*/Data.PSD.msl;provider=Oracle.DataAccess.Client;provider connection string=&quot;DATA SOURCE=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=DBService)));PASSWORD=****;USER ID=****&quot;”
      providerName=”System.Data.EntityClient” />

*notice the &quot; before and after the embedded connection string.

 

References –

Good tutorial with screen shots (http://alderprogs.blogspot.com/2009/04/deploying-odpnet-with-oracle-instant.html)

Oracle’s Developer Guide (http://download.oracle.com/docs/html/E10927_01/InstallODP.htm)