Search code examples
.netoracleoracle11g

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

Solution

  • 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, I guess it does not support TNS alias resolution from OID/LDAP)

       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, I guess it does not support TNS alias resolution from OID/LDAP)

       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.