Post
Ask Question

Accessing Oracle Database from Microsoft.NET 2.0 using Oracle Data Provider for .NET

Introduction

 
This article is intended to show how to access the Oracle database using the ADO.NET programming model and Oracle Data Provider for .NET (ODP.NET) that Oracle makes available.
 
We discuss a common business scenario where we have an Oracle database as a backend server and a Windows client application querying this database system. Oracle is one of the leading database vendors and the ADO.NET model provides an interface to develop applications regardless of the underlying data source.  ODP.NET is the data provider supported by Oracle and implements several Oracle database's specific features. Although, Microsoft.NET framework ships with an Oracle database's ADO.NET provider and both of the providers will satisfy the needs of most applications, and in this article, I will focus on the ODP.NET provider.
 

ODP.NET

 
It is Oracle's ADO.NET 2.0 provider, and implements all the requirements and adds several specific features such as statement caching which eliminates the need to recompile each SQL statement before the execution as well as supports Change Notification one of the features of Oracle Database 10g. It is available for free downloading on the Oracle Technology Network website. After installation, a toolset is integrated into Visual Studio.NET (VS.NET) which allows interacting with the Oracle database and access to the database designer capabilities without leaving VS.NET.
 
The object model of ODP.NET provides a rich collection of classes that assist in easy database interaction and the objects are part of the Oracle.DataAccessClient namespace hosted in the assembly Oracle.DataAccess.dll. When you install ODP.NET, the Oracle Universal Installer registers this assembly with the Global Assembly Cache. You can browse objects in any Oracle database through the Oracle Explorer windows available from the View menu in the Visual Studio main menu. You can access any Oracle database's specific features from this window and allows dragging and dropping schema objects and the generation of automatic code for you.
 
Getting started.
 
Now we're going to create the Windows client application performing the following steps.
  1. In Visual Studio.NET go to the main menu and select File|New|Project and from the New Project window choose Windows Application template, enter ODPWinClient for the name, and enter a directory to store the project.
     
  2. Add a reference to the assembly Oracle.DataAccessClient.dll selecting Project|Add Reference... from the main menu.
     
  3. Add a DataGridView control from the toolbox to the form. Name it m_dgvViewer and one Button control.
     
  4. Add the following namespace declaration. 
    1. using Oracle.DataAccess.Client;  
    2. using Oracle.DataAccess.Types; 
  5. The most important object is the connection instance of class OracleConnection. Prior to connecting to an Oracle Database using ODP.NET, you should add Net Service Names. You need to update the file tnsnames.ora in your local ORACLE_HOME\network\admin directory by adding entries that would be the data source list. You can edit this file manually, or using the Oracle Net Configuration tool which walks you through some screens gathering required connection information. The code for creating the connection is shown below. 
    1. string strConn = "Data Source=ORCL; User Id=scott; Password=tiger";  
    2. OracleConnection objConnection = new OracleConnection();  
    3. objConnection.ConnectionString = strConn; 
    In this case, we have configured the file tnsnames.ora as shown below to connect the sample database ORCL using the well-known user Scott and password tiger.
    1. ORCL =  
    2.     (DESCRIPTION =  
    3.         (ADDRESS_LIST =  
    4.             (ADDRESS = (PROTOCOL = TCP)(HOST = yourhost)(PORT = 1521))  
    5.         ) {  
    6.             CONNECT_DATA =  
    7.                 (SERVICE_NAME = ORCL)  
    8.         }  
    9.     ) 
    There are some connection string properties specific to ODP.NET providers such as DBA Privilege which sets to SYSDBA or SYSOPER to request administrative privileges, ValidateConnection, StatementCachePurge which causes the statement cache to be purged when the connection is closed, StatementCacheSize, ProxyUserId, ProxyPassword, IncrPoolSize, and DecrPoolSize.
     
  6. You can retrieve information about departments in the ORCL database system (this is an illustrative database system for managing human resources' data) using the following code.
    1. string strConn = "Data Source=ORCL; User Id=scott; Password=tiger";  
    2. using(OracleConnection objConnection = new OracleConnection()) {  
    3.     objConnection.ConnectionString = strConn;  
    4.     try {  
    5.         objConnection.Open();  
    6.         OracleCommand objCommand = new OracleCommand();  
    7.         objCommand.Connection = objConnection;  
    8.         objCommand.CommandText = "select deptno, dname, loc from dept";  
    9.         objCommand.CommandType = System.Data.CommandType.Text;  
    10.         OracleDataAdapter objAdapter = new OracleDataAdapter(objCommand);  
    11.         DataTable objTable = new DataTable();  
    12.         objAdapter.Fill(objTable);  
    13.         this.m_dgvViewer.DataSource = objTable;  
    14.         objConnection.Close();  
    15.     } catch (Exception ex) {  
    16.         System.Windows.Forms.MessageBox.Show(ex.ToString());  
    17.     } finally {  
    18.         objConnection.Close();  
    19.     }  
    20. } 
  7. You use a bind variable to include the value of the text box as part of the SELECT statement. For example, we retrieve information about a particular department as shown below. In this case the SQL SELECT Statement has some parameters. As you can see the parameter format in Oracle provider, it is used the ":" character, is different to SQL Server provider, while it is used the "@" character. Update operation is done similar using the ADO.NET model.
    1. string strConn = "Data Source=ORCL; User Id=scott; Password=tiger";  
    2. using(OracleConnection objConnection = new OracleConnection()) {  
    3.     objConnection.ConnectionString = strConn;  
    4.     try {  
    5.         objConnection.Open();  
    6.         OracleCommand objCommand = new OracleCommand();  
    7.         objCommand.Connection = objConnection;  
    8.         objCommand.CommandText = "select deptno, dname, loc from dept where deptno =: deptnoparam ";  
    9.         objCommand.CommandType = System.Data.CommandType.Text;  
    10.         OracleParameter objDeptNoParam = new OracleParameter("deptnoparam", OracleDbType.Int16);  
    11.         objDeptNoParam.Value = this.m_tbDeptNo.Text.Trim();  
    12.         objCommand.Parameters.Add(objDeptNoParam);  
    13.         OracleDataAdapter objAdapter = new OracleDataAdapter(objCommand);  
    14.         DataTable objTable = new DataTable();  
    15.         objAdapter.Fill(objTable);  
    16.         this.m_dgvViewer.DataSource = objTable;  
    17.         objConnection.Close();  
    18.     } catch (Exception ex) {  
    19.         System.Windows.Forms.MessageBox.Show(ex.ToString());  
    20.     } finally {  
    21.         objConnection.Close();  
    22.     }  
    23. } 

Conclusion

 
This article has illustrated the mechanism to access the Oracle database from the Microsoft.NET framework using ODP.NET and supporting platform interoperability concepts.


Similar Articles