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.