Accessing Oracle Database from Microsoft.NET 2.0 using Oracle Data Provider for .NET By John Charles Olamendy April 16, 2007
Author Rank: Technologies: ADO.NET,Visual C# .NET Total downloads : Total page views : 19601 Rating : 4.5/5 This article has been rated : 2 times
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.
This Article is sponsored by: ExpertPDF ExpertPDF is a .NET library that offers the possibility to convert your HTML pages to PDF on the fly. If you need PDF reports you don't have to use complex report generators anymore. Just create a simple ASP.NET page and export it to PDF with ExpertPDF HtmlToPdf Converter. 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 © 2008 C# Corner and Authors. page 1 / 3 purged when the connection is closed, StatementCacheSize, ProxyUserId, ProxyPassword, IncrPoolSize and DecrPoolSize.
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.
About the author
© 2008 C#
John Charles Olamendy He’s 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 Corner and Authors. 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. He ’s a
More Similar Articles Accessing Oracle Database from Microsoft.NET 2.0 using Oracle Data Provider for .NET How To Get All Database Tables and TableColumns in Oracle Accessing Oracle Database Oracle Database Connectivity
page 2 / 3
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. He ’s 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 Master’s 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.
Oracle Database Connectivity
This article is converted to PDF using Expert PDF. Click here for a free download .
© 2008 C# Corner and Authors.
page 3 / 3