VS2010 + Oracle driver: ORA-12154: TSN:could not resolve the connect identifier specified

I am using:

• Visual Studio 2010
• .Net Framework Data Provider for Oracle
• Oracle Developer Tools for Visual Studio (from Oracle's website)

I tried installing 'Oracle Developer Tools for Visual Studio' and created tnsnames.ora and sqlnet.ora files in my C:\app\ [my username]\product\11.2.0\client_1\Network\Admin directory.

They look like this:

# tnsnames.ora

ORATEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dbs-oratest)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = [ORATEST])
    )
  )

and

# sqlnet.ora

SQLNET.AUTHENTICATION_SERVICES= (ALL)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

When I try using the .Net Framework Data Provider for Oracle driver to set up a new connection (or any other driver for that matter: OLE, ODBC, etc) it gives the error:

ORA-12154: TSN:could not resolve the connect identifier specified

Using the OLE driver on a mac开发者_如何转开发hine without the Oracle client installed DOES work though:

OleDbConnection conn = new OleDbConnection(
    "Provider=MSDAORA;Data Source=ORATEST;" + 
    "Persist Security Info=True;Password=readonly;User ID=readonlyuser");

What am I doing wrong? Are there any simple instructions online about how to install a basic Oracle driver?

Thank you in advance!

The best solution I found was to use the Oracle Data Access Client library, and include the entire TNS names entry in the connection string. This allows the project to be easily published to a web server, ClickOnce, etc.

Here are the steps necessary to set up the Oracle driver working in your project:

1) Get DLLs from 'Oracle Data Provider for .NET' package

Download installer file from this location: http://www.oracle.com/technetwork/topics/dotnet/index-085163.html

I went ahead and installed the full 200 MB ODAC with Oracle Developer Tools for Visual Studio, but you only really need four DLLs from this download. (You may be able to extract them directly from the installer package, instead of going through the entire install process, or perhaps one of the smaller download includes all of them.)

2) Reference DLLs in your project

Search the installation directory of the Oracle Data Access Client and drag the following four DLLs into the root of your project:

• Oracle.DataAccess.dll
• oci.dll
• oraciicus11.dll
• OraOps11w.dll

Set the Copy to Output Directory property all of the files except Oracle.DataAccess.dll to Copy always.

Under Project --> Add Reference..., click on the Browse tab and select the Oracle.DataAccess.dll file.

3) Use the driver with full connection string (optional)

So as not to have to worry about TNS names files being set up on the machines the application was deployed to, I put the entire definition in the file as shown by connectionstrings.com. It makes the connection string a little bulky, but removed a lot of the TNS Names file headaches I was experiencing before:

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=servername)(PORT=‌​1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=servicename)));User Id=username;Password=********;

Here's the full class I used to test the driver:

using System;
using System.Data;
using Oracle.DataAccess.Client;

static class Program
{
    [STAThread]
    static void Main()
    {
        TestOracle();
    }

    private static void TestOracle()
    {
        string connString = 
            "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)" + 
            "(HOST=servername)(PORT=‌​1521)))" +
            "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=servicename)));"+ 
            "User Id=username;Password=********;";
        using (OracleConnection conn = new OracleConnection(connString))
        {
            string sqlSelect = "SELECT * FROM TEST_TABLE";
            using (OracleDataAdapter da = new OracleDataAdapter(sqlSelect, conn))
            {
                var table = new DataTable();
                da.Fill(table);

                if (table.Rows.Count > 1) 
                    Console.WriteLine("Successfully read oracle.");
            }
        }
    }
}

You should use the Oracle Data Access Client library, and then the OracleConnection object instead.

http://www.oracle.com/technology/sample_code/tech/windows/odpnet/howto/connect/index.html

I know that Oracle is kind of picky with TNS names file. I usually count on the DBA's for this. SQL-Server is much easier to get going...

Use local IIS web server instead of Visual Studio Development server (Project Settings - WEB) did the trick for me!

Tns-12154 had me pulling my hair out... web site worked fine in VS2008...

Regards,

Mike

The code that I´m using is the above.

P.S.: I´ve tested many times. Using Visual Studio .Net 2010 (VB.Net 2010).

Dim conn As New Odbc.OdbcConnection
Dim cmd As New Odbc.OdbcCommand
Dim drResult As Odbc.OdbcDataReader
Dim connString As String
Dim QuerySQL As String

connString = "Driver={Microsoft ODBC for Oracle};CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORACLEDB01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORACLE_INSTANCE_NAME)));Uid=john;Pwd=mypassword;"
QuerySQL = "select first_name, last_name from employees where id = 28"

conn.ConnectionString = connString
conn.Open()
cmd.Connection = conn
cmd.CommandText = QuerySQL
drResult = cmd.ExecuteReader()

While drResult.Read
    TextBox1.Text = TextBox1.Text & drResult("last_name") & ", " & drResult("first_name") & Environment.Newline
End While
drResult.Close()