Update:

This article is quite old, you’re likely best off using ODAC via nuget nowadays.

I also wrote a related article which may help.

Summary:

The other day, I needed to access an oracle database from a C# console application, and google found a whole heap of different ways to achieve this. Here’s the best way i found that works, and you don’t even need to install any oracle software. I’ve deliberately kept this entry short, to emphasise that this is the simple and straightforward way.

Also, you may want to check out another post for a simple application using this technique: Oracle Explorer - very simple open source Toad alternative.

Oracle bits: Full credit: This part uses details from http://www.codeproject.com/KB/database/C__Instant_Oracle.aspx

You’ll need the following files, which can be obtained from within the ‘basic lite’ version of oracle’s instant client: oci.dll orannzsbb11.dll oraocci11.dll oraociicus11.dll In my case, i downloaded the ‘instantclient-basiclite-win32-11.1.0.6.0.zip’ file from their website, the link is here. You’ll need to place these DLL’s in the same folder as your application’s .EXE. If you’re using asp.net, put it in the ‘Bin’ folder of your application. The important thing to note is that you don’t need to install the instant client, or register these DLLs or anything, just grab them from the zip file and ignore the rest of its contents.

Visual Studio bits: Note that i’m using .Net 2, with Visual Studio 2005. There may be differences in other versions, however i’m hoping for your sake that it is similar enough to figure out. In the solution explorer, right click on ‘References’ and choose ‘Add Reference…’ as below: [[posterous-content:GhElHlcfCwqlumlGHfis]] Then scroll through the list until you find ‘System.Data.OracleClient’ and click OK. [[posterous-content:jjmbyvEHbBAbEswahink]]

Code: Now for the code. You’ll need this ‘using’ at the top of your file:

using System.Data.OracleClient;

Here’s a simple function I wrote to create an oracle connection string, it may be useful to you:

public string OracleConnString(string host,string port,string servicename,string user,string pass)
{
  return String.Format(
    "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST={0})" +
    "(PORT={1}))(CONNECT_DATA=(SERVICE_NAME={2})));User Id={3};Password={4};",
    host,
    port,
    servicename,
    user,
    pass);
}

And here’s the gist of opening a connection, and reading all the lines. You’ll notice the interface is pretty much the same as accessing SQL server.

string connectionstring = OracleConnString("aaa","1521","bbb","ccc","ddd");
string sql = "select * from some_table";
      
using (OracleConnection conn = new OracleConnection(connectionstring)) // connect to oracle
{
  conn.Open(); // open the oracle connection
  using (OracleCommand comm = new OracleCommand(sql, conn)) // create the oracle sql command
  {
    using (OracleDataReader rdr = comm.ExecuteReader()) // execute the oracle sql and start reading it
    {
      while (rdr.Read()) // loop through each row from oracle
      {
        Console.WriteLine( rdr[0] );             // You can do this
        Console.WriteLine( rdr.GetString(0); );  // or this
        Console.WriteLine( rdr["column_name"] ); // or this
      }
      rdr.Close(); // close the oracle reader
    }
  }
  conn.Close(); // close the oracle connection
}

That’s all!

Copying from Oracle to Sql Server Here’s an example of copying a 2-column table from Oracle to Sql server. Lets pretend this table is called ‘foo’, and has two integer columns ‘x’ and ‘y’.

private void CopyOracleToSql()
{
  string connectionstring = OracleConnString("host","1521","servicename","user","pass");
  using (OracleConnection conn = new OracleConnection(connectionstring)) // connect to oracle
  {
    conn.Open(); // open the oracle connection
    string sql = "select x,y from foo";
    using (OracleCommand comm = new OracleCommand(sql, conn)) // create the oracle sql command
    {
      using (OracleDataReader rdr = comm.ExecuteReader()) // execute the oracle sql and start reading it
      {
        using (SqlConnection sql_conn = new SqlConnection(sqlconnstring)) // connect to the sql 2005 database
        {
          sql_conn.Open(); // open the sql database

          // Read the data from oracle, and every 100 rows send it to the sql database
          StringBuilder sb = new StringBuilder(); // build the insert statements with this
          int row = 0;
          while (rdr.Read()) // loop through each row from oracle
          {
            sb.AppendFormat(
              "insert into foo(x,y) values({0},{1});",
              rdr[0], rdr[1]);
            row++;

            if (row >= 100) // send to the DB every 100 rows
            {
              using (SqlCommand sql_comm = new SqlCommand(sb.ToString(), sql_conn))
              {
                sql_comm.ExecuteNonQuery();
                row = 0;
                sb = new StringBuilder();
              }
            }
          }

          // get the remaining few rows and send them to the DB
          if (sb.Length > 0)
          {
            using (SqlCommand sql_comm = new SqlCommand(sb.ToString(), sql_conn))
            {
              sql_comm.ExecuteNonQuery();
            }
          }

          rdr.Close(); // close the oracle reader
          sql_conn.Close(); // close the sql connection
        }
      }
    }
    conn.Close(); // close the oracle connection
  }
}

Thanks for reading! And if you want to get in touch, I'd love to hear from you: chris.hulbert at gmail.

Chris Hulbert

(Comp Sci, Hons - UTS)

Software Developer (Freelancer / Contractor) in Australia.

I have worked at places such as Google, Cochlear, Assembly Payments, News Corp, Fox Sports, NineMSN, FetchTV, Coles, Woolworths, Trust Bank, and Westpac, among others. If you're looking for help developing an iOS app, drop me a line!

Get in touch:
[email protected]
github.com/chrishulbert
linkedin

Subscribe via RSS

Connecting to Oracle from C# / Winforms / Asp.net without tnsnames.ora

Update:

Summary:

Chris Hulbert