Using Oracle Data Provider for .NET
page 2 of 4
by Steven Swafford
Feedback
Average Rating: 
Views (Total / Last 10 Days): 63115/ 118

Code Samples

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 .NET
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;";

 
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 .NET
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(); 
}

 
Visual Basic .NET
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.


View Entire Article

User Comments

Title: ODP for .NET   
Name: Adam Woods
Date: 2005-01-28 6:44:06 AM
Comment:
Very good example and well narrated, been looking for a clear C# focused example for some time now, my search is over. Thanks






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2021 ASPAlliance.com  |  Page Processed at 2021-11-29 5:56:17 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search