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;
Visual Basic .NETImports 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;";
Visual Basic .NET
' 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;
Visual Basic .NETDim 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();
}
Visual Basic .NETTry
' 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.