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

You are using an ODBC connection yet you pass an Oracle string. Either create an ODBC source or use OracleConnection — Panagiotis Kanavos, Mar 18 '14 at 15:33
Just to know, does your database has grants of all operation? Just to check can you grant all and check if you get connection up and running? and please check this site:https://community.oracle.com/thread/2497887 — Mehbube Arman, Mar 18 '14 at 18:54
If you want to use ODBC check this connection strings: http://www.connectionstrings.com/microsoft-odbc-for-oracle/ — Pellared, Mar 18 '14 at 22:21

arndtdv · Accepted Answer · 2014-03-15T12:29:22.877

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

 Install-Package odp.net.managed

odp.net.managed work without preinstalled Oracle Client

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

score 2 · Answer 2 · answered Mar 07 '14 at 10:49

2

you need to use OracleConnection

OracleConnection conn = new OracleConnection(connectionString);

download and install Oracle Data Provider for .NET

answered Mar 07 '14 at 10:49

Damith

62,401
13
102
153

I've got 2 queries 1. The download says 12c oracle data provider. Does end user need to have same version of provider to run in his/her system (End user has got Oracle 11g version installed) ? 2. Could you give me connection string complete syntax? Im new to this oracle connections. If there exists any possibility to go with Oracle 11g itself without additional installations that would be very great.Thanks for ur suggestions & help. – Shalem Mar 11 '14 at 05:10
@Shalem - you need to use ODP.NET, that's the recommended Oracle access method, and yes, you *must* install it on the client machine. You can't connect to an Oracle server from a give machine without installing the Oracle package on it. – Simon Mourier Mar 13 '14 at 07:01
Im not sure on getting ODP.Net in client machines. Please suggest with an alternative. – Shalem Mar 18 '14 at 08:09

score 0 · Answer 3 · answered Mar 12 '14 at 06:40

0

Go to Connections Strings for Oracle

Maybe will find some help

answered Mar 12 '14 at 06:40

score 0 · Answer 4 · answered Mar 12 '14 at 15:47

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

score 0 · Answer 5 · answered Mar 14 '14 at 08:00

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");
        }

    }
}
}

Thanks for help with the Connections string! I needed a way to not have to include the tnsnames.ora file with my deployment and this worked. — Trebor, Oct 11 '14 at 14:15

score 0 · Answer 6 · answered Mar 18 '14 at 22:24

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)

score -1 · Answer 7 · answered Sep 15 '14 at 07:20

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.

cannot connect to oracle server from C#.net application

7 Answers7