Connecting to an Oracle Database (and doing an update on a table)

Answered my first stackoverflow question the other day (and earned some rep doing so).

The question was on how to connect to an Oracle database and update a table and since I use Oracle next to never I had to go Google for myself.
Many resources later I found an answer to the question and it is as follows:

To connect to an Oracle database you’ll need an Oracle driver if you haven’t got one.
The only location I’ve found for Oracle driver downloads is this one:
http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

Choose the 32 bit driver for the time being. 64 bit is probably awesome when you’re running things in production but design time tools aren’t available in the 64 bit download.
Click the topmost link in the list that appears. At the time of writing it looked like this:

That should bring you to a page looking somewhat like this:
And now you’ll need to register to be able to download the driver. Having downloaded and installed the driver (you’ll see there is a different link in the above screenshot to a ODAC Installation Instructions, use that if needed) you’re finally ready for some coding.

First thing to do is to actually add reference to the new ODAC driver. You do that by right-clicking the references folder and choosing “Add Reference…”.

That brings up a tabbed window and we’re interested in the leftmost tab, .NET.
Choose the oracle stuff and press OK:

Next add these lines to the list of includes

using Oracle.DataAccess;
using System.Data;

1 2	using Oracle.DataAccess; using System.Data;

Now we need to add a connection string to the class responsible for communicating with the Oracle database.

private const string connString = @"Data Source=TORCL;User Id=myUsername;Password=myPassword;";

1	private const string connString = @"Data Source=TORCL;User Id=myUsername;Password=myPassword;";

Examples of proper Oracle connection strings can be found at the URL below. I picked the topmost connection string for this demo:
http://www.connectionstrings.com/oracle

And now some coding

private static void DoOracleStuff( int param)
{
  OracleConnection conn = new OracleConnection(connString);
  try
  {
    conn.Open();
    OracleCommand command = conn.CreateCommand();
    command.CommandText = "UPDATE <TableName> SET Task_status = 'Assigned' WHERE task_id = :taskId";

    OracleParameter taskId = new OracleParameter();
    taskId.DbType = DbType.Int32;
    taskId.Value = param; // this is your variable, right?
    taskId.ParameterName = "taskId";

    command.Parameters.Add(taskId);

    command.ExecuteNonQuery();
    command.Dispose();
  }
  catch (Exception ex)
  {
  // Handle the exception     
  }
  finally
  {
    conn.Close();
  }
}

private static void DoOracleStuff( int param)

{

OracleConnection conn = new OracleConnection(connString);

try

{

conn.Open();

OracleCommand command = conn.CreateCommand();

command.CommandText = "UPDATE <TableName> SET Task_status = 'Assigned' WHERE task_id = :taskId";

OracleParameter taskId = new OracleParameter();

taskId.DbType = DbType.Int32;

taskId.Value = param; // this is your variable, right?

taskId.ParameterName = "taskId";

command.Parameters.Add(taskId);

command.ExecuteNonQuery();

command.Dispose();

}

catch (Exception ex)

{

// Handle the exception

}

finally

{

conn.Close();

}

And that is that.

Now you should be able to connect to an Oracle database and perform an update on one of its tables.

Performing a query on the same table is almost identical: switch the update statement out for a select statement and instead of using a ExecuteNonQuery() you should use a DataReader or a DataAdapter.

Just ask and I’ll show you how

The Funkalistic Blog

I'll get to that later…

Connecting to an Oracle Database (and doing an update on a table)