Users Online

· Guests Online: 45

· Members Online: 0

· Total Members: 188
· Newest Member: meenachowdary055

Forum Threads

Newest Threads
No Threads created
Hottest Threads
No Threads created

Latest Articles

· Do while loop
· The for loop in C
· C while loop
· Pointers in C
· Functions in C

Articles Hierarchy

Articles Home » DOTNET » Oracle Connection String in C#

Oracle Connection String in C#

Oracle Connection String in C#

Introduction 

 
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. In this article and code example, we will see how to connect and access Oracle database from a .NET application using .NET Oracle Data Provider and other data providers. Using various data providers, you can create an ADO.NET connection string that is used to connect and access Oracle database in C#. 
 
You can access Oracle from a C#/.NET application using various data providers. Here is a list of various Oracle data providers: 
  1. Oracle Data Provider for .NET / ODP.NET (OracleConnection)
  2. Oracle Provider for OLE DB
  3. Oracle in OraHome92
  4. Oracle in XEClient
  5. dotConnect for Oracle (OracleConnection)
  6. .NET Framework Data Provider for Oracle (OracleConnection)
  7. .NET Framework Data Provider for OLE DB (OleDbConnection)
  8. .NET Framework Data Provider for ODBC (OdbcConnection)
  9. Microsoft OLE DB Provider for Oracle
  10. Microsoft ODBC Driver for Oracle
  11. Microsoft ODBC for Oracle
  12. 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
  1. using Oracle.DataAccess.Client;  
  2. OracleConnection myConnection = new OracleConnection();  
  3. myConnection.ConnectionString = myConnectionString;  
  4. myConnection.Open();  
  5. //execute queries   
  6. myConnection.Close();  
TNS 
  1. Data Source=TORCL;User Id=urUsername;Password=urPassword;  
> Integrated Security
  1. Data Source=TORCL;Integrated Security=SSPI;  
Privileged Connections
  1. Data Source=urOracle;User Id=urUsername;Password=urPassword;DBA Privilege=SYSDBA;  
Runtime Connection Load Balancing
  1. Data Source=urOracle;User Id=urUsername;Password=urPassword;Load Balancing=True;  
Connect Naming Method to connect to an Instance
  1. Data Source=username/password@urserver//instancename;  
Connect Naming Method for connecting to a dedicated server instance
  1. Data Source=username/password@urserver/urservice:dedicated/instancename;  
Applying ODP.NET without tnsnames.ora
  1. Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort)))  
  2. (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=urOracleSID)));User Id=urUsername;  
  3. Password=urPassword;  
Applying the Easy Connect Naming Method (aka EZ Connect)
  1. Data Source=username/password@//urserver:1433/ur.service.com;  
Windows user authentication
  1. Data Source=urOracle;User Id=/;  
Specification of Pooling parameters
  1. 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
  1. Data Source=urOracle;User Id=urUsername;Password=urPassword;Max Pool Size=50;Connection Timeout=60;  
Disable Pooling
  1. 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 that 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
  1. Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
Trusted Connection
  1. Provider=OraOLEDB.Oracle;Data Source=urOracleDB;OSAuthent=1;  
> Microsofts OLE DB .NET Data Provider
  1. Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
  2. OLEDB.NET=True;  
> OraOLEDB Custom Properties with Microsofts OLE DB .NET Data Provider
  1. Provider=OraOLEDB.Oracle;DataSource=urOracleDB;UserId=urUsername;Password=urPassword;OLEDB.NET=True;  
  2. SPPrmsLOB=False;NDatatype=False;SPPrmsLOB=False;  
> Using distributed transactions
  1. Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
  2. DistribTX=1;  
> TNS-less connection string
  1. Provider=OraOLEDB.Oracle;DataSource=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=  
  2. (PROTOCOL=TCP)(HOST=urHost)(PORT=urPort)))(CONNECT_DATA=(SID=urOracleSID)  
  3. (SERVER=DEDICATED)));User Id=urUsername;Password=urPassword;  
Oracle XE, VB6 ADO
  1. Provider=OraOLEDB.Oracle;dbq=localhost:1433/XE;Database=urDataBase;User Id=urUsername;  
  2. Password=urPassword;  
> Oracle XE, C++ ADO
  1. Provider=OraOLEDB.Oracle;Data Source=localhost:1433/XE;Initial Catalog=urDataBase;User Id=urUsername;Password=urPassword;  
> Controling rowset cache mechanism
  1. Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
  2. CacheType=File;  
> Controling the fetchsize
  1. Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
  2. FetchSize=200;  
> Controling the chunksize
  1. Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
  2. ChunkSize=200;  

Oracle in OraHome92 Driver

> Standard Security
  1. Driver={Oracle in OraHome92};Dbq=urTNSServiceName;Uid=urUsername;Pwd=urPassword;  
Oracle in XEClient
 
> Standard Security
  1. 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
  1. using Devart.Data.Oracle;  
  2. OracleConnection myConnection = new OracleConnection();  
  3. myConnection.ConnectionString = myConnectionString;  
  4. myConnection.Open();  
  5. //execute queries, etc  
  6. myConnection.Close();  
> Standard Security 
  1. 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
  1. using System.Data.OracleClient;  
  2. OracleConnection myConnection = new OracleConnection();  
  3. myConnection.ConnectionString = myConnectionString;  
  4. myConnection.Open();  
  5. //execute queries, etc  
  6. myConnection.Close();  
> Standard Security 
  1. Data Source=urOracleDB;Integrated Security=yes;  
> Using Connection Pooling
  1. 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
  1. Data Source=urOracleDB;User Id=/;  
> Privileged Connection With SYSDBA
  1. Data Source=urOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSDBA;  
Privileged Connection With SYSOPER 
  1. Data Source=urOracleDB;User Id=SYS;Password=SYS;DBA Privilege=SYSOPER;  
> Proxy Authentication
  1. Data Source=urOracleDB;User Id=urUsername;Password=urPassword;Proxy User Id=pUserId;Proxy Password=pPassword;  
> Specifying username and password
  1. Data Source=UrOracleDB;User Id=urUsername;Password=urPassword;Integrated Security=no;  
> Utilizing the Password Expiration functionality
  1. Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
  2. oConn.OpenWithNewPassword(sTheNewPassword);  
 >Omiting tnsnames.ora
  1. SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort))(CONNECT_DATA=(SERVICE_NAME=urOracleSID)));uid=urUsername;pwd=urPassword;  
OR:
  1. Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=urHost)(PORT=urPort))(CONNECT_DATA=  
  2. (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
  1. using System.Data.OleDb;  
  2. OleDbConnection myConnection = new OleDbConnection();  
  3. myConnection.ConnectionString = myConnectionString;  
  4. myConnection.Open();  
  5. //execute queries, etc  
  6. myConnection.Close();  
> Bridging to Oracle Provider for OLE DB 
  1. Provider=OraOLEDB.Oracle;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;  
  2. 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
  1. using System.Data.Odbc;  
  2. OdbcConnection myConnection = new OdbcConnection();  
  3. myConnection.ConnectionString = myConnectionString;  
  4. myConnection.Open();  
  5. //execute queries, etc  
  6. myConnection.Close();  
> Bridging to Oracle in OraHome92 ODBC Driver
  1. Driver={Oracle in OraHome92};Server=urServerAddress;Dbq=urDataBase;Uid=urUsername;  
  2. 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 
  1. Provider=msdaora;Data Source=urOracleDB;User Id=urUsername;Password=urPassword;   
> Trusted connection
  1. 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 
  1. Driver={Microsoft ODBC for Oracle};Server=urServerAddress;Uid=urUsername;Pwd=urPassword;  
> Direct Connection
  1. Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)  
  2. (HOST=192.168.1.11,1433)(PORT=1233))(CONNECT_DATA=(SID=dbName)));Uid=urUsername;  
  3. Pwd=urPassword;  
OR:
  1. Driver={Microsoft ODBC for Oracle};CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)  
  2. (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 
  1. Driver={Microsoft ODBC Driver for Oracle};ConnectString=OracleServer.world;Uid=urUsername;  
  2. Pwd=urPassword;  
MSDataShape
 
MSDataShape is used to create hierarchical 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
  1. Provider=MSDataShape;Persist Security Info=False;Data Provider=MSDAORA;Data Source=orac;User Id=urUsername;Password=urPassword;  
 

 

 

· Posted by Superadmin on March 21 2022 · 0 Comments · 246 Reads · Print
Page 1 of 2: 12

Comments

No Comments have been Posted.

Post Comment

Please Login to Post a Comment.

Ratings

Rating is available to Members only.

Please login or register to vote.
No Ratings have been Posted.
Render time: 0.78 seconds
10,841,719 unique visits