Click here to Skip to main content
15,867,488 members
Articles / Programming Languages / C#

Instant Oracle Using C#

Rate me:
Please Sign up or sign in to vote.
4.64/5 (42 votes)
7 May 2007CPOL8 min read 580.3K   118   53
Connecting to Oracle using C# without using Oracle Developer Tools for Visual Studio .NET. Managing Oracle connectivity with a minimal footprint using Oracle Instant Client.

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:

C#
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 DefinitionDescriptionExample
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:

C#
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.

C#
// 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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Avectra
United States United States

Comments and Discussions

 
QuestionI have some trouble with .NetFramework 4 Pin
Member 112775391-Dec-14 4:20
Member 112775391-Dec-14 4:20 
GeneralMy vote of 5 Pin
ahmadamiri31-Dec-12 6:27
ahmadamiri31-Dec-12 6:27 
GeneralMy vote of 5 Pin
Yafie24-Apr-12 20:45
Yafie24-Apr-12 20:45 
QuestionBig Thanks man!!! Pin
Yafie24-Apr-12 20:43
Yafie24-Apr-12 20:43 
Generalthanks man! Pin
korvarn korvarn9-Apr-12 23:28
korvarn korvarn9-Apr-12 23:28 
GeneralMy vote of 5 Pin
fschudel8-Nov-11 21:20
fschudel8-Nov-11 21:20 
GeneralThank you Pin
xxandrejxx15-Mar-11 3:23
xxandrejxx15-Mar-11 3:23 
GeneralRe: Thank you Pin
Paul Groetzner16-Mar-11 15:55
Paul Groetzner16-Mar-11 15:55 
Generalarticle Pin
abd128-Jan-11 8:51
abd128-Jan-11 8:51 
Generalocienvnlscreate failed with return code 1 Error Pin
Member 16348374-Jan-11 17:51
Member 16348374-Jan-11 17:51 
I copied oci.dll,orannzsbb11.dll,oraocci11.dll,oraociicus11.dll these 4 files in bin folder but still I get the "ocienvnlscreate failed with return code 1 " this error. Once I removed these dlls system can connect via installed oracle 11 client. Any idea as what causing error when using DLL..I tried google as i can but not much success..any help is appreciated...

Nambi
GeneralFlexible datatype Pin
Ajay Kale New11-Nov-10 0:32
Ajay Kale New11-Nov-10 0:32 
QuestionTNS Timeout error only through installed version of application, none through Visual Studio runs Pin
Sreedevi Jagannath20-Jul-10 23:51
Sreedevi Jagannath20-Jul-10 23:51 
Generalthanks Pin
Member 472511727-Oct-09 0:20
Member 472511727-Oct-09 0:20 
QuestionConnecting Oracle database remotely/without installing oracle client on client machine? Pin
Member 172672022-Jun-09 21:10
Member 172672022-Jun-09 21:10 
GeneralThanks Pin
Pablo Grisafi9-Dec-08 6:03
Pablo Grisafi9-Dec-08 6:03 
GeneralReceive client error Pin
subaqua8-Aug-08 5:06
subaqua8-Aug-08 5:06 
QuestionRe: Receive client error Pin
Member 266203811-Aug-08 6:15
Member 266203811-Aug-08 6:15 
AnswerRe: Receive client error Pin
Member 266203811-Aug-08 9:36
Member 266203811-Aug-08 9:36 
GeneralRe: Receive client error Pin
subaqua11-Aug-08 11:10
subaqua11-Aug-08 11:10 
QuestionRe: Receive client error Pin
Member 266203813-Aug-08 7:52
Member 266203813-Aug-08 7:52 
AnswerRe: Receive client error Pin
subaqua14-Aug-08 11:05
subaqua14-Aug-08 11:05 
GeneralRe: Receive client error Pin
Member 18648279-Mar-09 12:10
Member 18648279-Mar-09 12:10 
GeneralRe: Receive client error Pin
Member 18648279-Mar-09 12:34
Member 18648279-Mar-09 12:34 
GeneralAnother similar solution Pin
S.HASAN ALMOSAWI29-Jul-08 20:02
S.HASAN ALMOSAWI29-Jul-08 20:02 
QuestionTesting with Oracle installed Pin
patrickst129-Jul-08 4:31
patrickst129-Jul-08 4:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.