Microsoft.NET

……………………………………………….Expertise in .NET Technologies

Using Oracle Data Provider for .NET

Posted by Ravi Varma Thumati on October 9, 2009

This article covers how to connect to an Oracle database using the Oracle Data Provider for .NET (ODP.NET) and how to control parameters that manage ODP.NET database connection pooling.

Article Contents:

  • Introduction
  • Code Samples
  • ODP.NET Assembly
  • ODP.Net Connection String Attributes

Introduction

Objective:

Connect to an Oracle database using Oracle Data Provider for .NET (ODP.NET) and control parameters that manage ODP.NET database connection pooling.

Prerequisite:

I assume you are familiar with Microsoft Visual Studio .NET, and also have an basic understanding of ODP.NET and databases.

Introduction:

ODP.NET, which is included in the Oracle.DataAccesss.dll assembly, provides a robust collection of classes that assist in easy database interaction. It uses Oracle’s native APIs to offer fast and reliable access to Oracle data and features from any .NET application.

In this tutorial, I will discuss how to use the OracleConnection class, provided by ODP.NET, then to establish a connection to an Oracle database and interact with the database. I will also show the usage of a small code fragment that demonstrates how to connect to an Oracle database using ODP.NET.

Connection pooling is enabled by default in ODP.NET. I will also explain how you can control the connection pooling parameters provided by ODP.NET.

Requirements:

  • Oracle Data Provider for .NET (ODP.NET)
  • Oracle9i Database or later running SQL*Net TCP/IP listener.
  • Your choice of languages either C# or VB.NET, and Microsoft Visual Studio .NET

Description:

When you install ODP.NET, the Oracle Universal Installer automatically registers ODP.NET with the Global Assembly Cache (GAC).

The most important class with respect to this tutorial is the OracleConnection class. An OracleConnection object represents a connection to an Oracle database. In this tutorial, I will display how to connect to an Oracle database and list names of all the employees and their employee number present in a table.
 
Now that I have discussed the objective, prerequisite, introduction, requirements, and description it is time to move on to the code.

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

ODP.NET Assembly

Oracle.DataAccess.dll assembly provides two namespaces:

  • The Oracle.DataAccess.Client namespace contains ODP.NET classes and enumerations.
  • The Oracle.DataAccess.Types namespace contains the Oracle Data Provider for .NET Types (ODP.NET Types).

Oracle.DataAccess.Client Classes

  1. OracleCommand – An OracleCommand object represents a SQL command, a stored procedure, or a table name
  2. OracleCommandBuilder – An OracleCommandBuilder object provides automatic SQL generation for the OracleDataAdapter when updates are made to the database
  3. OracleConnection – An OracleConnection object represents a connection to an Oracle database
  4. OracleDataAdapter – An OracleDataAdapter object represents a data provider object that communicates with the DataSet
  5. OracleDataReader – An OracleDataReader object represents a forward-only, read-only, in-memory result set
  6. OracleError – The OracleError object represents an error reported by an Oracle database
  7. OracleErrorCollection – An OracleErrorCollection object represents a collection of OracleErrors
  8. OracleException – The OracleException object represents an exception that is thrown when Oracle Data Provider for .NET encounters an error
  9. OracleFailoverEventArgs – The OracleFailoverEventArgs object provides event data for the OracleConnection.Failover event

10.  OracleFailoverEventHandler delegate – The OracleFailoverEventHandler delegate represents the signature of the method that handles the OracleConnection.Failover event

11.  OracleGlobalization – The OracleGlobalization class is used to obtain and set the Oracle globalization settings of the session, thread, and local computer (read-only)

12.  OracleInfoMessageEventHandler delegate – The OracleInfoMessageEventHandler delegate represents the signature of the method that handles the OracleConnection.InfoMessage event

13.  OracleInfoMessageEventArgs – The OracleInfoMessageEventArgs object provides event data for the OracleConnection.InfoMessage event

14.  OracleParameter – An OracleParameter object represents a parameter for an OracleCommand

15.  OracleParameterCollection – An OracleParameterCollection object represents a collection of OracleParameters

16.  OracleRowUpdatedEventArgs – The OracleRowUpdatedEventArgs object provides event data for the OracleDataAdapter.RowUpdated event

17.  OracleRowUpdatedEventHandler – The oracleRowUpdatedEventHandler delegate represents the signature of the method that handles the OracleDataAdapter.RowUpdated event

18.  OracleRowUpdatingEventArgs – The OracleRowUpdatingEventArgs object provides event data for the OracleDataAdapter.RowUpdating event

19.  OracleRowUpdatingEventHandler – The OracleRowUpdatingEventHandler delegate represents the signature of the method that handles the OracleDataAdapter.RowUpdating event

20.  OracleTransaction – An OracleTransaction object represents a local transaction

21.  OracleXmlQueryProperties – An OracleXmlQueryProperties object represents the XML properties used by the OracleCommand class when the XmlCommandType property is Query

22.  OracleXmlSaveProperties – An OracleXmlSaveProperties object represents the XML properties used by the OracleCommand class when the XmlCommandType property is Insert, Update, or Delete

Oracle.DataAccess.Client Enumerations  

  1. FailoverEvent – FailoverEvent enumerated values are used to explicitly specify the state of the failover
  2. FailoverReturnCode – FailoverReturnCode enumerated values are passed back by the application to the ODP.NET provider to request a retry in case of a failover error or to continue in case of a successful failover
  3. FailoverType – FailoverType enumerated values are used to indicate the type of failover event that was raised
  4. OracleDbType – OracleDbType enumerated values are used to explicitly specify the OracleDbType of an OracleParameter
  5. OracleParameterStatus – The OracleParameterStatus enumeration type indicates whether a NULL value is fetched from a column, whether truncation has occurred during the fetch, or whether a NULL value is to be inserted into a database column
  6. OracleXmlCommandType – The OracleXmlCommandType enumeration specifies the values that are allowed for the OracleXmlCommandType property of the OracleCommand class

Oracle.DataAccess.Types Structures

  1. OracleBinary – The OracleBinary structure represents a variable-length stream of binary data
  2. OracleDate – The OracleDate structure represents the Oracle DATE datatype
  3. OracleDecimal – The OracleDecimal structure represents an Oracle NUMBER in the database or any Oracle numeric value
  4. OracleIntervalDS – The OracleIntervalDS structure represents the Oracle INTERVAL DAY TO SECOND datatype
  5. OracleIntervalYM – The OracleIntervalYM structure represents the Oracle INTERVAL YEAR TO MONTH datatype
  6. OracleString – The OracleString structure represents a variable-length stream of characters
  7. OracleTimeStamp – The OracleTimeStamp structure represents the Oracle TimeStamp datatype
  8. OracleTimeStampLTZ – The OracleTimeStampLTZ structure represents the Oracle TIMESTAMP WITH LOCAL TIME ZONE data type
  9. OracleTimeStampTZ – The OracleTimeStampTZ structure represents the Oracle TIMESTAMP WITH TIME ZONE data type

Oracle.DataAccess.Types Exceptions

  1. OracleTypeException – The OracleTypeException object is the base exception class for handling exceptions that occur in the ODP.NET Type classes
  2. OracleNullValueException – The OracleNullValueException represents an exception that is thrown when trying to access an ODP.NET Type structure that is null
  3. OracleTruncateException – The OracleTruncateException class represents an exception that is thrown when truncation in an ODP.NET Type class occurs

Oracle.DataAccess.Types Classes

  1. OracleBFile – An OracleBFile is an object that has a reference to BFILE data. It provides methods for performing operations on BFiles
  2. OracleBlob – An OracleBlob object is an object that has a reference to BLOB data. It provides methods for performing operations on BLOBs
  3. OracleClob – An OracleClob is an object that has a reference to CLOB data. It provides methods for performing operations on CLOBs
  4. OracleRefCursor – An OracleRefCursor object represents an Oracle REF CURSOR
  5. OracleXmlStream – An OracleXmlStream object represents a sequential read-only stream of XML data stored in an OracleXmlType object
  6. OracleXmlType – An OracleXmlType object represents an Oracle XmlType instance

Okay, now we will move on to connection pooling as I briefly wrote of earlier.

ODP.Net Connection String Attributes

Connection String Attributes

Attribute – Connection Lifetime
Default value – 0
Description – Maximum life time (in seconds) of the connection

Attribute – Connection Timeout
Default value – 15
Description – Maximum time (in seconds) to wait for a free connection from the pool

Attribute – Data Source
Default value – empty string
Description – Oracle Net Service Name that identifies the database to connect to

Attribute – DBA Privilege
Default value – empty string
Description – Administrative privileges: SYSDBA or SYSOPER

Attribute -Decr Pool Size
Default value – 1
Description – Controls the number of connections that are closed when an excessive amount of established connections are unused

Attribute – Enlist
Default value – true
Description – Enables or disables serviced components to automatically enlist in distributed transactions

Attribute – Incr Pool Size
Default value – 5
Description – Controls the number of connections that are established when all the connections in the pool are used

Attribute – Max Pool Size
Default value – 100
Description – Maximum number of connections in a pool

Attribute – Min Pool Size
Default value – 1
Description – Minimum number of connections in a pool

Attribute – Password
Default value – empty string
Description – Password for the user specified by User Id

Attribute- Persist Security Info
Default value – false
Description – Enables or disables the retrieval of password in the connection string

Attribute – Pooling
Default value – true
Description – Enables or disables connection pooling

Attribute – Proxy User Id
Default value – empty string
Description – User name of the proxy user

Attribute – Proxy Password
Default value – empty string
Description – Password of the proxy user
User Id   empty string Oracle user name

Now we finally get to a Pooling Example.

OracleConnection con = new OracleConnection();
con.ConnectionString = "User;Password=tiger;Data Source=oracle;" +
"Min Pool Size=10;Connection Lifetime=120;Connection Timeout=60;" +
"Incr Pool Size=5; Decr Pool Size=2";
con.Open();

 

Advertisements

2 Responses to “Using Oracle Data Provider for .NET”

  1. Polprav said

    Hello from Russia!
    Can I quote a post in your blog with the link to you?

  2. I thus enjoyed every part of this website and I’ve bookmarked your blog to maintain the new topics you will publish in the future.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: