C# – cannot connect to oracle server from C#.net application

cconnection-stringnetoracletnsnames

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

Best Answer

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();
Related Topic