CODEWITHSUNDEEP
c#oracle-database
haughtonomous
haughtonomous

Reputation: 4850

Unable to connect to Oracle 11g database using Oracle.ManagedDataAccess.dll instead of the Microsoft System.Data.OracleClient

I am trying to migrate a .Net 4.5.2 application from using Microsoft's deprecated System.Data.OracleClient dll to using Oracle's own Oracle.ManagedDataAccess.dll (using the Nuget package v12.1.1.24160419). On the machine in question I have the Oracle client installed, and I have a tnsnames.ora file set up which the original app successfully uses to connect. I can also successfully connect with Oracle's SqlPlus and SqlDeveloper tools. However if I try to use the Oracle dll my application cannot connect.

To distill this down to the very basics I have created a very simple console application (code below) to see what is going on. If the project references the Microsoft dll (with the appropriate using statement) it will connect, but if instead I reference the Oracle.ManagedDataAccess.dll (with the appropriate using statement) it complains about a TNS error:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

I've tried adding a SERVICE_NAME clause to the tnsnames.ora file but with no improvement.

What else do I need to do?

Test app is:

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
//using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace oracleconnect
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                InitializeDBConnection();
            }
            finally
            {
                if (_con.State==System.Data.ConnectionState.Open)
                _con.Close();
            }
        }

        static private OracleConnection _con;
        private const string connectionString = "Data Source=oracledbserver2;User ID=MATTESTNHSYS;Password=thePassword";



        private static void InitializeDBConnection()
        {
            _con = new OracleConnection();
            _con.ConnectionString = connectionString;
            _con.Open();
        }
    }
}

Upvotes: 1

Views: 2201

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Oracle.ManagedDataAccess.dll has a different search pattern to find your tnsnames.ora file. Unlike System.Data.OracleClient or SQL*Plus Oracle.ManagedDataAccess.dll does not read TNS_ADMIN value from Registry or Environment variable.

See Oracle Data Provider for .NET, Managed Driver Configuration

  1. data source alias in the dataSources section under <oracle.manageddataaccess.client> section in the .NET config file (i.e. machine.config, web.config, user.config).
  2. data source alias in the tnsnames.ora file at the location specified by TNS_ADMIN in the .NET config file. Locations can consist of either absolute or relative directory paths.
  3. data source alias in the tnsnames.ora file present in the same directory as the .exe.

Upvotes: 1

user2250152
user2250152

Reputation: 20625

Data Source should be defined like this:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=type_hostname)(PORT=type_port))(CONNECT_DATA=(SERVICE_NAME=type_service_name_from_tsnames)))

And the connection string like:

private const string connectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=type_hostname)(PORT=type_port))(CONNECT_DATA=(SERVICE_NAME=type_service_name_from_tsnames)));User ID=MATTESTNHSYS;Password=thePassword";

Upvotes: 0

Related Questions