mydomain
No ADS
No ADS

Connect to Oracle Database in C# without Oracle Client

  1. Introduction
  2. Connecting to Oracle from C# - Overview
  3. Download Libraries
  4. Copy the libraries into the Project
  5. Test Connection
  6. Working with Oracle Database using C#

1. Introduction

This document is based on:
  • Visual Studio 2013, 2015
  • Oracle 11g, 12c

2. Connecting to Oracle from C# - Overview

No ADS
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. Download Libraries

No ADS
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 isjust downloaded in previous step.

4. Copy the libraries into the Project

No ADS
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
<DIR>/instantclient_12_1
orannzsbb12.dll
<DIR>/instantclient_12_1
oraocci12.dll
<DIR>/instantclient_12_1
oraocci12d.dll
<DIR>/instantclient_12_1
oraociei12.dll
<DIR>/instantclient_12_1
oraons.dll
<DIR>/odp.net4/odp.net/bin/4
Oracle.DataAccess.dll
<DIR>/oramts/bin
oramts.dll
<DIR>/oramts/bin
oramts12.dll
<DIR>/oramts/bin
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.

5. Test Connection

No ADS
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:

6. Working with Oracle Database using C#

No ADS
Next tutorial - Working with Oracle Database using C#:
No ADS
No ADS