.net - How to connect to Oracle 11 database from . net

Question

What is the easiest way to connect a . NET web application to an Oracle 11g database? Can EntityFramework handle this right out of the box? Or will I need some sort or ODBC plugin from Oracle?

*I'm running from a locked down environment, so I can't really test any of these scenarios at this time.

I'm currently running VS2010, but I'm looking to see if they will let me run with VS2013 (no nuget).

Answer 1

I know 17 ways to connect to an Oracle Database from a .NET application.

• ODBC with driver from Oracle

   var connectString = "Driver={Oracle in OraClient11g_home1};Uid=scott;Pwd=secret;DBQ=orcl1";
   var con = new System.Data.Odbc.OdbcConnection(connectString);
   con.Open();
  

  (exact driver name Oracle in OraClient11g_home1 depends on installed Oracle version)

• ODBC with driver from Microsoft (only for 32bit, deprecated, does not work anymore with Oracle Client 18c or newer)

   var connectString = "Driver={Microsoft ODBC for Oracle};Uid=scott;Pwd=secret;Server=orcl1";
   var con = new System.Data.Odbc.OdbcConnection(connectString);
   con.Open();
  

• Oracle Provider for OLE DB

   var connectString = "Provider=OraOLEDB.Oracle;Data Source=orcl1;Password=secret;User ID=scott";
   var con = new System.Data.OleDb.OleDbConnection(connectString);
  
   con.Open();
  

• Microsoft OLE DB Provider for Oracle (only for 32bit, deprecated, does not work anymore with Oracle Client 18c or newer)

   var connectString = "Provider=MSDAORA;Data Source=orcl1;Password=secret;User ID=scott";
   var con = new System.Data.OleDb.OleDbConnection(connectString);
   con.Open();
  

• Microsoft .NET Framework Data Provider for Oracle (deprecated)

   var connectString = "Data Source=orcl1;User ID=scott;Password=secret";
   var con = new System.Data.OracleClient.OracleConnection(connectString);
   con.Open();
  

• Oracle Data Provider for .NET (ODP.NET)

   var connectString = "Data Source=orcl1;User ID=scott;Password=secret";
   var con = new Oracle.DataAccess.Client.OracleConnection(connectString);
   con.Open();
  

• Oracle Data Provider for .NET, Managed Driver (ODP.NET Managed Driver)

   var connectString = "Data Source=orcl1;User ID=scott;Password=secret";
   var con = new Oracle.ManagedDataAccess.Client.OracleConnection(connectString);
   con.Open();
  

• dotConnect for Oracle from Devart (formerly known as OraDirect .NET from Core Lab)

   var connectString = "Data Source=orcl1;User ID=scott;Password=secret";
   var con = new Devart.Data.Oracle.OracleConnection(connectString);
   con.Open();
  

• dotConnect Universal from Devart (uses deprecated System.Data.OracleClient)

   var connectString = "Provider=OracleClient;Data Source=orcl1;User ID=scott;Password=secret";
   var con = new Devart.Data.Universal.UniConnection(connectString);
   con.Open();
  

• ODBC with driver from Devart

   var connectString = "Driver={Devart ODBC Driver for Oracle};Uid=scott;Pwd=secret;Server=orcl1";
   var con = new System.Data.Odbc.OdbcConnection(connectString);
   con.Open();
  

• DataDirect Connect for ADO.NET from Progress

   var connectString = "Data Source=orcl1;User ID=scott;Password=secret";
   var con = new DDTek.Oracle.OracleConnection(connectString);
   con.Open();
  

• ODBC with driver from Progress

   var connectString = "Driver={DataDirect 8.0 Oracle Wire Protocol};Uid=scott;Pwd=secret;ServerName=orcl1";
   var con = new System.Data.Odbc.OdbcConnection(connectString);
   con.Open();
  

• ODBC with Oracle Driver from Easysoft (did not work for me)

   var connectString = "Driver={Easysoft ODBC-Oracle Driver};Database=orcl1;Uid=scott;Pwd=secret;Server=orcl1;SID=orcl1";
   var con = new System.Data.Odbc.OdbcConnection(connectString);
   con.Open();
  

• ODBC with Oracle WP Driver from Easysoft (did not work for me)

   var connectString = "Driver={Easysoft ODBC-Oracle WP Driver};Database=orcl1;Uid=scott;Pwd=secret;Server=orcl1;SID=orcl1";
   var con = new System.Data.Odbc.OdbcConnection(connectString);
   con.Open();
  

• ADO.NET Provider for Oracle OCI from CData

   var connectString = "Data Source=orcl1;User=scott;Password=secret";
   var con = new System.Data.CData.OracleOci.OracleOciConnection(connectString);
   con.Open();
  

• ODBC with Driver for Oracle OCI from CData

   var connectString = "Driver={CData ODBC Driver for Oracle OCI};Data Source=orcl1;User=scott;Password=secret";
   var con = new System.Data.Odbc.OdbcConnection(connectString);
   con.Open();
  

• ODBC with Oracle Driver with SQL Connector from Magnitude (formerly Simba)

   var connectString = "Driver={Simba Oracle ODBC Driver};TNS=orcl1;UID=scott;PWD=secret";
   var con = new System.Data.Odbc.OdbcConnection(connectString);
   con.Open();
  

In general all of them are working. For new application you should use ODP.NET or ODP.NET Managed Driver. ODP.NET Managed Driver is quite new and has still a few limitations and also the "newest" bugs.

The third party providers which may come with additional costs.

Apart from ODP.NET Managed Driver, Progress and Easysoft ODBC-Oracle WP Driver all drivers/providers need to have an Oracle (Instant-) Client installed.

I developed an application in github which runs all this 32 (17 64-bit + 15 32-bit) variants at once.