Connect to Oracle Database in C# without Oracle Client
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.
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:
No ADS
C# Programming Tutorials
- Inheritance and polymorphism in C#
- What is needed to get started with C#?
- Quick learning C# for Beginners
- Install Visual Studio 2013 on Windows
- Abstract class and Interface in C#
- Install Visual Studio 2015 on Windows
- Compression and decompression in C#
- C# Multithreading Programming Tutorial with Examples
- C# Delegates and Events Tutorial with Examples
- Install AnkhSVN on Windows
- C# Programming for Team using Visual Studio and SVN
- Install .Net Framework
- Access Modifier in C#
- C# String and StringBuilder Tutorial with Examples
- C# Properties Tutorial with Examples
- C# Enums Tutorial with Examples
- C# Structures Tutorial with Examples
- C# Generics Tutorial with Examples
- C# Exception Handling Tutorial with Examples
- C# Date Time Tutorial with Examples
- Manipulating files and directories in C#
- C# Streams tutorial - binary streams in C#
- C# Regular Expressions Tutorial with Examples
- Connect to SQL Server Database in C#
- Work with SQL Server database in C#
- Connect to MySQL database in C#
- Work with MySQL database in C#
- Connect to Oracle Database in C# without Oracle Client
- Work with Oracle database in C#
Show More