CodeProject: Instant Oracle Using C#. Free source code and programming help

This is actually my first CodeProject article and my first attempt at writing C# code, so if I have made any mistakes along the way, please feel free to comment. I won't get offended ;)

The idea behind this article was prompted because I found only one article that deals with C# and Oracle on this site (which is unrelated to my needs), and I haven't been able to find any articles anywhere else on the internet regarding this specific topic and platform.

In order to properly use the information contained in this article, I am going to assume the following:

Background

There is an Oracle database at the company I work for that contains customer case information which I wanted to access in order to query information from. I had, in the past, created an MFC application and used Oracle Objects for OLE to connect to the database in order to run my queries. While this worked, it required an insane amount of files to be installed along with my application, as well as some Registry entries. I really hated having to distribute all the extra files and complications along with it, but had no choice at the time. To put it simply, it required about 590 files totaling in the area of 40MB. Not exactly what I had in mind, but the documentation I had on how to use it wasn't very clear. And, I don't think there's an article to date on CodeProject on how to properly use it and what the client requires to have installed on his/her machine. Perhaps, someone will take up the challenge.

In any case, now that I am gravitating towards using C#, I wanted to reattempt a few things I have done with Oracle, but leaving as little a footprint as possible on the client's computer. Just a few months prior to this article being written, I came across Oracle Instant Client (http://www.oracle.com/technology/tech/oci/instantclient/index.html). This seemed like just what I was looking for. I spent the next few days trying to figure out how to use it with MFC. I can't recall the exact amount of time, but I can say this, it was far easier to implement with C# than C++, at least in my opinion.

Oracle Instant Client uses OCI (Oracle call-level interface) for accessing Oracle databases.

What is the Oracle Call Interface?

The Oracle Call Interface (OCI) is an application programming interface (API) that allows applications written in C to interact with one or more Oracle servers. OCI gives your programs the capability to perform the full range of database operations that are possible with the Oracle9i database, including SQL statement processing and object manipulation.

What you need?

You will need to create a free account on Oracle's site (below) and agree to their terms to be able to download the client.

Download Oracle Instant Client for Microsoft Windows (32-bit) here. There are other platforms available, and a 64-bit version for Windows, but I haven't looked at the contents of any of those and they are outside the scope of this document anyhow.

There are two versions you can choose from. They are: Basic and Basic-Lite. I recommend getting the Basic Lite version, unless you need to support more than the English language.

OCCI requires only four dynamic link libraries to be loaded by the dynamic loader of the Operating System. When this article was written, it is using the 10.2 version.

The main difference between the two Instant Client packages is the size of the OCI Instant Client Data Shared Library files. The Lite version is roughly 17MB, whereas the Basic version is almost 90MB since it contains more than just the English version.

Once you have these files, simply copy them into the same directory as your executable. You could possibly put them in another folder as long as your environmental variables are set to point to its path, but I found it easiest to do it this way. After all, it is only four files.

The only other required file you will need to have is tsanames.ora which is simply a text file that looks similar to this:

This will be different for everyone, but I am posting the sample so you know what to expect in this file if you are new to this subject. Also, you can expect to find multiple entries in this file so don't be surprised if there is more than one set.

An alternative to including the tsanames.ora file is to include it within your connection string, as the following snippet demonstrates:

Connection string parameters

Parameter Definition	Description	Example
Server or Data Source	TNS name or network address of the Oracle instance to connect	Server=TESTDB
User ID	Name of the Oracle database user	User ID=myUserID
Password	Password for the Oracle database user	Password=myPassword
Integrated Security	To connect using external authentication or not. Valid values for Integrated Security are: YES or TRUE to use external authentication, NO or FALSE to not use external authentication. The default is false.	Integrated Security=true
Min Pool Size	Specifies the minimum number of connections in the connection pool. Default is 0.	Min Pool Size=0
Max Pool Size	Specifies the maximum number of connections in the connection pool. Default is 100.	Max Pool Size=100
Pooling	Specifies whether or not to use connection pooling. Valid values are TRUE or YES to use connection pooling, or FALSE or NOT to not use connection pooling. Default is TRUE.	Pooling=true

If you use Integrated Security, make sure you have a user created externally. Also, make sure you know what you are doing if you use external authentication - there are security implications. Read the Oracle Database Advanced Security Administrator's Guide for more info about external authentication.

Code example - Connecting to Oracle and running a simple query

Create a new C# application. For this example, let's keep it simple and create it as a console application.

Be sure to include a reference to System.Data.OracleClient.dll, and place the following at the top of your code along with all the other using statements:

This is a standard library provided by Microsoft. No voodoo witchcraft or additional Oracle library references are required. More information about this library can be found here.

The following section of code should be all you need to get yourself started. This is simply an exercise in connecting to the database and running a simple SELECT query to return some data. The purpose of this article is to establish a connection to Oracle, installing as little as possible on a user's machine. You won't be seeing anything more complicated than that. We can save the rest for another article.

We will start by creating two methods: static private string GetConnectionString() and static private void ConnectAndQuery(). I won't be going into any specific details regarding any of the code provided. There's plenty of documentation available to explain what can be done with System.Data.OracleClient, if you want more information.

I will assume you can make the necessary modifications to the connection string and your query. The code should otherwise be self-explanatory.

Errors you may run into at runtime

Cause:

Resolution:

Cause:

Resolution:

Cause:

Resolution:

Performance issues

I used both Oracle Developer Tools for Visual Studio .NET and Oracle Instant Client to experiment with. I did not noticeably see any performance differences during these tests although there may be some depending on what you are trying to do. For my purposes, there would be little gained by using the developer tools since it requires a more complex install and no noticeable performance gain. If anyone has any experiences they can share, please do.

Conclusion

I hope this will help someone who needs to establish a connection to Oracle with their application and wishes to distribute it without any complicated client installs and with only a small footprint on the client's machine. Please feel free to leave comments and/or questions. If you have anything negative to say, please leave an explanation why. Otherwise, no one will learn from it.