C# .NET Programming Tip: Connecting to an Oracle Database

on

Take not that Microsoft will discontinue support for System.Data.OracleClient in .NET 4.0. This method should still work, but it will be depreciated…

Please view this post for a better way to connect to Oracle.

Ugh, I spent a good 6 hours figuring out how to do this! Hopefully this post can save someone some time.

Connecting to an Oracle database requires driver files from Oracle. This is true no matter what you use in Visual Studio, it true for System.Data.ODBC or whatever else. This little factoid took me a while to figure out, as I didn’t want to believe that I had to install a 200mb piece of software from Oracle just to connect to one of their databases.

Well thankfully I didn’t have to install their standard client. They offer an “instant client” that is quite a bit smaller. They even offer a lite version of the instant client, which is the one I decided to use. The bad thing about it is even that is around 20mb in size.

You can download it here:
http://www.oracle.com/technology/software/tech/oci/instantclient/index.html

Even downloading that I had issues. I tried downloading the 11.1.0.6.0 version of the Instant Client Lite, but I kept getting errors on their website, so I just settled for 10.2.0.3.

*Instant Client Package - Basic Lite: Smaller version of the Basic, with only English error messages and Unicode, ASCII, and Western European character set support (10.2 only)instantclient-basiclite-win32-10.2.0.3-20061115.zip (10,360,970 bytes)

You only need the one zip file. The other ones like SDK and ODBC are not necessary unless you want to specifically use ODBC. This file above allows you to connect with using System.Data.OracleClient. In Visual Studio go to the menu item “Project->Add Reference…” and find System.Data.OracleClient in there and check it. All of the computers I have tried have the reference available (.NET 2.0 version).

If for whatever reason it is not in there, Microsoft has their old .NET 1.1 version for download on their site HERE. Good luck finding the 2.0 version though.

The instant client zip file you downloaded has a few files in it.

We need these four:

  • oci.dll
  • orannzsbb10.dll
  • oraocci10.dll
  • oraociicus10.dll

Place those into the directory of your project EXE. Most likely that will be in the “Project_folder/bin/debug”. Of course once you publish your EXE, you will have to place the DLLs in their proper place.

There is one other file you must have. It’s called “tnsnames.ora” and is another thing that wasted a lot of my time figuring out. It basically tells the dlls where and how to connect to the database.

Here is an example of a tnsnames.ora file:

AAAA.WORLD =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = XXXXXXXXXX)(PORT = 1521))(CONNECT_DATA = (SID = AAAA)))

AAAA.WORLD is the Oracle database you are trying to connect to. The SID parameter is for older versions of Oracle. Just put the first part of your database name in there. The database at my job is using version 8 of Oracle, so the newer parameter called “SERVICE_NAME” would not work. The HOST parameter is where you put your IP address, or server name (say you have an oracle server named DBNINJA, just put that in there). The PORT 1521 is the default for Oracle, so if you have troubles, make sure your database administrator didn’t change the port.

What I’m hoping to figure out eventually is how to input this information in the connection object in Visual Studio, without having this file (ed: done, see newer post). This thing doesn’t make sense, because programs should be able to connect to more then one database instance. Anyways, I’ll have to do more research on that and post my finding here in the future. This whole tnsnames.ora file seems like a bad idea.

Now here is how to connect to the database in Visual Studio 2005:

using System.Data.OracleClient;

...

StringBuilder tableList = new StringBuilder();

try{ 
    using (OracleConnection testConnection 
        = new OracleConnection("Data Source=AAAA.WORLD;Persist Security Info=true;User Id=******;Password=******;")) 
    { 
        testConnection.Open(); 

        using (OracleCommand testCommand = testConnection.CreateCommand()) 
        { 
            testCommand.CommandText = "SELECT TABLE_NAME FROM ALL_CATALOG"; 
            testCommand.Prepare(); 
            
            using (OracleDataReader testReader = testCommand.ExecuteReader()) 
            { 
                while (testReader.Read()) 
                { 
                    tableList.Append("Table: " + testReader.GetString(0) + "\r\n"); 
                } 
            } 
        }
 
        testConnection.Close(); 
    }
}
catch (Exception error)
{
    System.Windows.Forms.MessageBox.Show(error.ToString());
}

txtDisplay.Text = tableList.ToString();


Notice that the data source points to the same place as the tnsnames.ora file. Also, the connection string here is where you place your username and password. After creating the connection object, you open the connection and perform some sql with a command object.

Also take note that I am using … “using” statements. These automatically close and unset the objects after they have been used. It saves a lot of hassle!

The SQL statement “SELECT TABLE_NAME FROM ALL_CATALOG” fetches a list of every table that is available to the user and returns the name of each one.

The StringBuilder tableList I am using is just a faster way of concatenating strings.

That’s it for now. I’m sure I will be doing a lot more with Oracle in the future, so I will probably have a lot more to write about here as well.