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.
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:
- 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)
- 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:
- 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.
- 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);
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