Update

This article is quite old, you’re likely best off using ODAC via nuget nowadays. But I’ll leave it here for posterity:

Old article contents

Microsoft has deprecated System.Data.OracleClient, so here’s a simple example of using the new ODP.NET Oracle.DataAccess.Client, with the following benefits:

Simple Xcopy deployment
No Oracle instant client installation needed
No TnsNames.Ora file needed

Firstly, download the ODP.Net from Oracle. Don’t freak out, you won’t need to redistribute all this with your app, just ~30megs worth. One of the Xcopy variants is likely what you want: https://www.oracle.com/database/technologies/dotnet-odacdeploy-downloads.html

Grab Oracle.DataAccess.dll from: ODAC1110710beta.zip \ stage \ Components \ oracle.ntoledb.odp_net_2 \ 11.1.0.7.10 \ 1 \ DataFiles \ filegroup4.jar. Copy it into your project (For winforms: the same folder as your project’s Program.cs; For Asp.net: the Bin folder). In Visual Studio, right click references and add the Oracle.DataAccess.dll.

Write some code like this in your app:

using Oracle.DataAccess.Client;
...
string connstring =
  "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=myhost)(PORT=1527))" +
  "(CONNECT_DATA=(SID=mysid)));User Id=myuserid;Password=mypassword;";
using (OracleConnection conn = new OracleConnection(connstring))
{
  conn.Open();
  string sql = "select distinct owner from sys.all_objects order by owner";
  using (OracleCommand comm = new OracleCommand(sql, conn))
  {
    using (OracleDataReader rdr = comm.ExecuteReader())
    {
      while (rdr.Read())
      {
        Console.WriteLine(rdr.GetString(0));
      }
    }
  }
}

You’ll then need the following dll’s to be placed in the same folder as your EXE:

oci.dll (Find ‘oci.dll.dbl’ and rename it to remove the ‘.dbl’; in ODAC1110710beta.zip \ stage \ Components \ oracle.rdbms.rsf.ic \ 11.1.0.7.0 \ 1 \ DataFiles \ filegroup2.jar)
Oracle.DataAccess.dll (in ODAC1110710beta.zip \ stage \ Components \ oracle.ntoledb.odp_net_2 \ 11.1.0.7.10 \ 1 \ DataFiles \ filegroup4.jar)
oraociicus11.dll (in ODAC1110710beta.zip \ stage \ Components \ oracle.rdbms.ic \ 11.1.0.7.0 \ 1 \ DataFiles \ filegroup3.jar)
OraOps11w.dll (in ODAC1110710beta.zip \ stage \ Components \ oracle.ntoledb.odp_net_2 \ 11.1.0.7.10 \ 1 \ DataFiles \ filegroup3.jar) You may need the following dll’s, but I didn’t. I’d get them anyway, just to be safe, as some people say they’re needed:
orannzsbb11.dll (in ODAC1110710beta.zip \ stage \ Components \ oracle.ldap.rsf.ic \ 11.1.0.7.0 \ 1 \ DataFiles \ filegroup1.jar)
oraocci11.dll (in ODAC1110710beta.zip \ stage \ Components \ oracle.rdbms.rsf.ic \ 11.1.0.7.0 \ 1 \ DataFiles \ filegroup3.jar)
ociw32.dll (called ‘ociw32.dll.dbl’ in ODAC1110710beta.zip \ stage \ Components \ oracle.rdbms.rsf.ic \ 11.1.0.7.0 \ 1 \ DataFiles \ filegroup2.jar)
Newer versions of ODAC will likely require different DLLs, you can determine the required files by using the depends tool against Oracle.DataAccess.dll.

If you get the exception ‘The provider is not compatible with the version of Oracle client’, don’t stress, simply retrace your steps and make sure you get ALL those DLL’s from the same ODP.Net / ODAC distribution to avoid version number conflicts, and put them all in the same folder as your EXE (or, in the Bin folder if its an Asp.Net application, then try restarting IIS).

Good luck!

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

Using the new ODP.Net to access Oracle from C# with simple deployment

Update

Old article contents

Chris Hulbert