Skip to main content

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

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.



Labels:

Popular posts from this blog

ChatGPT is a new, and faster, way to do programming!

Currently ChatGPT is in a free “initial research preview” . One of its well known use cases at this point is generating software code. I’ve also just used it to write most of this article… Well, actually a future article about cleaning up SRT subtitle files of their metadata faster than I have been by hand with Notepad++ and its replace functionality. Update: I recorded a screencast of writing the SRT subtitle cleaner application loading and processing portion. I relied heavily on ChatGPT for code. It was a fun process! https://youtu.be/TkEW39OloUA ChatGPT, developed by OpenAI, is a powerful language model that can assist developers in a variety of tasks, including natural language processing and text generation. One such task that ChatGPT can help with is creating an SRT cleaner program. SRT, or SubRip Subtitle, files are commonly used to add subtitles to video files. However, these files can become cluttered with unnecessary information, such as timing lines or blank spaces. To clean...
Read more

Theme error in 2010s Android App after AppCompat Migration

I plan on releasing a lot of my old work as GPL open source, but most of it has aged to the point that it no longer functions, or if it does work it’s running in compatibility mode. Basically it’s no longer best practices. Not a good way to start off any new public GPL projects, in my opinion. The current project I’m working on is an Android app that calculates star trails meant to help photographers get or avoid that in their night time photos. For now I’m going to skip some of the import process because I didn’t document it exactly. It’s been mostly trial and error as I poke around Android Studio post import. The Android Studio import process… Removing Admob Google Play code before the project would run at all. After removing dependencies, it kind of worked, but when running it in the emulator it shows a pop-up message saying that the app was developed for an old version of Android. Going through the process of updating code to match current best practices… I had the IDE convert the ...
Read more

Printing to file in Linux WINE

I noticed that this post has been sitting as a draft since 2011. At this point I have no idea if it’s useful or what I was even doing, but I might as well make it public in case someone can find it helpful! So I’ve been trying to get one of those PDF print drivers working in WINE without success. I then came upon a process that might work. When printing you need to select the checkbox “Print to file” that creates a .prn file. Just Linux things... I was using a program that only has printing facilities, but I want to export around 100 pages of text and images. Once you have the .prn (postscript) file, you can do any number of things to it. In my case I want the postscript file to be converted to HTML. I am also considering PDF format because that has more conversion options to eventually get me to HTML or plain text. sudo apt-get install cups-pdf Or it looks like that package might have changed to this… sudo apt-get install printer-driver-cups-pdf Where PDFs would be generated in /home/...
Read more