Connect to Oracle Database in C# without Oracle Client

Introduction
Connecting to Oracle from C# - Overview
Download Libraries
Copy the libraries into the Project
Test Connection
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

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

http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

64-bit

32-bit

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

4. 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
<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

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#

Next tutorial - Working with Oracle Database using C#:

Work with Oracle database in CSharp