Monday, March 17, 2008

Using Oracle InstantClient with ADO.NET

We've been using the DataDirect ADO.Net Connectors for awhile to connect to Oracle but wanted to look into replacing them with th shipped Oracle client. It had been years since I installed the Oracle client so I was very pleased to find out about Oracle InstantClient. It removes the need to run through the old horrible Oracle Universal Installer and configure tnsnames.ora, etc. Just download, unzip the dlls into your bin directory and connect to the database. Sounds simple it was in the end but I ran into some snags along the way.

1. I first tried this in an website and got this error when trying to connect "System.Data.OracleClient requires Oracle client software version 8.1.7 or greater". Tried messing with permissions and other things and nothing seemed to work. I then opened a command-line program I used for testing, copied in the dlls, set the config string, ran the program and everything worked. Ok so how to make the website work. Finally tried adding the location of the InstantClient to the PATH environment variable restarting the web application and it connected.

2. I am running 64bit Windows Vista. When I tried to use the 64bit Instant Client I got this error message: "Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed." Found some posts saying that only the 32bit client is supported. Sure enough only the 32bit client worked for me.

3. I was able to connect to the database without a tnsnames.ora simply by setting the connection string in the format: "User Id={0};Password={1};Data Source=//{2}:{3}/{4};"; where 2 is replaced with the host, 3 is replaced with the port and 4 is replaced with the service name.

Overall a huge improvement over the old Oracle client experience.

Helpful Links:
- CodeProject article on using the InstantClient
- Instant Client FAQ

No comments: