cannot connect to oracle server from C#.net application

Question

I'm trying to connect to remote Oracle server. My connection string -

OdbcConnection con = new OdbcConnection();
con.ConnectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= xxxx)(PORT=xxxxx))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=abc.domain.com)));USER ID=user1;Password=pwd;";

I encountered error saying - "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified" (System.Data.Odbc.OdbcException) Exception Message = "ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified", Exception Type = "System.Data.Odbc.OdbcException", Exception WinRT Data = ""

I specified my connection string according to my TNSNAMES.ora

Entry for my DB in TNSNAMES.ora goes like this:

DB.WORLD=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST= xxxx)
      (PORT=xxxxx)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=abc.domain.com)
    )
  )

Can someone explain on the error. Please help/suggest if my connection string went wrong and how to connect to Oracle server from my windows application

Answer 1

first install odp.net.managed using nuget packet manager:

 Install-Package odp.net.managed

odp.net.managed work without preinstalled Oracle Client

next:

const string connectionString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= xxxx)(PORT=xxxxx))(CONNECT_DATA=(SERVER=dedicated)(SERVICE_NAME=abc.domain.com)));USER  ID=user1;Password=pwd;";
var connection = new OracleConnection(connectionString);
connection.Open();

if you have tnsnames.ora in application folder:

const string connectionString = @"Data Source=DB.WORLD;USER  ID=user1;Password=pwd;";
var connection = new OracleConnection(connectionString);
connection.Open();

or if tnsnames.ora in other folder:

Environment.SetEnvironmentVariable("TNS_ADMIN", @"path_to_tnsadmin.ora");
const string connectionString = @"Data Source=DB.WORLD;USER  ID=user1;Password=pwd;";
var connection = new OracleConnection(connectionString);
connection.Open();

Answer 2

you need to use OracleConnection

OracleConnection conn = new OracleConnection(connectionString);

download and install Oracle Data Provider for .NET

Answer 3

Go to Connections Strings for Oracle

Maybe will find some help

Answer 4

Use following Code:

using System;
using Oracle.DataAccess.Client; 

class ConnectionSample
{
  static void Main()
  {
    OracleConnection con = new OracleConnection();

    //using connection string attributes to connect to Oracle Database
    con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle";
    con.Open();
    Console.WriteLine("Connected to Oracle" + con.ServerVersion);

    // Close and Dispose OracleConnection object
    con.Close();
    con.Dispose();
    Console.WriteLine("Disconnected");
  }
}

Source ONE , TWO and THREE

Answer 5

Try something like this class :

public class OracleOperations
{
OracleConnection oraConn = new OracleConnection();

private bool connStatus;

public OracleOperations()
{
    connStatus = false;
    connect();
}

~OracleOperations()
{
    disconnect();
}

public bool getConnStatus()
{
    return connStatus;
}

public void connect()
{
    string connString = "User Id=xxxx; Password=yyyyy; Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1583))(CONNECT_DATA=(SERVER=dedicated)(SID=oracledb)))";
    if (oraConn.State != ConnectionState.Open)
    {
        try
        {
            oraConn.ConnectionString = connString;
            oraConn.Open();
            Console.WriteLine("Successful Connection");
            connStatus = true;
        }
        catch (Exception eOra)
        {
            Console.WriteLine(eOra.Message+ "Exception Caught");
            connStatus = false;
            throw eOra;
        }
    } 
}
public void disconnect()
{
    if (oraConn.State == ConnectionState.Open)
    {
        try
        {
            oraConn.Close();
            connStatus = false;
            Console.WriteLine("Connection Closed");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message + "Exception Caught");
        }

    }
}
}

Answer 6

I would try Tnsping utility to make sure you can connect via tnsnames.ora

Try putting tnsnames.ora and sqlnet.ora in the same folder of the application and see if that addresses the issue.

With Managed ODP.Net there is one catch it does not support LDAP look up (e.g. LDAP.ora)

Answer 7

I'Ve Created an app.config File and configured the DB entry like this

<configuration>
  <configSections>
    <section name ="Environment" type="System.Configuration.NameValueSectionHandler" />
  </configSections>

  <Environment>
    <add key ="CIT" value ="Password=pwd123;User ID=abc123;Data Source=db1;Persist Security Info=True;Provider=MSDAORA"/>
    <add key ="SIT" value ="Password=pwd234;User ID=abc234;Data Source=db2;Persist Security Info=True;Provider=MSDAORA"/>
    <add key ="UAT" value ="Password=pwd345;User ID=abc345;Data Source=db3;Persist Security Info=True;Provider=MSDAORA"/>

  </Environment>
</configuration>

Reffered that configuration into my form using ConfigurationManager(Need to refer the assembly - system.configuration). Add namespace - using System.Collections.Specialized to avail NameValueCollection. Code goes like this

environments = ConfigurationManager.GetSection("Environment") as NameValueCollection; 
string strConnString = environments[envs]; 
conn = new OleDbConnection(strConnString); 
conn.Open(); 
OleDbDataAdapter objDa = new OleDbDataAdapter("select * from tblABC", conn); 
DataSet ds1 = new DataSet(); 
objDa.Fill(ds1); dataGridView1.DataSource = ds1.Tables[0];

Using datset, i've populated datagrid using an OleDbDataAdapter. It worked for my Windowsapplication.