Connecting to Oracle Database using C# without Oracle Client

1- Introduction

This document is based on:
  • Visual Studio 2013 (OK for VS 2015)

  • Oracle 12c (OK for Oracle 11g)

2- Connecting to Oracle from C# - Overview

First, you need to understand connection principle from C# to Oracle Database.

Modal 1:

In this model, in computer running C# application, you need to install  Oracle Client, this is a part of Oracle that install in client to support connect to main Oracle database. If  Database Oracle is installed in a computer running C# application, you don't need to install  Oracle Client due to  Database Oracle itself is Oracleclient. Oracle Client capacity is of about 50MB.

Modal 2:

Model 2 allows you to connect directly to Oracle, without needing to install Oracle Client on the computer which run C # applications. You need to copy some DLL libraries of the Oracle client, and several ODAC XCopy libraries into your project.

And because not using Oracle Client, there is no tnsname.ora file containing information of Oracle Database, so the Connection String parameter specifies the content of the database like tnsname.ora file.
// Connection string to connect directly to Oracle.
string connString = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = "
     + host + ")(PORT = " + port + "))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = "
     + sid + ")));Password=" + password + ";User ID=" + user;

3- C# direct connection to Oracle without Oracle Client

3.1- Download Libraries

In case you download 64bit:

In case you download 32bit:

Download results:
Note: You can use the 32bit version just downloaded (Use for both 64bit and 32bit OS), if using 64bit version sometimes using C# connects to Oracle you may get a following error:
An unhandled exception of type 'System.BadImageFormatException' occurred in mscorlib.dll

Additional information: Could not load file or assembly 'Oracle.DataAccess, Version=4.121.2.0,
Culture=neutral, PublicKeyToken=89b483f429c47342' or one of its dependencies.
An attempt was made to load a program with an incorrect format.
Although my computer is 64bit OS, but I will use the ODAC 32bit which is  just downloaded in previous step.

3.2- Copy the libraries into the Project

I created a project called ConnectOracleWithoutClient to Demo:
Project was created.
Unzip the zip file you downloaded
Copy the following files:
Directory File Copy
<DIR>/instantclient_12_1 oci.dll
orannzsbb12.dll
oraocci12.dll
oraocci12d.dll
oraociei12.dll
oraons.dll
<DIR>/odp.net4/odp.net/bin/4 Oracle.DataAccess.dll
<DIR>/oramts/bin oramts.dll
oramts12.dll
oramtsus.dll
<DIR>/odp.net4/bin OraOps12.dll
Copy and Paste files directly to your project in  Visual Studio:
In Visual Studio, select all DLLs file and set properties for them.
  • Copy to Output Directory: Copy if newer
Declare the library, refer to Oracle.DataAccess.dll.

3.3- Test Connection

DBOracleUtils class has a utility method to connect directly to Oracle.
DBOracleUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;

namespace Tutorial.SqlConn
{
    class DBOracleUtils
    {

        public static OracleConnection
                       GetDBConnection(string host, int port, String sid, String user, String password)
        {

            Console.WriteLine("Getting Connection ...");

            // Connection string to connect directly to Oracle.
            string connString = "Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = "
                 + host + ")(PORT = " + port + "))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = "
                 + sid + ")));Password=" + password + ";User ID=" + user;


            OracleConnection conn = new OracleConnection();

            conn.ConnectionString = connString;

            return conn;
        }
    
    }
}
A utility class with parameters to connect to your Oracle database:
DBUtils.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;

namespace Tutorial.SqlConn
{
    class DBUtils
    {
        public static OracleConnection GetDBConnection()
        {
            string host = "192.168.205.1";
            int port = 1521;
            string sid = "db12c";
            string user = "simplehr";
            string password = "12345";

            return DBOracleUtils.GetDBConnection(host, port, sid, user, password);
        }
    }

}
Test Oracle Connection:
Program.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Oracle.DataAccess.Client;
using Tutorial.SqlConn;

namespace ConnectOracleWithoutClient
{
    static class Program
    {

        static void Main(string[] args)
        {
            //
            OracleConnection conn = DBUtils.GetDBConnection();

            Console.WriteLine("Get Connection: " + conn);
            try
            {
                conn.Open();

                Console.WriteLine(conn.ConnectionString, "Successful Connection");
            }
            catch (Exception ex)
            {
                Console.WriteLine("## ERROR: " + ex.Message);
                Console.Read();
                return;
            }

            Console.WriteLine("Connection successful!");

            Console.Read();
        }
    }
}
Running test:

4- Working with Oracle Database using C#

Next tutorial - Working with Oracle Database using C#: