RSS

Accessing Oracle database without installing Oracle Client

01 Aug

Background:
A legacy client-server database accessing .NET Windows Form application deployed by ClickOnce. In order to communicate with the database, Oracle Client 8i is installed on every client machine. Clearly installation jobs are disturbing. Another problem is distributed transaction not being supported until Oracle Client 9i. It should be better to implement a WCF service to centralize data access but the application is already too big to reshape.

Solution:
What I’m trying to achieve here is to enable distributed transaction with newer version Oracle Client without the need to install it on each client PC and still be able to dispatch the application by ClickOnce. It took me a long way to figure this out and make it work. Here are the steps, the files downloaded are for 32-bit application:

  1. Download Oracle Instant Client:Instant Client Downloads for Microsoft Windows (32-bit)
    Files we need: oci.dll, orannzsbb11.dll, oraociei11.dll(basic) or oraociicus11.dll(lite)

  2. Download and install Oracle Data Access Components (ODAC):32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio
    The installation is only for data provider and MTS related files. In the install directories, find the followings:

    • OraOps11w.dll
    • Oracle.DataAccess.dll
    • oramts.dll
    • oramts11.dll
  3. Open the Visual Studio project, add these dll files into the project and configure their properties to Copy if newer. These dlls need to reside in the application .exe directory at runtime.
     
  4. Add a reference to the Oracle.DataAccess.dll to use ODP.NET in the code.

Once finished setup we could use either COM+ or TransactionScope class for distributed transactions. And coding against Oracle database is no different.

Setting the connection string in app.config without tnsnames.ora:

<add name="MyConnectionString" 
     connectionString="Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHostIP)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyServiceName)));User ID=****;Password=****;"
     providerName="Oracle.DataAccess.Client" />

Initialize a connection:

OracleConnection Conn = new OracleConnection(
    ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString);

Reference:
Connecting to Oracle from C# / Winforms / Asp.net without tnsnames.ora
Instant Oracle Using C#
Packaging Oracle Data Access Components into .Net projects
What is the minimal setup required to deploy a .NET application with Oracle client 11?
Oracle Instant Client with ODP.NET

Files needed:
Instant Client Downloads for Microsoft Windows (32-bit)
32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio

Advertisements
 
10 Comments

Posted by on August 1, 2012 in .NET, Database, Oracle

 

Tags: , ,

10 responses to “Accessing Oracle database without installing Oracle Client

  1. Lai Kuo-Ying

    August 1, 2012 at 7:01 AM

    thanks , very helpful !

     
  2. granadaCoder

    August 2, 2012 at 1:29 PM

    Thanks! I was looking for a “no tnsnames.ora” or “tnsnames.ora less” solution! This worked right out of the box. I set up my original code to return IDataReaders, so I’m barely making any changes (going from Sql Server to Oracle). A ounce of prevention is worth a gallon of cure!

     
    • begeeben

      August 2, 2012 at 2:08 PM

      I’m so glad it helped!

       
  3. Sandeep KP

    August 25, 2013 at 10:36 AM

    Hi,

    My Excel is connected via ODBC connection, but when I launch the excel file on client machine, I need to have Oracle Client and ODBC configured on client’s machine in order to have data in the excel.
    Will this article help in achieving what I said above? Please advise if you’ve any other approach for my requirement.

    Thanks,

     
    • begeeben

      August 26, 2013 at 2:22 PM

      I guess not. The process here is to provide a ODAC connection specifically to the visual studio application. You might be able to get the needed connection installation from the official Oracle website.

       
  4. Brian

    April 5, 2014 at 12:41 AM

    Is it possible to update this based on the latest ODAC update? When I downloaded the ODAC files, I noticed that oraociicus11.dll is missing. This approach would be huge for what I’m working on. Thanks!

     
    • begeeben

      April 21, 2014 at 11:18 PM

      You could try instantclient-basiclite-nt-12.1.0.1.0.zip in the download link. There is oraociicus12.dll. Hope it works.

       
  5. xiaolin sun

    May 14, 2014 at 7:31 PM

    Hi Begeeben,
    I meet a question in my project. I reference the Oracle.DataAccess.dll without install ODAC, just adding those mandatory dlls in the main project.
    The Oracle.DataAccess.dll is referred in another project which the main project is depended. When I build the solution in VS2010, it could successfully and the main project works well. When I build this solution by MSBuild command, it is build successed, but the following exception is occurred when run the main project.

    My pc is Win7, the platform target of all the projects in the solution is “x86”, and the version of referred Oracle.DataAccess.dll is 2.112.1.0

    “Could not load file or assembly ‘Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. An attempt was made to load a program with an incorrect format.
    System.BadImageFormatException: Could not load file or assembly ‘Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342’ or one of its dependencies. An attempt was made to load a program with an incorrect format.
    File name: ‘Oracle.DataAccess, Version=2.112.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342’

    Would you please advise how to make the MSBuild works fine?
    Thank you very much!

     
    • begeeben

      May 15, 2014 at 11:09 PM

      It sounds like the referred dll file is missing in the build. Sorry that I have no experience in MSBuild command. Guess you could try to first move the dll to the relative build path manually, see if it works. Then Google how to include the dll with MSBuild.

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: