ODP.NET and ClickOnce possible?

Question

We have a sqlserver (WinForms) application that is deployed with ClickOnce that talks directly to the database. If we are forced to port it to oracle, can ODP.NET be used with ClickOnce.

(The users may not have admin rights on their PCs)

Background

This data import application is used by a handful of users at each customer’s site it uses intergraded logon to connect to SQL Server. Most users access the system var an Asp.net application, or a WinForms (clickOnce) application that talks to a web service.

see also "How to write a .Net application that works with both SqlServer and Oracle "

Answer 1

EDIT: I have revised this answer for modernization. Also, to clarify, ClickOnce is merely an application deployment tool, how to use Oracle Client and ODP.Net with your application is still determined by architecture. Here's a summary of the most common scenarios:

Smart Client Apps (ex. Client App + Web Service)

Your ClickOnce-deployed client application is installed on users' machines, but speaks to some sort of service to work with data. In this scenario, your client application does not need to distribute an Oracle Client or ODP.Net.

The Oracle Client and ODP.Net package should be installed via Oracle's normal instructions on the machine hosting the back-end service, and referenced from that project/application as any other library would be used/distributed.

Fat Client "Thin Deployment" Apps (ex. "fat client" on Citrix)

Your ClickOnce-deployed client application is "fat" in that it includes its own data layer, and therefore must be able to connect to Oracle via a locally installed client, and able to reference the ODP.Net libraries.

In this scenario, the Oracle Client and ODP.Net packages should be installed on the hosting machine (ex. Citrix box) using Oracle's standard instructions, and your application should reference the appropriate libraries installed on its hosting computer.

Fat Client "Fat Deployment" Apps (ex. fat-client on users' machines)

In this scenario, your application is a typical "fat client" in that it has its own data layer and needs to be able to communicate with the Oracle Client and ODP.Net libraries.

The particular scenario we're discussing here is how to distribute Oracle Client and ODP.Net within your application (ex. when your users do not have these products installed on their machines). Below are instructions:

1. Download the Oracle Data Access Components package.

   (A) the current version is ODAC 11.2 Release 4 (11.2.0.3) and includes support for Microsoft Entity Framework 4.

   (B) You will need the 32-bit version of ODP.Net installed as Visual Studio is a 32-bit application. You can then compile to a target processor and bit version when you deploy.

2. In the ODAC package are the Oracle Instant Client and ODP.Net libraries. You need to copy the instant client libraries to your application and set them to "Copy Always"

3. Reference Oracle.DataAccess.Client like you would any other lib and set Copy Local = True.

4. Your connection string should look much like a normal entry in a TNSNAMES.ORA file:

    Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SID = oracle_world_here)));User Id=schema_here;Password=password_here;Persist Security Info=TRUE;
   

Notes

A. There are two main versions of the instant client - the full version (approx. 115mb) which includes multiple language support, and the Lite version (approx. 28mb) with support for only English.

B. IMPORTANT Ensure the libraries deployed are all from the same version of the client - meaning if you deploy 32-bit ODP.Net you have to deploy 32-bit instant client libs. If you use 11.2 instant client, you cannot use 11.0 ODP.Net, etc. -- silly but lots of people overlook this; use good source control.

C. My experience, the following libraries were placed in the root of my project (to avoid any issues with permissions or %PATH%)

oci.dll, ociw32.dll, orannzsbb11.dll, oraocci11.dll, oraociicus11.dll, oraops11w.dll

---

Summary & Results:

You can now use ClickOnce deployments for all Windows Client applications - fat, thing, or smart.