Instant Oracle Using C#

Introduction

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:

1. You have at least a basic understanding of C# and have written code in it or some other language such as C++ or Java.
2. You have a basic understanding of writing SQL commands.
3. Have an Oracle database to connect to.
4. If your database is at your place of work, a copy of tsanames.ora provided by the DB admin or whomever. (And hopefully, permission to access the database!)

So without any further introduction, let me get into a little background.

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.

They are as follows:

• OCI Instant Client Data Shared Library
• oraociicus10.dll (Basic-Lite version)
• oraociei10.dll (Basic version)
• Client Code Library
• oci.dll
• Security Library
• orannzsbb10.dll
• OCCI Library
• oraocci10.dll

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:

myserver.server.com = (DESCRIPTION = (ADDRESS = (PROTOCOL= TCP)
    (Host= myserver.server.com)(Port= yourPort#))(CONNECT_DATA = (SID = yourSID)) )

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:

private static string CONNECTION_STRING = 
  "User Id=myUserID;Password=myPassword;Data Source=(DESCRIPTION=" + 
  "(ADDRESS=(PROTOCOL=TCP)(HOST=myserver.server.com)(PORT=yourPort#))" + 
  "(CONNECT_DATA=(SID=yourSID)));";

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

Once you have the above, the rest is easy.

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:

using System.Data.OracleClient;

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.

// This really didn't need to be in its own method, but it makes it easier 
// to make changes if you want to try different things such as
// promting the user for credentials, etc.
static private string GetConnectionString() 
{ 
   // To avoid storing the connection string in your code, 
   // you can retrieve it from a configuration file. 
   return "Data Source=myserver.server.com;Persist Security Info=True;" + 
          "User ID=myUserID;Password=myPassword;Unicode=True"; 
}

// This will open the connection and query the database
static private void ConnectAndQuery() 
{ 
  string connectionString = GetConnectionString(); 
  using (OracleConnection connection = new OracleConnection()) 
  { 
    connection.ConnectionString = connectionString; 
    connection.Open(); 
    Console.WriteLine("State: {0}", connection.State); 
    Console.WriteLine("ConnectionString: {0}", 
                      connection.ConnectionString); 
    
    OracleCommand command = connection.CreateCommand(); 
    string sql = "SELECT * FROM MYTABLE"; 
    command.CommandText = sql; 
 
    OracleDataReader reader = command.ExecuteReader(); 
    while (reader.Read()) 
    { 
      string myField = (string)reader["MYFIELD"]; 
      Console.WriteLine(myField); 
    }
  }
}

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

All that remains is a call to ConnectAndQuery() from Main.

Errors you may run into at runtime

Error: Unhandled Exception: System.Data.OracleClient.OracleException: 
       ORA-12154: TNS:could not resolve the connect identifier specified

Cause:

• Your connection string is invalid.
• Missing the tsanames.ora file.

Resolution:

• Fix the connection string making sure the server name and/or credentials are correct.
• Make sure the tsanames.ora file is present in the application path and contains valid data.

Error: Unhandled Exception: System.Exception: 
       OCIEnvNlsCreate failed with return code - 1 but error message 
       text was not available.

Cause:

• One or both of the required Oracle Instant Client DLLs are missing from the application's path.
• There is no 'PATH=' environmental variable set that points to these files should they not reside in the application's path.

Resolution:

• Copy the DLLs into the application's path or modify your PATH= to include the directory where these files reside.

Error: Unhandled Exception: System.Data.OracleClient.OracleException: 
       ORA-12705: Cannot access NLS data files or invalid environment specified

Cause:

• You have Oracle or Oracle development tools installed locally (or on the machine running the application).

Resolution:

• Check to see if [HKLM/Software/Oracle] exists. Chances are it does.
• Within the Oracle key, look to see if NLS_LANG exists.
• If it does, do one of the following: rename to NLS_LANG.OLD, or delete it entirely. Providing a valid language such as AMERICAN_AMERICA.WE8MSWIN1252 would also resolve the issue. Single-byte character sets include US7ASCII, WE8DEC, WE8MSWIN1252, and WE8ISO8859P1. Unicode character sets include UTF8, AL16UTF16, and AL32UTF8.

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.