Oracle Rdb and the .NET Framework

An Oracle Rdb How-To Document
January 2006

• What is .NET?
• What is Oracle Data Provider for .NET
• Using .NET with Oracle Rdb
  • Setting up Oracle SQL/Services
  • Download and Install Oracle Data Provider for .NET
  • Define the Service
  • Connect to the Service
• A Programming Example
  • Connecting
  • Creating a Table
  • Dropping a Table
  • Inserting a Row
  • Deleting a Row
  • Updating a Row
  • Performing a Select Count()
  • Performing a Query and Fetching the Results
  • Disconnecting
  • The Declaration Section
  • The main() Section
  • A Sample Execution
• Restrictions

.NET is the Microsoft strategy for connecting people, information, and devices through a set of services. The .NET technology is integrated throughout the Microsoft product suite and therefore allows for quick development and deployment of solutions.

Detailed information about .NET and its technology framework may be found on the Microsoft website or at your local bookstore.

What is Oracle Data Provider for .NET?

Oracle Data Provider for .NET, or otherwise known as ODP.NET, provides access to Oracle databases through the .NET framework. While there are other methods such as ODBC.NET and OLE DB.NET to access an Oracle database, Oracle Data Provider for .NET is the most native to the .NET environment and therefore allows for greater performance and functionality.

Detailed information about the Oracle Data Provider for .NET may be found on the Oracle Technology Network website. From that website, there are links for additional documentation and whitepapers as well as links for downloading the ODP.NET software.

Oracle Rdb currently supports the .NET framework through the use of the Oracle Data Provider for .NET for Oracle 10g. For it work though, the Oracle SQL/Services component must be used. When Oracle SQL/Services is coupled with Oracle Rdb, it enables the data provider written for an Oracle 10g database to work correctly with an Oracle Rdb database.

To get started, perform the following steps:

1. Perform the appropriate setup for Oracle SQL/Services on the OpenVMS node running the Oracle Rdb database so it can handle requests from Oracle clients.
2. Download and install the Oracle Data Provider for .NET for Oracle 10g on the Windows client machine.
3. Define the connecting service for the Data Provider by modifying the appropriate tnsnames.ora file on the Windows client.
4. Connect to the service via an application in .NET

1 Setting up Oracle SQL/Services

Before Oracle Rdb can accept requests from Oracle clients, Oracle SQL/Services needs to be appropriately configured on the OpenVMS node running Oracle Rdb. A correct setup allows Oracle Rdb to receive and translate the Oracle calls and act like a normal Oracle OCI service. Refer to the Oracle SQL/Services Server Configuration Guide or the Oracle Rdb Guide to SQL*Net for Rdb in the Oracle Rdb documentation for specific setup details.

Once this setup is completed, there will be an Oracle OCI service running on the OpenVMS node that is capable of accepting requests from the Oracle Data Provider for .NET. The service will be listening on the specific TCP port specified during setup.

2 Downloading and Installing Oracle Data Provider for .NET

The Oracle 10g Data Provider for .NET needs to be downloaded and installed onto the Windows machine that will be used to access the Oracle Rdb database via .NET. The Oracle Data Provider for .NET is available as a standalone install, and it is also included with the full installation of Oracle 10g on Windows. Either may be downloaded from the Oracle 10g website.

The last step is to define the service in the appropriate tnsnames.ora file on the Windows client. Once defined, the service can be referenced within applications using Oracle Data Provider for .NET.

The Oracle 10g tnsnames.ora file is located in the <ORACLE_HOME>/NETWORK/ADMIN directory. An entry similar to the following needs to be added to that file:

    # format
    #<CLIENT_NAME> =
    #  (DESCRIPTION =
    #    (ADDRESS = 
    #      (PROTOCOL = TCP)
    #      (HOST = <HOSTNAME>)
    #      (PORT = <PORT>)
    #    )
    #    (CONNECT_DATA =
    #      (SERVER = DEDICATED)
    #      (SERVICE_NAME = <SERVER_SERVICE_NAME>)
    #    )
    #  )

    # Sample
    MY_MFPER =
      (DESCRIPTION =
        (ADDRESS = 
          (PROTOCOL = TCP)
          (HOST = MYAXP.MYCOMPANY.COM)
          (PORT = 1521)
        )
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = MFPER)
        )
      )
   

The first part of the above example shows the format of the tnsnames.ora entry while the second part shows an actual sample entry.

For <CLIENT_NAME>, substitute a name such as MY_MFPER with which the service will be referenced on the client Windows machine.

For <HOSTNAME>, substitute the name of the OpenVMS node (for example, MYAXP.MYCOMPANY.COM) hosting the Oracle SQL/Services service.

For <PORT>, substitute the port number on the OpenVMS node which the service is listening. In the example, 1521 was used.

For <SERVER_SERVICE_NAME>, substitute the name of the Oracle SQL/Services OCI service on the OpenVMS system. In the example, MFPER must be an OCI service defined in Oracle SQL/Services on the MYAXP.MYCOMPANY.COM OpenVMS system.

Once the preceding steps have been performed, an Oracle Data Provider for .NET application will be able to connect to an Oracle Rdb database. The exact connection method will vary depending upon the .NET language used. However, the Oracle Rdb database will always be referenced by using the <CLIENT_NAME> service name (MY_MFPER in the example).

A Programming Example

The remainder of this document contains a dissection of a simple Microsoft Visual C++ application that makes a connection to an Oracle Rdb database, creates a table, performs a variety of inserts, updates, and deletes along with queries, and then drops the table and closes the connection.

Connecting

To connect to an Oracle Rdb database, create an OracleConnection object and supply it with the database service name and the username and password to be used for log in. The actual connection to the database is then made using the OracleConnection->Open method.

    void login( OracleConnection* conn,
                String* sid,
                String* userId,
                String* password)
    {
      conn->ConnectionString = String::Format( 
          S"Data Source={0};User Id={1};Password={2}",
          sid,
          userId,
          password );

       try
       {
         conn->Open();
       }
       catch ( OracleException* e1 )
       {
         Console::WriteLine( S"Open error: {0}", e1->Message->ToString() );
       }
    }
   

Almost all operations on the database must be performed using an OracleCommand object. Once created, the OracleCommand object must be provided with the OracleConnection object that is associated with the Oracle Rdb connection.

For creating a table, a String object is created with the SQL CREATE TABLE command and then assigned to the OracleCommand->CommandText attribute. It is then executed against the database using the OracleCommand->ExecuteNonQuery() method.

    void createDepartmentsTable( OracleConnection* conn )
    {
      OracleCommand* cmd = new OracleCommand();
      cmd->Connection = conn;

      try
      {
        cmd->CommandText = String::Concat(  
            S"create table departments (",
            S"department_id integer, ",
            S"department_name varchar(30) )" );
        cmd->CommandType = CommandType::Text;

        cmd->ExecuteNonQuery();
      }
      catch( OracleException* e1 )
      {
        Console::WriteLine( "Create Table error: {0}", e1->Message->ToString() );
      }

      cmd->Dispose();
    }
   

Inserting a Row

To insert a row in a table, create a OracleCommand object and provide it with the OracleConnection object. Then set the OracleCommand->CommandText attribute with the appropriate SQL INSERT statement and execute it using the OracleCommand->ExecuteNonQuery() method.

    void insertDepartment( OracleConnection* conn,
                           String* deptNbr,
                           String* deptName )
    {
      OracleCommand* cmd = new OracleCommand();
      cmd->Connection = conn;

      try
      {
        cmd->CommandText = String::Format(
            S"insert into departments ( department_id, department_name ) values( {0}, '{1}' )",
            deptNbr,
            deptName );
        cmd->CommandType = CommandType::Text;

        cmd->ExecuteNonQuery();	
      }
      catch( OracleException* e1 )
      {
        Console::WriteLine( "Insert error: {0}", e1->Message->ToString() );
      }

      cmd->Dispose();
    }
   

Deleting a Row

Deleting a row from a table is similar to inserting one. First, create a OracleCommand object and provide it with the OracleConnection object. Afterwards, fill the OracleCommand->CommandText attribute with the appropriate SQL DELETE statement and execute it using the OracleCommand->ExecuteNonQuery() method.

    void deleteDepartment( OracleConnection* conn,
                           String* deptNbr )
    {
      OracleCommand* cmd = new OracleCommand();
      cmd->Connection = conn;

      try
      {
        cmd->CommandText = String::Format(
            "delete from departments where department_id={0}",
            deptNbr );
        cmd->CommandType = CommandType::Text;

        cmd->ExecuteNonQuery();
      }
      catch( OracleException* e1 )
      {
        Console::WriteLine( S"Delete error: {0}", e1->Message->ToString() );
      }

      cmd->Dispose();
    }
   

Updating a Row

Updating a row (or rows) is also similar to inserting one. First, create a OracleCommand object and provide it with the OracleConnection object. Then fill the OracleCommand->CommandText attribute with the appropriate SQL UPDATE statement and execute it using the OracleCommand->ExecuteNonQuery() method.

    void updateDepartment( OracleConnection* conn,
                           String* deptNbr,
                           String* deptName )
    {
      OracleCommand* cmd = new OracleCommand();
      cmd->Connection = conn;

      try
      {
        cmd->CommandText = String::Format(
            S"update departments set department_name='{0}' where department_id = {1}",
            deptName,
            deptNbr );
        cmd->CommandType = CommandType::Text;

        cmd->ExecuteNonQuery();
      }
      catch( OracleException* e1 )
      {
        Console::WriteLine( "Update error: {0}", e1->Message->ToString() );
      }

     cmd->Dispose();
    }
   

Performing a Select count(*)

To perform a query that returns just the match count, create an OracleCommand object and assign it the OracleConnection object.

Then construct the appropriate SQL SELECT COUNT(*) command and assign it to the OracleCommand->commandText attribute. The count is calculated and returned by executing the OracleCommand->ExecuteScalar() method.

    void countDepartments( OracleConnection* conn )
    {
      OracleCommand* cmd = new OracleCommand();
      cmd->Connection = conn;

      try
      {
        cmd->CommandText = S"select count(*) from departments";
        cmd->CommandType = CommandType::Text;

        Object* numberOfDepartments = cmd->ExecuteScalar();
        Console::WriteLine( S"Number of Departments: {0}", numberOfDepartments );
      }
      catch ( OracleException* e1 )
      {
        Console::WriteLine( S"Count error: {0}", e1->Message->ToString() );
      }

      cmd->Dispose();
    }
   

Performing a Query and Fetching the Results

To perform a simple query and fetch the resulting rows, create an OracleCommand object and provide it with the OracleConnection object.

Once the SQL SELECT command has been constructed and assigned to the OracleCommand->commandText attribute, execute the query by invoking the OracleCommand->ExecuteReader() method. This will return an OracleDataReader object containing the result set.

Each row is fetched from the result set by using the OracleDataReader->Read() method. Each column in the row is retrieved using the OracleDataReader->get_item( column-number ) where column-number is a zero-based index of the columns listed in the SQL SELECT statement.

    void displayAllDepartments( OracleConnection* conn )
    {
      OracleCommand* cmd = new OracleCommand();
      cmd->Connection = conn;

      try
      {
        cmd->CommandText = S"select department_id, department_name from departments";
        OracleDataReader *dr = cmd->ExecuteReader();
        while( dr->Read() )
        {
          Console::Write( dr->get_Item(0)->ToString() );
          Console::Write( S" : " );
          Console::WriteLine( dr->get_Item(1)->ToString() );
        }
      }
      catch ( OracleException* e1 )
      {
        Console::WriteLine( S"Select error: {0}", e1->Message->ToString() );
      }

      Console::WriteLine( S"[End of List]" );
      cmd->Dispose();
    }
   

Dropping a Table

As with the create table operation, a drop table operation also requires an OracleCommand object. Once created, the OracleCommand object must be provided with the OracleConnection object that is associated with the Oracle Rdb connection.

For dropping a table, the appropriate SQL DROP TABLE command is constructed and then assigned to the OracleCommand->CommandText attribute. It is then executed against the database using the OracleCommand->ExecuteNonQuery() method.

    void deleteDepartmentsTable( OracleConnection* conn )
    {
      OracleCommand* cmd = new OracleCommand();
      cmd->Connection = conn;

      try
      {
        cmd->CommandText = S"drop table departments";
        cmd->CommandType = CommandType::Text;

        cmd->ExecuteNonQuery();
      }
      catch( OracleException* e1 )
      {
        Console::WriteLine( "Drop error: {0}", e1->Message->ToString() );
      }

      cmd->Dispose();
    }
   

Disconnecting

To disconnect from the Rdb database, simply call the OracleConnection->Close() method.

    void logout( OracleConnection* conn )
    {
      try
      {
        conn->Close();
      }
      catch ( OracleException* e1 )
      {
        Console::WriteLine( "Close error: {0}", e1->Message->ToString() );
      }
    }
   

The Declaration Section

If all the sections of this sample application were to be reassembled, the following include and using statements would be required to compile and run the application. All of the Oracle Data Provider for .NET definitions are accessible through the Oracle::DataAccess::Client namespace.

    #include "stdafx.h"

    #using 

    using namespace System;
    using namespace System::Data;
    using namespace Oracle::DataAccess::Client;
   

The main() Section

The following is the main() function which is required for the application to be runnable. It creates an OracleConnection object named conn and then calls all of the above described functions to log in, create a table and insert it's rows, perform searches, and finally cleanup.

    int main()
    {
      OracleConnection* conn = new OracleConnection();

      login( conn, S"MFPER", S"rdbuser", S"rdbpwd" );

      createDepartmentsTable( conn );
      countDepartments( conn );
      displayAllDepartments( conn );

      insertDepartment( conn, S"100", S"IT" );
      insertDepartment( conn, S"200", S"Sales" );
      insertDepartment( conn, S"300", S"Marketing" );
      insertDepartment( conn, S"400", S"Manufacturing" );
      insertDepartment( conn, S"500", S"Distribution" );
      countDepartments( conn );
      displayAllDepartments( conn );

      updateDepartment( conn, S"200", S"New Sales" );
      countDepartments( conn );
      displayAllDepartments( conn );

      deleteDepartment( conn, S"200" );
      countDepartments( conn );
      displayAllDepartments( conn );

      deleteDepartmentsTable( conn );

      logout( conn );

      return 0;
    }
   

A Sample Execution

When executed, the above main() function would produce the following output.

    Number of Departments: 0

    [End of List]

    Number of Departments: 5

    100 : IT
    200 : Sales
    300 : Marketing
    400 : Manufacturing
    500 : Distribution
    [End of List]

    Number of Departments: 5

    100 : IT
    200 : New Sales
    300 : Marketing
    400 : Manufacturing
    500 : Distribution
    [End of List]

    Number of Departments: 4

    100 : IT
    300 : Marketing
    400 : Manufacturing
    500 : Distribution
    [End of List]
   

Feature-rich .NET applications that access Oracle databases using the Oracle Data Provider for .NET are already available on the market. Custom applications can also be written by any in-house software development group. Almost all of these applications may be used to access Oracle Rdb databases instead of Oracle 9i / 10g databases, but there are a few restrictions.

Some of the more elaborate objects provided in Oracle Data Provider for .NET attempt to execute PL/SQL blocks of code behind the scenes in order to collect metadata from the database. These objects, such as the OracleCommandBuilder, will fail with an Invalid SQL command error. With your in-house applications, this error may be worked around by using the ExecuteReader() and ExecuteScalar() methods of the OracleCommand object to gather the database metadata.