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

Introduction

ODP.NET

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