Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to connect to Oracle 11 database from . net

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).

like image 715
PrivateJoker Avatar asked Jan 15 '16 01:01

PrivateJoker


1 Answers

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 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 these 32 (17 64-bit + 15 32-bit) variants at once.

like image 88
Wernfried Domscheit Avatar answered Oct 19 '22 08:10

Wernfried Domscheit