the MMM blog

Connect to Oracle Database in C# without using Oracle.DataAccess

Connect to Oracle Database in C# without using Oracle.DataAccess


There are a few different ways to connect to Oracle database in C# program. The most native one is to use ODP.NET which is provided by Oracle. But in order to use it, you have to install Oracle client on your machine and your production server. There are some ways to work around this, i.e. carry the Oracle instant client in your project. But for those who just want to connect to Oracle database without installing or carrying Oracle client, here is the solution.

The Devert Universal ADO.NET Provider offers everything you need to connect to Oracle database in your C# program. It offers free version and professional version. The professional version also has 30-day trial period.

  1. Download Devert dotConnect Universal
  2. Install the exe.
  3. Create your solution and project in Visual Studio
  4. Reference Devert dotConnect Universal DLLs in your project. The default directory should be 
    • C:\Program Files (x86)\Devart\dotConnect\Universal\Devart.Data.dll
    • C:\Program Files (x86)\Devart\dotConnect\Universal\Devart.Data.Universal.dll
    • C:\Program Files (x86)\Devart\dotConnect\Universal\Providers\Devart.Data.Universal.Oracle.dll
  5. Copy the following code into your project.
using (UniConnection con = new UniConnection("Provider=Oracle;direct=true;data source=your_host;port=1521;sid=database_name;user=user_name;password=user_password"))
{
    con.Open();
    DataSet ds = new DataSet();
    UniDataAdapter udA = new UniDataAdapter();
    UniCommand cmd = new UniCommand(your_query, con);
    udA.SelectCommand = cmd;
    udA.Fill(ds);
    foreach (DataTable dataTable in ds.Tables)
    {
        foreach (DataRow dataRow in dataTable.Rows)
        {
            // read dataRow["field_name"]
        }
    }
}

To add some parameters

using (UniConnection con = new UniConnection("Provider=Oracle;direct=true;data source=your_host;port=1521;sid=database_name;user=user_name;password=user_password"))
{
    con.Open();
    DataSet ds = new DataSet();
    UniDataAdapter udA = new UniDataAdapter();
    UniCommand cmd = new UniCommand("BEGIN OPEN :cur FOR select table_name from user_tables; END;", con);
cmd.Parameters.Add("cur", UniDbType.Cursor);
cmd.Parameters["cur"].Direction = ParameterDirection.Output;
    udA.SelectCommand = cmd;
    udA.Fill(ds);
    foreach (DataTable dataTable in ds.Tables)
    {
        foreach (DataRow dataRow in dataTable.Rows)
        {
            // read dataRow["field_name"]
        }
    }
}

If you want to see how to connect Oracle database by using ODP.NET, the code snippetis here.

"It is never too late to be what you might have been"

Contact me!

Have questions? Feel free to use the form or send me an email with any questions.

I will be responsive to your requests and your questions.