Sample Code:

Include Required Namespaces: It is advisable to add references of the namespaces in the 'general declarations' section of the .cs or .vb file, to avoid qualifying their usage later in the code:

C#

using System;
using System.Data;
using Oracle.DataAccess.Client;

Imports System
Imports System.Data
Imports Oracle.DataAccess.Client

1. Set the any connection parameters such as the User Id, Password and Data Source:

C#

// Create the connection object
OracleConnection con = new OracleConnection();
  
// Specify the connect string
// NOTE: Modify User Id, Password, Data Source as per your database set up
con.ConnectionString = "User Id=userid;Password=password;Data Source=dbinstance;";

' Create the connection object
Dim con As OracleConnection = New OracleConnection()


' Specify the connect string
' NOTE: Modify User Id, Password, Data Source as per your database set up
con.ConnectionString = "User Id=userid;Password=password;Data Source=dbinstance;"

2. Now open the database connection through ODP.NET:

C#

try
{
  // Open the connection
  con.Open();
  Console.WriteLine("Connection to Oracle database established!");
  Console.WriteLine(" ");
} 
catch (Exception ex)
{
 Console.WriteLine(ex.Message);
}

Visual Basic .NET

Try


  ' Open the connection
  con.Open()
  Console.WriteLine("Connection to Oracle database established!")
  Console.WriteLine(" ")


Catch ex As Exception


  Console.WriteLine(ex.Message)
End Try

3. Create a command object to perform a query against the database:

C#

string cmdQuery = "SELECT empno, ename FROM emptab";
     
// Create the OracleCommand object
OracleCommand cmd = new OracleCommand(cmdQuery);
cmd.Connection = con;
cmd.CommandType = CommandType.Text;

Dim cmdQuery As String = "SELECT empno, ename FROM emptab"


' Create the OracleCommand object
Dim cmd As OracleCommand = New OracleCommand(cmdQuery)
cmd.Connection = con
cmd.CommandType = CommandType.Text

4. Obtain the data result and place this result into an OracleDataReader object and then display the data via the console. Then, simply close the connection object.

C#

try
{
  // Execute command, create OracleDataReader object
  OracleDataReader reader = cmd.ExecuteReader();
  while (reader.Read())
  {
    // Output Employee Name and Number
    Console.WriteLine("Employee Number: " + 
                    reader.GetDecimal(0) + 
                                    " , " +
                       "Employee Name : " +


                      reader.GetString(1));
  }
}
catch (Exception ex) 
{
  Console.WriteLine(ex.Message);
} 
finally
{
  // Dispose OracleCommand object
  cmd.Dispose();


  // Close and Dispose OracleConnection object
  con.Close();
  con.Dispose(); 
}

Try


  ' Execute command, create OracleDataReader object
  Dim reader As OracleDataReader = cmd.ExecuteReader()
  While (reader.Read())


    ' Output Employee Name and Number
    Console.WriteLine("Employee Number : " & _
                      reader.GetDecimal(0) & _
                                     " , " & _
                        "Employee Name : " & _
    reader.GetString(1))


  End While
Catch ex As Exception


  Console.WriteLine(ex.Message)


Finally


  ' Dispose OracleCommand object
  cmd.Dispose()


  ' Close and Dispose OracleConnection object
  con.Close()
  con.Dispose()


End Try

Now to dig in even deeper into ODP.Net let us now look at the assemblies in greater detail.