Connecting to Oracle Database Through C#

Connecting to Oracle Database through C#?

First off you need to download and install ODP from this site
http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

After installation add a reference of the assembly Oracle.DataAccess.dll.

Your are good to go after this.

using System; 
using Oracle.DataAccess.Client; 

class OraTest
{ 
    OracleConnection con; 
    void Connect() 
    { 
        con = new OracleConnection(); 
        con.ConnectionString = "User Id=<username>;Password=<password>;Data Source=<datasource>"; 
        con.Open(); 
        Console.WriteLine("Connected to Oracle" + con.ServerVersion); 
    }

    void Close() 
    {
        con.Close(); 
        con.Dispose(); 
    } 

    static void Main() 
    { 
        OraTest ot= new OraTest(); 
        ot.Connect(); 
        ot.Close(); 
    } 
}

How to connect to an Oracle database Connection from .Net Core

Oracle published the official Data Provider for .NET Core on nuget.

Here is a basic example to show how to use it:

using Oracle.ManagedDataAccess.Client;

public void Execute(string queryString, string connectionString)
{
    using (OracleConnection connection = new OracleConnection(connectionString))
    {
        OracleCommand command = new OracleCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

Basically you can use it exactly like the official .NET System.Data.SqlClient (easy to find online tutorials for this) and just replace everywhere in the code SqlConnection with OracleConnection and SqlCommand with OracleCommand.

Connect to Oracle database from asp.net core

To be precise,

1. Right click on your project
2. Go to Manage NuGet packages
3. Search for Oracle.manageddataaccess.core . You will get Nuget package 2.12.0-beta2 version. Add it.

4. In the page, import as below to access oracle classes

   using Oracle.ManagedDataAccess.Client;

This is a beta version of Oracle data access provider and PROD version is supposed to be available at the end of 3rd quarter of 2018.

C# connect to Oracle database using Wallet

here you have examples

---

Managed Assemby/Driver

1. With managed assemby you have 2 options:

First option:

• put 2 files (sqlnet.ora, tnsnames.ora) from Wallet.zip to your executable path (bin/Debug)
• edit sqlnet.ora and change 'DIRECTORY' variable

WALLET_LOCATION=(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=<PATH_TO_WALLET_DIRECTORY>)))

Second option:

• add XML to your app.config

<oracle.manageddataaccess.client>
  <version number="*">
    <settings>
      <setting name="TNS_ADMIN" value="PATH_TO_WALLET_DIRECTORY"/>
      <setting name="Wallet_Location" value="PATH_TO_WALLET_DIRECTORY"/>  
    </settings>
  </version>
</oracle.manageddataaccess.client>

---

Unmanaged Assemby/Driver

• put 2 files (sqlnet.ora, tnsnames.ora) from Wallet.zip to your executable path (bin/Debug)
• edit sqlnet.ora and change 'DIRECTORY' variable (same as manged driver, option first)

---

Connecting to DB

public static OracleConnection TakeMeToTheClouds()
{
    var user = "admin";
    var password = "YOUR PASSWORD";
    var ds = "THIS YOU FIND IN 'tnsnames.ora' FILE";

    var connectionString = $"User Id={user};Password={password};Data Source={ds};";
    return new OracleConnection(connectionString);
}

How to connect Oracle Database to Visual Studio C# project

I'd really recommend you use Oracle's ODP.net. It works very efficiently between Oracle and .NET and can take advantage of many of the low level features (such as bulk inserts/updates) available through the Oracle Call Interface (OCI).

From there, ODP.net has a OracleConnectionStringBuilder class that demistifys the connection string difficulties you have with most databases:

OracleConnectionStringBuilder sb = new OracleConnectionStringBuilder();
sb.DataSource = "<your datasource>";
sb.UserID = "library";
sb.Password = "library";

OracleConnection conn = new OracleConnection(sb.ToString());
conn.Open();

If your database is remote, Oracle's EZ Connect makes it nice to not have to worry about TNS names:

sb.DataSource = "hostname.whatever.com:1521/ServiceName";

c# Connect to Oracle database

Having struggled some years ago with connection to Oracle servers I used the TOAD freeware version to debug the connection strings. It was a life saver. Also, I needed the assistance of the Oracle DBAs.

It looks like your application might not be able to find the server.

---

Related Topics