As Published In May/June 2006

DEVELOPER: ODP.NET

Following the Changes, Part Two

By Mark A. Williams

Use database change notification with ODP.NET and Oracle Database 10g Release 2.

You can access data from Oracle databases in .NET applications in different ways, but for features and performance, Oracle Data Provider for .NET (ODP.NET) is your best choice.

In the last issue ("Following the Changes, Part One," March/April 2006), I examined the new Database Change Notification feature introduced in Oracle Database 10g Release 2. In this issue, I examine in greater detail the classes that support change notification and show how to take advantage of additional ODP.NET features.

First, a Review

The Database Change Notification feature enables the database to automatically notify your application when a change occurs. This change can be in one or more of the values in a result set, in the underlying schema objects, or in the state of the database itself.

Using Database Change Notification is a three-step process: You register a query with the database, receive notification when a change occurs, and respond to the notification in your application. Any datatype can be included in the registered query, and all objects can be queried, with the exception of materialized views; fixed tables and views, such as V$ tables; and objects accessed through a database link.

The ODP.NET notification framework—in conjunction with Database Change Notification—supports the following activities 

• Creating a notification registration

• Grouping multiple change notifications into one request

• Registering for database change notification

• Removing notification registration

• Ensuring change notification persistence—guaranteeing that change notification is sent

• Retrieving notification information, including the object name, schema name, database events, and ROWID

• Defining the listener port number

The sample code download in the last issue demonstrates the Database Change Notification process and uses the default values for the ODP.NET objects that support change notification. Two important limitations are inherent in this sample code: Only a single change notification event is generated by the database per registration, and the application does not retrieve the actual changed data. In this issue, I extend this sample code to generate and receive multiple change notifications in a single registration and provide the information needed so you can retrieve the changed data. To retrieve the changed rows, you need to include a pseudocolumn—ROWID—in the registered query.

The ROWID Pseudocolumn

The pseudocolumn construct allows you to include a column name in a query for a column that is not part of a table definition. However, you use a pseudocolumn in a query exactly as you would a real table column. Various pseudocolumns are available in Oracle Database; they are documented in chapter 3 of the Oracle Database 10g Release 2 SQL Reference manual.

A ROWID value represents the address of a particular database row. Selecting a ROWID as part of a query is trivial—you simply include the ROWID pseudocolumn in the query text. The ROWID pseudocolumn is of particular interest when your application uses Database Change Notification because it allows the application to retrieve only rows that have changed (rather than reissuing the original query on a change notification event to retrieve all the rows that make up a result set).

To retrieve only the changed rows, include the ROWID pseudocolumn as part of the query you register with the database. Alternatively, set the AddRowid property of the OracleCommand object to true, and ODP.NET will automatically add the ROWID pseudocolumn to your registered query.

Listing 1, also available with the complete code download for this article, contains the main class file from the sample code download and registers a Database Change Notification query: 

select
  rowid,
  first_name,
  last_name,
  salary
from
  employees
where
  employee_id = :1

Code Listing 1: Main class file 

                               
using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;

namespace DatabaseChangeNotification02
{
  /// <summary>
  /// Summary description for Class1.
  /// </summary>
  class ChangeNotificationSample
  {
    // connection object for the database
    static OracleConnection con;

    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main(string[] args)
    {
      // sql statement to create result set that we will watch for changes
      // include the rowid to use to retrieve data that has changed
      string sql = "select rowid, first_name, last_name,
      salary from employees where employee_id = :1";
      
      // ensure you change this to an appropriate setting for your environment
      string constr = "User Id=hr;Password=hr;Data Source=oramag;Pooling=false";

      // create and open connection to database
      con = new OracleConnection(constr);
      con.Open();

      // create the command object
      OracleCommand cmd = new OracleCommand(sql, con);

      // create parameter object for the employee id
      OracleParameter p_id = new OracleParameter();
      p_id.OracleDbType = OracleDbType.Decimal;
      p_id.Value = 149;

      // add parameter to the collection
      cmd.Parameters.Add(p_id);

      // create the dependency object
      // note this does not perform the registration
      // it only defines the relationship
      OracleDependency dep = new OracleDependency(cmd);

      // set notification to persist after first notification is received
      // this will allow all notifications to be received instead of just
      // a single notification
      cmd.Notification.IsNotifiedOnce = false;

      // define the event handler to invoke when the change notification
      // is received
      dep.OnChange += new OnChangeEventHandler(OnDatabaseNotification);

      // execute the command (ignore the actual result set here)
      // this performs the registration that was defined when
      // the dependency object was created
      cmd.ExecuteNonQuery();

      // simply loop forever waiting for the notification from the database
      // you need to ctrl+c from the console
      // or Stop Debugging if running from within Visual Studio
      while (true)
      {
        Console.WriteLine("Waiting for notification...");
        Thread.Sleep(2000);
      }
    }

    public static void OnDatabaseNotification(object src, OracleNotificationEventArgs args)
    {
      // this method is invoked each time a change notification
      // is received from the database

      // sql statement to retrieve changed data using the rowid
      // including the rowid here is not required but is
      // informational in nature
string sql = "select rowid, first_name, last_name, salary from employees where rowid = :1";

      // create parameter object to hold the rowid
      // get the rowid from the OracleNotificationEventArgs
      // parameter to this method
      // this assumes there is a single row updated which is the
      // case in this sample
      OracleParameter p_rowid = new OracleParameter();
      p_rowid.Value = args.Details.Rows[0]["rowid"];

      // command to retrieve new data
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = sql;
      cmd.Parameters.Add(p_rowid);

      // execute the command
      OracleDataReader dr = cmd.ExecuteReader();

      // get the data
      dr.Read();

      // output a simple message with the resource name
      Console.WriteLine();
      Console.WriteLine("Database Change Notification received!");
      DataTable changeDetails = args.Details;
      Console.WriteLine("Resource {0} has changed.", changeDetails.Rows[0]["ResourceName"]);

      // display the new data
      Console.WriteLine();
      Console.WriteLine("  New Data:");
      Console.WriteLine("     Rowid: {0}", dr.GetString(0));
      Console.WriteLine("First Name: {0}", dr.GetString(1));
      Console.WriteLine(" Last Name: {0}", dr.GetString(2));
      Console.WriteLine("    Salary: {0}", dr.GetDecimal(3).ToString());
      Console.WriteLine();

      // clean-up
      dr.Dispose();
      cmd.Dispose();
      p_rowid.Dispose();
    }
  }
}

                            

You can include ROWID in the query you register or set the OracleCommand object AddRowid property to true. In either case, the ROWID value is included as part of the notification data, which allows you to construct a new query to retrieve data based on that ROWID.

The database passes the ROWID value to your application as part of the OracleNotificationEventArgs parameter, and this code passes this parameter to the change notification event handler: 

public static void 
OnDatabaseNotification(
     object src,
     OracleNotificationEventArgs args)

You can then access the ROWID through the Details property of the args parameter. The ROWID is represented as a String object in your .NET code. This code accesses the ROWID and assigns the value to an OracleParameter object named p_rowid: 

p_rowid.Value = 
  args.Details.Rows[0]["rowid"]

After you have retrieved the ROWID for a changed row, you construct a new query to get that row from the database. This code does just that: 

select
  rowid,
  first_name,
  last_name,
  salary
from
  employees
where
  rowid = :1

As you can see in this example, the query for retrieving the changed row differs from the original query in only one way: The ROWID, rather than the EMPLOYEE_ID, is used in the WHERE clause.

Two Key Classes

The OracleNotificationRequest and OracleNotificationEventArgs classes form the backbone of the Database Change Notification support in ODP.NET. Each of these classes is documented in ODP.NET and available in the Microsoft Visual Studio online integrated help.

OracleNotificationRequest. One important property of the OracleNotificationRequest class is IsNotifiedOnce. This Boolean property has a default value of true and is used to inform the database about whether you wish to receive a notification for each change that occurs or only a single notification. When the property is set to true, the server's notification registration will be removed after the first change. When the property is set to false, the registration will persist, regardless of the number of changes. In the sample code, the property is set to false, allowing the notification registration to persist: 

cmd.Notification.IsNotifiedOnce = false;

OracleNotificationEventArgs. The Oracle NotificationEventArgs class exposes the data (and metadata) associated with a change event to your application. Several properties exposed by the OracleNotificationEventArgs class may be useful in your applications: 

• The Details property is a DataTable that contains information about the current change notification. You use this property to access the ROWID pseudocolumn when handling a change notification event. The ROWID is exposed as the Rowid column in the DataTable.

• The Info property exposes a value of the OracleNotificationInfo enumeration. You use this property to determine what change event occurred—for example, to determine if the driving change in the database was an insert, update, or delete operation.

• The ResourceNames property is a string that contains the name of the schema and the name of the object that was changed.

Sample Code and Beyond

The sample code download (revised from Part One) responds to multiple database change notifications (rather than a single notification) and retrieves the changed row from the database. I encourage you to download and experiment with it. For example, you can extend this code to retrieve multiple changed rows and loop through all the rows in the Details DataTable (accessed via the args parameter) and then build a query such as this: 

select
  rowid,
  first_name,
  last_name,
  salary
from
  employees
where
  rowid = :1
or
  rowid = :2
or
  rowid = :3

Set breakpoints in the code, and run the sample, using the debugger. Examine the properties of the objects. Use this code as a starting point for implementing Database Change Notification in your applications, and experience its benefits for yourself.