Oracle is a powerful relational database management system that offers a large
feature set. Along with Microsoft SQL Server, Oracle is widely regarded as one
of the two most popular full-featured database systems on the market today.
Oracle is made up of a set of processes running in your operating system. These
processes manage how data is stored and how it is accessed. It is a program that
is running in the background, maintaining your data for you and figuring out
where it should go on your hard drive.
Providers for Oracle
- Oracle Data Provider for .NET / ODP.NET (OracleConnection)
- Oracle Provider for OLE DB
- Oracle in OraHome92
- Oracle in XEClient
- dotConnect for Oracle (OracleConnection)
- .NET Framework Data Provider for Oracle (OracleConnection)
- .NET Framework Data Provider for OLE DB (OleDbConnection)
- .NET Framework Data Provider for ODBC (OdbcConnection)
- Microsoft OLE DB Provider for Oracle
- Microsoft ODBC Driver for Oracle
- Microsoft ODBC for Oracle
- MSDataShape
Oracle Data Provider for .NET / ODP.NET
Oracle Data Provider for .NET (ODP.NET) is an implementation of a .NET data
provider for Oracle Database. It uses Oracle native APIs to offer fast and
reliable access to Oracle data and features from any .NET application. Oracle
Data Provider for .NET (ODP.NET) features optimized ADO.NET data access to the
Oracle database. ODP.NET allows developers to take advantage of advanced Oracle
database functionality, including Real Application Clusters, XML DB, and
advanced security.
Sample Code
using Oracle.DataAccess.Client;
OracleConnection myConnection = new OracleConnection();
myConnection.ConnectionString = myConnectionString;
myConnection.Open();
//execute queries
myConnection.Close();
TNS
Syntax:
Data Source=TORCL;User
Id=urUsername;Password=urPassword;
Integrated Security
Syntax:
Data Source=TORCL;Integrated
Security=SSPI;
Privileged
Connections
Syntax:
Data Source=urOracle;User
Id=urUsername;Password=urPassword;DBA
Privilege=SYSDBA;
Runtime
Connection Load Balancing
Syntax:
Data Source=urOracle;User
Id=urUsername;Password=urPassword;Load
Balancing=True;
Connect
Naming Method to connect to an Instance
Syntax:
Data Source=username/password@urserver//instancename;
Connect
Naming Method for connecting to a dedicated server instance
Syntax:
Data Source=username/password@urserver/urservice:dedicated/instancename;
Applying
ODP.NET without tnsnames.ora
Syntax:
Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort)))
(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=urOracleSID)));User
Id=urUsername;
Password=urPassword;
Applying the
Easy Connect Naming Method (aka EZ Connect)
Syntax:
Data Source=username/password@//urserver:1433/ur.service.com;
Windows user
authentication
Syntax:
Data Source=urOracle;User
Id=/;
Specification of Pooling parameters
Syntax:
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;
Restricting
Pool size
Syntax:
Data Source=urOracle;User
Id=urUsername;Password=urPassword;Max
Pool Size=50;Connection
Timeout=60;
Disable
Pooling
Syntax:
Data Source=urOracle;User
Id=urUsername;Password=urPassword;Pooling=False;
Oracle Provider for OLE DB
The OLE DB Provider for Oracle supports a simple OLE DB architecture by
providing access to data stored in Oracle as well as limited access to Oracle8
databases.
OLE DB Provider is an open standard data access methodology which utilizes a set
of Component Object Model (COM) interfaces for accessing and manipulating
different types of data. OLE DB data providers are a set of COM components that
transfer data from a data source to a consumer. An OLE DB Provider places that
data in a tabular format in response to calls from a consumer. Providers can be
simple or complex. The provider may return a table, it may allow the consumer to
determine the format of that table, and it may perform operations on the data.
Code
Include "Provider=OraOLEDB.Oracle" in the connection string to use this
provider.
Standard
Security
Syntax:
Provider=OraOLEDB.Oracle;Data
Source=urOracleDB;User
Id=urUsername;Password=urPassword;
Trusted
Connection
Syntax:
Provider=OraOLEDB.Oracle;Data
Source=urOracleDB;OSAuthent=1;
Microsofts OLE DB .NET Data Provider
Syntax:
Provider=OraOLEDB.Oracle;Data
Source=urOracleDB;User
Id=urUsername;Password=urPassword;
OLEDB.NET=True;
OraOLEDB Custom Properties with Microsofts OLE DB .NET Data Provider
Syntax:
Provider=OraOLEDB.Oracle;DataSource=urOracleDB;UserId=urUsername;Password=urPassword;OLEDB.NET=True;SPPrmsLOB=False;NDatatype=False;SPPrmsLOB=False;
Using distributed transactions
Syntax:
Provider=OraOLEDB.Oracle;Data
Source=urOracleDB;User
Id=urUsername;Password=urPassword;
DistribTX=1;
TNS-less connection string
Syntax:
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;
Oracle XE,
VB6 ADO
Syntax:
Provider=OraOLEDB.Oracle;dbq=localhost:1433/XE;Database=urDataBase;User
Id=urUsername;
Password=urPassword;
Oracle XE, C++ ADO
Syntax:
Provider=OraOLEDB.Oracle;Data
Source=localhost:1433/XE;Initial
Catalog=urDataBase;User
Id=urUsername;Password=urPassword;
Controling rowset cache mechanism
Syntax:
Provider=OraOLEDB.Oracle;Data
Source=urOracleDB;User
Id=urUsername;Password=urPassword;
CacheType=File;
Controling the fetchsize
Syntax:
Provider=OraOLEDB.Oracle;Data
Source=urOracleDB;User
Id=urUsername;Password=urPassword;
FetchSize=200;
Controling the chunksize
Syntax:
Provider=OraOLEDB.Oracle;Data
Source=urOracleDB;User
Id=urUsername;Password=urPassword;
ChunkSize=200;
Oracle in OraHome92 Driver
Standard Security
Syntax:
Driver={Oracle
in OraHome92};Dbq=urTNSServiceName;Uid=urUsername;Pwd=urPassword;
Oracle in XEClient
Standard Security
Syntax:
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
Syntax:
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
Syntax:
Data Source=urOracleDB;Integrated
Security=yes;
Using Connection Pooling
Syntax:
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
Syntax:
Data Source=urOracleDB;User
Id=/;
Privileged Connection With SYSDBA
Syntax:
Data Source=urOracleDB;User
Id=SYS;Password=SYS;DBA
Privilege=SYSDBA;
Privileged
Connection With SYSOPER
Syntax:
Data Source=urOracleDB;User
Id=SYS;Password=SYS;DBA
Privilege=SYSOPER;
Proxy Authentication
Syntax:
Data Source=urOracleDB;User
Id=urUsername;Password=urPassword;Proxy
User Id=pUserId;Proxy
Password=pPassword;
Specifying username and password
Syntax:
Data Source=UrOracleDB;User
Id=urUsername;Password=urPassword;Integrated
Security=no;
Utilizing the Password Expiration functionality
Syntax:
Data Source=urOracleDB;User
Id=urUsername;Password=urPassword;
oConn.OpenWithNewPassword(sTheNewPassword);
Omiting
tnsnames.ora
Syntax:
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
Syntax:
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 the 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
Syntax:
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.
Code
Include "Provider=msdaora" in the connection string to use this provider.
Standard security
Syntax:
Provider=msdaora;Data
Source=urOracleDB;User
Id=urUsername;Password=urPassword;
Trusted connection
Syntax:
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.
Code
Include "Driver={Microsoft ODBC for Oracle}" in the connection string to use
this driver.
New version
Syntax:
Driver={Microsoft
ODBC for Oracle};Server=urServerAddress;Uid=urUsername;Pwd=urPassword;
Direct Connection
Syntax:
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
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.
Code
Include "Driver={Microsoft ODBC Driver for Oracle}" in the connection string to
use this driver.
Old version
Syntax:
Driver={Microsoft
ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=urUsername;
Pwd=urPassword;
MSDataShape
MSDataShape
is used to create hierarchial Recordsets, so that we can be able to browse
relational data in a convenient way.
Code
Include "Provider=MSDataShape;Data Provider=providername" in the
connection string to use this wrapper COM component.
MSDataShape
Syntax:
Provider=MSDataShape;Persist
Security Info=False;Data
Provider=MSDAORA;Data
Source=orac;User
Id=urUsername;Password=urPassword;