Connecting to Oracle from ASP.NET, ASP.NET MVC and .NET Applications (Configuring web.config)

UPDATE: THE STEPS BELOW WERE WRITTEN TO IGNORE ORACLE INSTANT CLIENT. I HAVE SUCCESSFULLY DEPLOYED WITH ORACLE INSTANT CLIENT AND NOW RECOMMEND IT TO ANYONE WHO NEEDS TO SHIP ORACLE DRIVERS AND INSTALL INSTRUCTIONS WITH THEIR PRODUCT. ORACLE INSTANT CLIENT IS ONLY TENS OF MB WHILE THE OTHER OPTIONS ARE HUNDREDS OF MB. THE ONLY REASON NOT TO USE ORACLE INSTANT CLIENT IS IF YOU MUST SUPPORT ORACLE VERSIONS EARLIER THAN ORACLE 10g.

Hi,

I would like to address a misconception I found while browsing forums. Namely, how to connect to an Oracle database from ASP.NET or ASP.NET MVC web applications and whether it was a simple as "changing connection string".

Normally Microsoft developers are heavily into Microsoft to the point of knowing nothing of anything else (nothing wrong with that, just specialization). So when they encounter Oracle they expect it to be as hassle free as SQL Server. That isn't the case.

There are several approaches to connect to Oracle from a .NET application :

1. Write your application to use Oracle. If you are writing a new application this is the best approach. Use ODP.NET Managed Driver and develop your .NET application to use Oracle specific code. The advantage of this is no installation of Oracle Client required when deploying to third parties. The disadvantage is you'll need to maintain two separate code bases for SQL Server and Oracle. This is not as big a problem as it seems if you use a proper design pattern and factory classes.

2. Use Oracle Client. There are many versions of Oracle Client, ranging from Oracle Instant Client to version specific clients.

• There is an Oracle product called "Oracle Instant Client". However, Oracle Instant Client lacks tnsnames.ora or listener.ora or any other such manual configuration. Oracle Instant Client is only available on Oracle 10g or higher (and a specific version of that as well.) Therefore, I do not recommend Oracle Instant Client for serious deployments.
• Each version of Oracle database has an associated Oracle Client. As well, different major and minor versions of the database may require different versions of the client. This is my recommended approach, as it also installs many tools useful for accessing Oracle database like SQL* Plus.
• Each version of the client also has associated extensions and drivers. For example, Oracle ODBC Driver. After you install Oracle Client for your specific version of Oracle, you may need to install the specific driver for your client. For example, Oracle ODBC for Oracle 11g.

After you install Oracle Client you will need Oracle Data Access Components. ODAC is an umbrella term for various Oracle drivers, among them ODBC.

Navigating the Oracle website is not like using Google and finding the download link. For starters, all the download links are gated for username and login so you won't find them with a simple Google search. Next, you may be looking for Oracle drivers or information out of date. As of January 2014, only Oracle 11g and Oracle 12c are available on the website. Luckily, Oracle 11g supports down to verson 9.2.0.8 when it comes to their client. If you need older versions your employer will have to have a long term contract with Oracle to provide specialized support.

Luckily, the Oracle website is well designed. Simply go to the database download section click on the version you want (as of this writing 11g and 12c are available) then click on the "See All" section beside the architecture you need (either x86 or x64). Legacy applications often need x86 so download x86 first if in doubt. Buried deep under the list of Oracle downloads for that version will be "Oracle Client."

Oracle client needs to be installed to connect to Oracle. Download and custom install Oracle client, installing everything you need.

After Oracle client has been installed, download and install Oracle Data Access Components. This will install drivers, most notably ODBC that you will need to connect to legacy applications like ADO. Certain versions of ODBC driver (for example for Oracle 9.2) require that you run command line programs in elevated mode.

After you install ODAC, you will need to do the standard work to configure the System DSN name to point to Oracle Client. This involves modifying the tnsnames.ora and listener.ora . If you installed Oracle Client properly, tnsnames.ora will be configured correctly and properly for you at the start. You will need the usual information (service name, host name, username, password) to connect to the given Oracle database. If the Oracle database is not correctly configured then you will not be able to connect. Configuring the Oracle database itself is out of scope of this blog post and you should contact your DBA or other sources if that's what you need. Check that listener.ora is correctly configured to allow NTS and EZCONNECT.

The final step for legacy connections like ODBC is to configure Windows correctly. This involves setting up ODBC with odbcad32.exe. Remember that ODBC is located in two places in Windows, in system32 and in SysWOW64 folders. Use the SysWOW64 folder to get the 32-bit ODBC (yes, that's correct).

Finally at long last you have the connection string. Make a small ASP.NET application to test the connection. Oracle has its own specific format for connection string. Always use up-to-date vendor documentation when it comes to issues like this. If you used the TEST option when installing Oracle client and when configuring the ODBC then this will be relatively painless. ODBC must be working and the client must be working before this step.

As a final note, you will need Oracle SQL Developer to act as a SQL Server Management Studio replacement. It doesn't have the full functionality of SSMS (for that you would need third-party tools like TOAD) but it has more than enough for a developer. You should be doing things like backups and imports and exports with the command line anyway.

I hope the above post will be helpful to those babysitting legacy applications for their breadwinning or developing new applications that need to connect to Oracle.