C# – How to use Oracle from .NET

cnetoracle

Unfortunately, I'm trying to use Oracle from .NET and it's like going to back to 1997. Explaining things properly is a sign of weakness and the registry and environment variables seem to feature.

So here's how far I've got.

I filled out a huge form about my life at Oracle.com and downloaded the

ODBC Windows 64-bit Instance Client, 11.2.0.3.0 and unzipped it into a folder.
Basic Windows 64-bit Instant Client, 11.2.0.3.0 and unzippped it into the same folder as above.

I ran the install .exe (no MSI, remember this is 1997).

I added a TNS_NAMES environment variable pointing at the corporate TNSNames.ora file on a network share and setup a quick DSN in Control Panel and ran the test – it worked!

I then downloaded the XCOPY version of the latest ODP.NET for .NET 4.0 and copied it into my Dependencies folder under my source control workspace.

I added a reference from my project to Dependencies\odp.net4\odp.net\bin\4\Oracle.DataAccess.dll

I've read that I need to let the managed ODP.NET stuff know where it can find its unmanaged Oracle libraries, which I'm guessing is Dependencies\odp.net4\bin\OraOps11w.dll but it could be some other Oracle binaries somewhere?

Now I'm setting a config file parameter called DllPath. Sadly, the Oracle docs seem not to realise that .NET configuration files need their sections hooking up in the configSections section.

Here is the config file section.

<oracle.dataaccess.client>
  <add key="DllPath" value="D:\Trunk\Dependencies\odp.net4\bin" />
</oracle.dataaccess.client>

What goes in the configSection? The few threads I've found on this give an example and then say "I don't know what type= should be", which is the main bit!

Can anyone advise me? Does anyone know a link to a Dummies Guide to Querying an Oracle View from .NET?

Is there something I can do in 1997 to stop myself having to use Oracle in 2012?

UPDATE

Just a quick update. I've copied OraOps11w.dll into my output bin folder and removed any web.config file changes I'd previously added.

I now get the following error, which I'm researching.

Could not load file or assembly 'Oracle.DataAccess' or one of its
dependencies. An attempt was made to load a program with an incorrect
format.

Some other forums talk about registering Oracle.DataAccess.dll in the GAC, which I'm keen to avoid – and I can't see what the difference would be anyway, but I'll give it a go.

UPDATE 2

Registering the Oracle.DataAccess.dll library in the GAC didn't make any difference and I've unregistered it.

The end of the working week is upon me and I have to catch a train, but I've emailed another dev team here and hope they've fought this battle before (and not used nightly extracts like everyone else).

UPDATE 3

This morning I downloaded and setup IIS Express 8.0 RC which has a 64-bit version. The 64-bit version is not supported from VS2010 so it needs to be run from the command line. This was fairly simple, by looking in Task Manager, I was able to see what command line VS uses for iisexpress.exe *32.

Unfortunately I get this error when browsing to my site hosted by the 64-bit IIS Express.

Handler "ExtensionlessUrl-Integrated-4.0" has a bad module
"ManagedPipelineHandler" in its module list

This is a problem too far down this line of enquiry so I'm going to try using the 32-bit version of ODP.NET.

UPDATE 4

I downloaded the full install of the 32-bit version but I got scared when I saw the Oracle Universal Installer. It looks too 90s to trust with my precious dev box. So I'm using the 32-bit 'XCopy' client, this lets me to bring up my site's homepage, so references are working. Thanks for those that suggested an architecture mismatch 32/64 problem.

Now I am looking into this error when instantiating a new OracleConnection.

The provider is not compatible with the version of Oracle client

UPDATE 5 I think I've done it.

So I downloaded the 32-bit latest 11g "Instant Client" and just put it in my \Dependencies folder for the solution trunk codebase. I added this folder to the %PATH% system environment variable and also chucked the OraOps11w.dll file in there.

Finally, _oracleConnection = new OracleConnection(connectionString) doesn't throw! Now I need to make sure I've a bottle of Laphroaig on the desk when I come to deploy to prod.

So, to recap, for IIS Express 7.5 (which is a 32-bit process) on Windows 7, 64:

  • Download and extract the 32-bit 11g "Instant Client" to some folder and add this location to your PATH. Oci.dll is the main Oracle client-side unmanaged library.

  • Download and extract the 32-bit ODP.NET to some folder.

  • Copy the OraOps11w.dll to the main folder in which the Instant Client resides (above).

  • Add a TNS_ADMIN environment variable pointing to a folder in which TNSNames.ora text file resides. This is like a hosts file and maps Oracle service names to server host names.

  • Close and re-open Visual Studio to ensure you pick-up the new environment variables, and add a reference to the Oracle.DataAccess.dll managed assembly in your ODP.NET folder.

That should be it. Sounds fairly simple when you know how.

UPDATE 5.1

Investigating the error below, which I originally didn't report since I was sure it was a firewall issue. The company I'm at has two on each workstation, but I just opened a raw socket to the Oracle server so it can't be.

ORA-12541: TNS:no listener

Best Answer

The simplest way to explain the issue is to point out the difference between the Oracle Client and the ODAC client libraries.

On a 64-bit machine (Windows 7+), you need to have installed the 64-bit Oracle Client. This is the client your machine will use to connect to Oracle databases that are deployed locally on your machine. For apps hosted on a web server or other terminal server, the rule is the same.

The trick is this... as a developer, our machines must also have the .Net IDE tooling. Oracle has a crappy naming convention but essentially there are 2 pieces: ODT (tooling) and ODAC (data access). The ODP.Net data provider is a part of the ODAC libraries.

So... back to the IDE... Visual Studio is 32-bit and therefore we must install the tooling above in 32-bit.

While developing, debugging, etc. VS.Net is using the 32-bit client libraries and data access libraries to work with Oracle.

As soon as you deploy this application to a machine, it uses whatever Client is loaded on the machine unless a specific platform is targeted.

This means if you target 32 and deploy to 64, it will break... and vice versa. The best thing to do is leave it on the any platform section, and simply remember what the hell you are doing :)

The other thing to be careful with is to ensure both your Client and ODAC packages are of the same version... you do not want to have 11g R2 client, and 11g R5 ODAC, because as soon as you deploy, crap breaks again.

The caveat here is if you wish to "embed" the oracle client inside your application, in which case OraOps alongside several other libraries are deployed with the application - this is called Oracle's Instant Client, and is also a part of the ODAC package and included in their full blown Client package as well.

The good news...

Oracle is soon (2013 Q1) to release their next ODP.Net package... which will be a fully managed-code library... meaning no more separate client or ODAC package to match up, and the platform will be ignorant of 32 and 64 bit distinctions... it will function much like the old microsoft library will, only it will be built and maintained by oracle with a more robust feature set. I only wish it would arrive sooner.

Related Topic