How to Access an Oracle Database in Code

Oracle database could be used as data source in a data driven test as described here. This is a built-in functionality for Test Studio Dev but if you need to establish the connection in code the connection and interaction with the database need to be established step by step. This article demonstrates how to access an Oracle database through code.

Install Oracle Client

To access the Oracle database in C# it is necessary to install Oracle Data Access Components. That driver package contains the necessary libraries to refer in the project.

Add an Assembly Reference

Once the ODAC is installed the following dll have to be referred in the project:

Oracle.DataAccess.dll

For default installation the dll file could be found in the Oracle data base installation folder:

C:\Oracle\DB\app\oracle\product\11.2.0\server\odp.net\bin\4

Include the following usings in the code.

using Oracle.DataAccess.Client; 
using Oracle.DataAccess.Types; 
import Oracle.DataAccess.Client 
import Oracle.DataAccess.Types 

Data Connection String

An Oracle connection string is required to identify the database to connect to. Here is an example:

Data Source=XE;User Id=SYSTEM;Password=pass;

Different connection strings could be used. See here for more examples.

Sample Code

This code runs a simple query against a database table similar to this example:

string oradb = "Data Source=XE;User Id=SYSTEM;Password=pass;"; 
 
OracleConnection conn = new OracleConnection(oradb); 
 
conn.Open(); 
 
OracleCommand cmd = new OracleCommand  
    { 
    Connection = conn, 
    CommandType = System.Data.CommandType.Text 
    }; 
 
// SQL query: 
cmd.CommandText = "select Name from Employees where ID=2" ; 
 
OracleDataReader dr = cmd.ExecuteReader(); 
 
dr.Read(); 
 
// Print the datavalue in the execution log 
Log.WriteLine(dr.GetString(0).ToString()); 
 
conn.Dispose(); 

Note: This code won't run without modification. The database, table, and column in the original code won't exist in your environment unless you create the same database.

For additional help on using Oracle database please visit the Oracle official help page.

In this article
Not finding the help you need? Improve this article