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 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 ADO.NET model provides an interface to develop application regardless 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 ODP.NET provider.



ODP.NET



It is the 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 it supports Change Notification one of the features of Oracle database 10g. It is available for free downloading in Oracle Technology Network website. After installation, a toolset is integrated in 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 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.

    using Oracle.DataAccess.Client;

    using Oracle.DataAccess.Types;



  5. The most important object is the connection instance of the class OracleConnection. Prior 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 Oracle Net Configuration tool which walks you through some screens gathering required connection information. The code for creating the connection is show below.


        

    string strConn = "Data Source=ORCL; User Id=scott; Password=tiger";

    OracleConnection objConnection = new OracleConnection();

    objConnection.ConnectionString = strConn;





    In this case we have configured the file tnsnames.ora as shown bellow to connect the sample database ORCL using the well known user scott and password tiger.



    ORCL =

    (DESCRIPTION=

    (ADDRESS_LIST=

    (ADDRESS= (PROTOCOL=TCP)(HOST=yourhost)(PORT=1521))

    )

    {CONNECT_DATA=

    (SERVICE_NAME=ORCL)

    }

    )



    There are some connection string properties specific to ODP.NET provider 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.


    string strConn = "Data Source=ORCL; User Id=scott; Password=tiger";

    using (OracleConnection objConnection = new OracleConnection())

    {

    objConnection.ConnectionString = strConn;

    try

    {

    objConnection.Open();

    OracleCommand objCommand = new OracleCommand();

    objCommand.Connection = objConnection;

    objCommand.CommandText =
    "select deptno, dname, loc from dept";

    objCommand.CommandType = System.Data.
    CommandType.Text;

    OracleDataAdapter objAdapter = new OracleDataAdapter(objCommand);

    DataTable objTable = new DataTable();

    objAdapter.Fill(objTable);

    this.m_dgvViewer.DataSource = objTable;

    objConnection.Close();

    }

    catch (Exception ex)

    {

    System.Windows.Forms.
    MessageBox.Show(ex.ToString());

    }

    finally

    {

    objConnection.Close();

    }

    }





  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.



        

    string strConn = "Data Source=ORCL; User Id=scott; Password=tiger";

    using (OracleConnection objConnection = new OracleConnection())

    {

    objConnection.ConnectionString = strConn;

    try

    {

    objConnection.Open();

    OracleCommand objCommand = new OracleCommand();

    objCommand.Connection = objConnection;

    objCommand.CommandText =
    "select deptno, dname, loc from dept where

    deptno=:deptnoparam"
    ;

    objCommand.CommandType = System.Data.
    CommandType.Text;

    OracleParameter objDeptNoParam = new OracleParameter

    (
    "deptnoparam",OracleDbType.Int16);

    objDeptNoParam.Value =
    this.m_tbDeptNo.Text.Trim();

    objCommand.Parameters.Add(objDeptNoParam);

    OracleDataAdapter objAdapter = new OracleDataAdapter(objCommand);

    DataTable objTable = new DataTable();

    objAdapter.Fill(objTable);

    this.m_dgvViewer.DataSource = objTable;

    objConnection.Close();

    }

    catch (Exception ex)

    {

    System.Windows.Forms.
    MessageBox.Show(ex.ToString());

    }

    finally

    {

    objConnection.Close();

    }



    }




Conclusion.



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











John Charles Olamendy


Hebs a senior Integration Solutions Architect and Consultant. His primary area of involvement is in Object-Oriented Analysis and Design, Database design , Enterprise Application Integration, Unified Modeling Language, Design Patterns and Software Development Process. He has knowledge and extensive experience in the development of Enterprise Applications using Microsoft.NET and J2EE technologies and standards. He is proficient with distributed systems programming; and business-process integration and messaging using the principles of the Services Oriented Architecture (SOA) and related technologies such as Microsoft BizTalk Server, Web Services (Windows Communication Foundation, WSE, BEA WebLogic, Oracle AS and Axis) through multiple implementations of loosely-coupled system. Hebs a prolific blogger contributing to .NET and J2EE communities and actively writes articles on subjects relating to integration of applications, business intelligence, and enterprise applications development. He holds a Masterbs degree in Business Informatics at Otto Von Guericke University, Magdeburg, Germany. He was recently awarded as MVP.
He currently works in the telecommunication industry and delivers integration solutions for this industry. He harbors a true passion for the technology.


Comments

Author: aradhya21 Nov 2009 Member Level: Silver   Points : 1

Very nice article on Oracle usage in .Net, information provided is very productive and it provided essential information regarding how to use oracle in .Net and i used it in my college project.
Thanking you for your good article.



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: