Guide: connect to an Oracle database with Oracle instant cient via unixODBC and Mono .net on Ubuntu

Today I found a fascinating problem, how to manage an access to a remote Oracle database via dotnet and with a program written in C#. There are mainly two ways to connect to a database: using a pre-built SqlClient module, or to use the good old ODBC one. Microsoft implemented the System.Data.OracleClient module for the ms.net framework, so did the Mono guys. However, this mono implementation is NOT managed code, so you can imagine. Oracle provided it's own dotnet classes for the developers, but it mainly focuses on the 10x version, and it has no release for mono. I have never managed Oracle before (just used in a huge application framework, but never developed on it), so first of all, I needed to figure out what tools do I need to create a connection. I found a cool RPM-based guide on the Oracle Technet. You need to say apt-get install alien first, to be able to install RPMs on Ubuntu. Then you can grab and install the two files, i usually download them with wget. I advise you to place the tnsnames.ora file directly under /etc, so it will always be on the path! To test it, type sqlplus scott/tiger@[the_server] I've installed the unixODBC package from the hoary universe, and to make it usable with Oracle, I've downloaded the proper Easysoft.com Oracle driver. It has a very nice install shell, and after it creates a text file with a machine number, you need to submit it via the website or email, and you'll receive a licence id (you need to place it under /usr/local/easysoft/licences BUT you need to delete the LICD: chars... it took me 5 minutes to figure out :) Now you need to set the variables:

ORACLE_HOME=/usr/lib/oracle/10.1.0.4/client
export ORACLE_HOME
LD_LIBRARY_PATH=/usr/lib/oracle/10.1.0.4/client/lib/
export LD_LIBRARY_PATH

(the best solution is to add these variables to the /etc/environment without the exports) if you get to this point, you can check the connection with the isql tool, located under /usr/local/easysoft/unixODBC/bin/: /usr/local/easysoft/unixODBC/bin/isql -v ORACLE (ORACLE is the DSN name in /etc/odbc.ini) It will reply with an sql shell, type: select * from scott.emp; to test it. I advise you to always grab the latest mono, at the time of writing this is 1.1.9, I've installed it by selecting the Fedora RPM repository, downloading all necessary files with wget and aliened them. I used the following odbc.cs program to test the connection (updated go-mono.com example)

using System;
using System.Data;
using System.Data.Odbc;
class OracleTest2 {
public static void Main() {
        string connectionString = "DSN=ORACLE;UID=scott;PWD=tiger";
        IDbConnection dbcon = new OdbcConnection(connectionString);
        dbcon.Open();
        IDbCommand dbcmd = dbcon.CreateCommand();
        string sql = "SELECT ename, job FROM scott.emp";
        dbcmd.CommandText = sql;
        IDataReader reader = dbcmd.ExecuteReader();
        while(reader.Read()) {
              string EmpName = (string) reader["ename"];
              string EmpJob = (string) reader["job"];
              Console.WriteLine("EmpName: " + EmpName + " Job: " + EmpJob);
         }
      reader.Close();
      reader = null;
      dbcmd.Dispose();
      dbcmd = null;
      dbcon.Close();
      dbcon = null;
      }
}

To compile and run, say:


mcs odbc.cs -r:System.Data.dll
mono odbc.exe

...and you can see the final results :)