How to Access an Oracle Database in Code

PROBLEM

I would like to access an Oracle database from a coded step, and not with the data binding feature.

SOLUTION

Note: You can access an Oracle database in a data driven test, as seen here. This is built-in functionality for Test Studio, but not for Telerik Testing Framework. This article demonstrates how to access an Oracle database through code, which allows for greater flexibility.

Install Oracle Client

Ensure the full Oracle Client is installed on your machine. The Instant Oracle Client is not enough; you need the full client. If you can run SQL*Plus from the Windows Start Menu, then you have the full client installed. Your Database Administrators can help with that installation.

Add Assembly Reference

Use the System.Data.OracleClient API. You'll need to add an assembly reference to it, as seen here. This assembly is located, by default, in the following location (on a 64-bit Windows 7 machine with .NET 4.0):

  • C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Data.OracleClient.dll

 

Ensure you add the using or Imports statement to the top of the code-behind file. Click the View Class button, scroll to the top of the code, and add this line:

 

C#
using System.Data.OracleClient;

 

Visual Basic
Imports System.Data.OracleClient

 

Data Connection String

The coded step requires an Oracle connection string to identify the database. Here is an example:

 

User Id=user;Password=pass;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostname) (PORT = 7115))) (CONNECT_DATA = (SID = dbname)));

 

You may need to shorten it because Test Studio only allows 128 characters in the connection string. You may be able to derive your own connection string using the tnsping command. From a command prompt, enter the following, where dbname matches the name of your database:

 

tnsping dbname

 

This command should produce a connection string. T add the User ID and Password until your connection string looks like the one above.

Sample Code

This code runs a simple query against one of the database tables:

 

C#
string connectionString = "User Id=user;Password=pass;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostname) (PORT = 7115))) (CONNECT_DATA = (SID = dbname)));";
using (OracleConnection connection = new OracleConnection())
{
    connection.ConnectionString = connectionString;
    connection.Open();
 
    Console.WriteLine("State: {0}", connection.State);
    Console.WriteLine("ConnectionString: {0}", connection.ConnectionString);     
 
    connection.CreateCommand();
    OracleCommand command = connection.CreateCommand();
 
    string sql = "SELECT count(*) cntpob, max(pob_id) maxpob, min(pob_id) minpob FROM pobs";
    command.CommandText = sql;
    OracleDataReader reader = (command).ExecuteReader();
 
    while (reader.Read())
    {
        string cntpob = reader["cntpob"].ToString();
        string maxpob = reader["maxpob"].ToString();
        string minpob = reader["minpob"].ToString();
        Log.WriteLine("Count: " + cntpob);
        Log.WriteLine("Max: " + maxpob);
        Log.WriteLine("Min: " + minpob);
    }          
    reader.Close();
    connection.Close();
}

 

Visual Basic
Dim connectionString As String = "User Id=user;Password=pass;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = hostname) (PORT = 7115))) (CONNECT_DATA = (SID = dbname)));"
Using connection As New OracleConnection()
    connection.ConnectionString = connectionString
    connection.Open()
 
    Console.WriteLine("State: {0}", connection.State)
    Console.WriteLine("ConnectionString: {0}", connection.ConnectionString)
 
    connection.CreateCommand()
    Dim command As OracleCommand = connection.CreateCommand()
 
    Dim sql As String = "SELECT count(*) cntpob, max(pob_id) maxpob, min(pob_id) minpob FROM pobs"
    command.CommandText = sql
    Dim reader As OracleDataReader = (command).ExecuteReader()
 
    While reader.Read()
        Dim cntpob As String = reader("cntpob").ToString()
        Dim maxpob As String = reader("maxpob").ToString()
        Dim minpob As String = reader("minpob").ToString()
        Log.WriteLine("Count: " + cntpob)
        Log.WriteLine("Max: " + maxpob)
        Log.WriteLine("Min: " + minpob)
    End While
    reader.Close()
    connection.Close()
End Using

 

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