Oracle Connection String in C#

using Oracle.DataAccess.Client;

OracleConnection myConnection = new OracleConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();

// execute queries

myConnection.Close();

Data Source=TORCL;User Id=urUsername;Password=urPassword;  

Data Source=TORCL;Integrated Security=SSPI;  `

Data Source=urOracle;User Id=urUsername;Password=urPassword;DBA Privilege=SYSDBA;  

Data Source=urOracle;User Id=urUsername;Password=urPassword;Load Balancing=True;  

Data Source=username/password@urserver//instancename;  

Data Source=username/password@urserver/urservice:dedicated/instancename;  

Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort)))  
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=urOracleSID)));User Id=urUsername;  
Password=urPassword;  

Data Source=username/password@//urserver:1433/ur.service.com;  

Data Source=urOracle;User Id=/;  

Data Source=urOracle;User Id=urUsername;Password=urPassword;Min Pool Size=10;Connection Lifetime=180;Connection Timeout=60;Incr Pool Size8;Decr Pool Size=5;  

Data Source=urOracle;User Id=urUsername;Password=urPassword;Max Pool Size=50;Connection Timeout=60;  

Data Source=urOracle;User Id=urUsername;Password=urPassword;Pooling=False;  

Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  

Provider=OraOLEDB.Oracle;Data Source=urOracleDB;OSAuthent=1;  

Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
OLEDB.NET=True;  

Provider=OraOLEDB.Oracle;DataSource=urOracleDB;UserId=urUsername;Password=urPassword;OLEDB.NET=True;  
SPPrmsLOB=False;NDatatype=False;SPPrmsLOB=False;

Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
DistribTX=1;

Provider=OraOLEDB.Oracle;DataSource=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=  
(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort)))(CONNECT_DATA=(SID=urOracleSID)  
(SERVER=DEDICATED)));User Id=urUsername;Password=urPassword;  

Provider=OraOLEDB.Oracle;dbq=localhost:1433/XE;Database=urDataBase;User Id=urUsername;  
Password=urPassword;

Provider=OraOLEDB.Oracle;Data Source=localhost:1433/XE;Initial Catalog=urDataBase;User Id=urUsername;Password=urPassword;  

Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
CacheType=File;  

Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
FetchSize=200;

Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
ChunkSize=200;  

Driver={Oracle in OraHome92};Dbq=urTNSServiceName;Uid=urUsername;Pwd=urPassword;  

Oracle in XEClient

Standard Security

Driver=(Oracle in XEClient);dbq=192.168.1.11,1433/XE;Uid=urUsername;Pwd=urPassword;  

dotConnect for Oracle (OracleConnection)

dotConnect for Oracle, formerly known as OraDirect .NET, is an enhanced ORM enabled data provider for Oracle that builds on ADO.NET technology to present a complete solution for developing Oracle-based database applications and websites. It introduces new approaches for designing application architecture, boosts productivity, and leverages database applications.

dotConnect for Oracle can be used as a powerful ADO.NET data provider, or an effective application development framework.

Sample Code

using Devart.Data.Oracle;

OracleConnection myConnection = new OracleConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();

// execute queries, etc.

myConnection.Close();

Standard Security

User ID=urUsername;Password=urPassword;Host=ora;Pooling=true;Min Pool Size=0;Max Pool Size=100;Connection Lifetime=0;  

.NET Framework Data Provider for Oracle

.NET Framework Data Provider for Oracle is an add-on component to the .NET Framework 1.0 that provides access to an Oracle database using the Oracle Call Interface (OCI) as provided by Oracle Client software.

The .NET Framework Data Provider for Oracle, unlike the Microsoft OLE DB provider for Oracle, also supports new Oracle 9i datatypes, as well as ref cursors. This provider, System.Data.OracleClient, is similar to the .NET Framework Data Provider for SQL Server, System.Data.SqlClient.

Sample Code

using System.Data.OracleClient;  
OracleConnection myConnection = new OracleConnection();  
myConnection.ConnectionString = myConnectionString;  
myConnection.Open();  
//execute queries, etc  
myConnection.Close();  

Standard Security

Data Source=urOracleDB;Integrated Security=yes;  

Using Connection Pooling

Data Source=urOracleDB;User Id=urUsername;Password=urPassword;Min Pool Size=15;Connection Lifetime=180;Connection Timeout=60;Incr Pool Size=8;Decr Pool Size=5;  

Windows Authentication

Data Source=urOracleDB;User Id=/;  

Privileged Connection With SYSDBA

Data Source=urOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSDBA;  

Privileged Connection With SYSOPER

Data Source=urOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSOPER;  

Proxy Authentication

Data Source=urOracleDB;User Id=urUsername;Password=urPassword;Proxy User Id=pUserId;Proxy Password=pPassword;  

Specifying username and password

Data Source=UrOracleDB;User Id=urUsername;Password=urPassword;Integrated Security=no;  

Utilizing the Password Expiration functionality

Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
oConn.OpenWithNewPassword(sTheNewPassword); 

Omiting tnsnames.ora

SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort))(CONNECT_DATA=(SERVICE_NAME=urOracleSID)));uid=urUsername;pwd=urPassword;  

OR

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort))(CONNECT_DATA=  
(SERVICE_NAME=urOracleSID)));User Id=urUsername;Password=urPassword;  

.NET Framework Data Provider for OLE DB

A data provider in the .NET Framework enables you to connect to a data source in order to retrieve and modify data from the data source. A .NET Framework data provider also serves as a bridge between a data source and an ADO.NET DataSet. A .NET Framework data provider is used for connecting to a database, executing commands, and retrieving results. Those results are either processed directly, placed in a Dataset in order to be exposed to the user as needed, combined with data from multiple sources, or remoted between tiers. .NET Framework data providers are lightweight, creating a minimal layer between the data source and code, increasing performance without sacrificing functionality.

Sample Code

using System.Data.OleDb;  
OleDbConnection myConnection = new OleDbConnection();  
myConnection.ConnectionString = myConnectionString;  
myConnection.Open();  
//execute queries, etc  
myConnection.Close();  

Bridging to Oracle Provider for OLE DB

Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
OLEDB.NET=True;  

.NET Framework Data Provider for ODBC

The ODBC .NET Data Provider is an add-on component to the .NET Framework. It provides access to native ODBC drivers the same way the OLE DB .NET Data Provider provides access to native OLE DB providers.

One of the best things about working with ADO.NET data providers is all data providers define a similar class hierarchy. The only things you need to change are the classes and the connection string.

Sample Code

using System.Data.Odbc;  
OdbcConnection myConnection = new OdbcConnection();  
myConnection.ConnectionString = myConnectionString;  
myConnection.Open();  
//execute queries, etc  
myConnection.Close(); 

Bridging to Oracle in OraHome92 ODBC Driver

Driver={Oracle in OraHome92};Server=urServerAddress;Dbq=urDataBase;Uid=urUsername;  
Pwd=urPassword;  

Microsoft OLE DB Provider for Oracle

Microsoft OLE DB Provider for Oracle exposes interfaces to consumers wanting access to data on one or more Oracle servers. You can use it to develop an optimized OLE DB consumer for Oracle databases. It is designed to be used with only one Oracle client on each computer.

The Microsoft OLE DB Provider for Oracle allows distributed queries on data in Oracle databases.

Include "Provider=msdaora" in the connection string to use this provider.

Standard security

Provider=msdaora;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;   

Trusted connection

Provider=msdaora;Data Source=urOracleDB;Persist Security Info=False;Integrated Security=Yes;  

Microsoft ODBC for Oracle

The Microsoft ODBC Driver for Oracle allows you to connect your ODBC-compliant application to an Oracle database

Include "Driver={Microsoft ODBC for Oracle}" in the connection string to use this driver.

New version

Driver={Microsoft ODBC for Oracle};Server=urServerAddress;Uid=urUsername;Pwd=urPassword;  

Direct Connection

Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)  
(HOST=192.168.1.11,1433)(PORT=1233))(CONNECT_DATA=(SID=dbName)));Uid=urUsername;  
Pwd=urPassword;

OR:

Driver={Microsoft ODBC for Oracle};CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)  
(HOST=server)(PORT=5000))(CONNECT_DATA=(SERVICE_NAME=urDb)));Uid=urUsername;Pwd=urPassword q

Microsoft ODBC Driver for Oracle

Microsoft ODBC Driver for Oracle allows you to connect your ODBC-compliant application to an Oracle database. The ODBC Driver for Oracle enables an application to access data in an Oracle database through the ODBC interface. The driver can access local Oracle databases or it can communicate with the network through SQL*Net.

Include "Driver={Microsoft ODBC Driver for Oracle}" in the connection string to use this driver.

Old version

Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=urUsername;  
Pwd=urPassword;  

MSDataShape

MSDataShape is used to create hierarchical Recordsets, so that we can be able to browse relational data in a convenient way.

Include "Provider=MSDataShape;Data Provider=providername" in the connection string to use this wrapper COM component.

MSDataShape

Provider=MSDataShape;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=urUsername;Password=urPassword;  

Next

Here is a detailed tutorial on Working with Oracle Data Provider in .NET