CODEWITHSUNDEEP
c#.netoracle-databaseconnection-stringtnsnames
Shalem
Shalem

Reputation: 1516

cannot connect to oracle server from C#.net application

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

Upvotes: 4

Views: 7671

Answers (7)

Shalem
Shalem

Reputation: 1516

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.

Upvotes: -1

02Anant
02Anant

Reputation: 318

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)

Upvotes: 0

arndtdv
arndtdv

Reputation: 111

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

Upvotes: 6

akdora
akdora

Reputation: 903

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

    }
}
}

Upvotes: 0

Vikas Hardia
Vikas Hardia

Reputation: 2695

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

Upvotes: 0

user3383479
user3383479

Reputation:

Go to Connections Strings for Oracle

Maybe will find some help

Upvotes: 0

Damith
Damith

Reputation: 63065

you need to use OracleConnection

OracleConnection conn = new OracleConnection(connectionString);

download and install Oracle Data Provider for .NET

Upvotes: 2

Related Questions