How to connect to Oracle DB from .NET?

Question

When I open SQL Command Line, I write CONNECT username/password@[//]host[:port][/service_name] and it connects me to the database just fine. However, I'm unable to connect from a .NET project using a connection string. I have tried many things such as <add name="ConnectionString" connectionString="Data Source=username/password@[//]host[:port][/service_name];" /> and <add name="ConnectionString" connectionString="server=tcp:host;Initial Catalog=service_name; user id=username; password=password; Connection Timeout=180;" providerName="System.Data.OracleClient" />, but nothing worked so far. Whenever I get to sconn.Open(); in the following:

var CurrentConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection sconn = new SqlConnection(CurrentConnectionString);
sconn.Open();

I practically always get the following error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

How can I connect to the database properly?

Answer 1

Try following connection string

string con = "Data Source=(DESCRIPTION =(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST = 000.00.0.00)(PORT = 0000)))(CONNECT_DATA =(SERVICE_NAME = database)));User ID=User/Schema;Password=password;Unicode=True";

& then use this conection string as follow

using (OracleConnection objConn = new OracleConnection(con))
 {
   \\ code
 }

Answer 2

I highly recommend using the "Official Oracle ODP.NET, Managed Driver".

https://www.nuget.org/packages/Oracle.ManagedDataAccess/

Or the one for Entity framework: https://www.nuget.org/packages/Oracle.ManagedDataAccess.EntityFramework/

After installing via Visual Studio it opens a readme that I also recommend you read.

Oracle provide massive amounts of documentation. This is where to start: http://www.oracle.com/technetwork/topics/dotnet/whatsnew/index.html