Oracle 10g RELEASE2 Notification of changes to new features

Introduction

In. NET applications, there are many ways to achieve access to Oracle databases. However, from functional and performance analysis, Oracle Data Provider for. NET (odp.net) is undoubtedly our best option, a set of interfaces designed by Oracle specifically for. NET applications, which are accessed much faster than other methods.

This article will introduce one of the new features of Oracle database: Change Notification. To better illustrate this new feature, I'll introduce the use-case approach to its definition and usage.

Generation background of Database change notification

In the current process of program development, we often consider a high performance method is to use the data cache. The data cache avoids the need to access the database every time we want it, which saves a lot of time. But there is a problem, when we use data cache, if the data in the database has changed, then our data cache is inconsistent with the data in the database, which will lead to errors. In order to solve this problem, we usually use two kinds of methods.

1. Let the user manually update the contents of the data cache, such as providing an Update button.

2. Let our program interval a certain amount of time automatically to update the contents of the data cache.

It is not difficult to see that both approaches have considerable limitations.

The first method users must remember to update the data frequently, if the data in the database is changed but the user does not update the data, this will cause errors. The limitation of the second approach is that we do not have the means to set an exact time interval to make the data cache changes when the data changes.

Database change notification is to solve this problem.

Basic concepts of Database change Notification

The role of the database change notification is to automatically send a notification when the data in the databases changes.

There are three steps to using the database change notification:

1. Registration: Specify the query that the database will listen to. Odp. NET autoenrollment is based on the listener event for this query. The database can listen for DML (data manipulation Language) events, DDL (Data Definition Language) events, and global events (such as shutting down the database).

2. Notice: Once the data in the database changes, the database will be automatically notified, we want to define the event handling operations in our program.

3. Response: In our program, once the notification, we generally will automatically update the data cache, of course, we can inform the user of the changes in information, he will decide whether to update.

Example:

Using the database change notification in odp.net is simple, see the following routines. This routine is used by the HR database user.

static void Main (string[] args)
{
String sql = "Select First_Name, last_name, salary from employees where employee_id = 149";
String constr = "User id=hr; Password=hr;data Source=oramag; Pooling=false ";
OracleConnection con = new OracleConnection (CONSTR);
Con. Open ();
OracleCommand cmd = new OracleCommand (sql, con);
oracledependency dep = new oracledependency (cmd);
Dep. OnChange + = new Onchangeeventhandler (ondatabasenotification);
Cmd. ExecuteNonQuery ();
while (notificationreceived = = False)
{
Console.WriteLine ("Waiting for notification ...");
System.Threading.Thread.Sleep (2000);
}
Cmd. Dispose ();
Con. Dispose ();
Console.WriteLine ("Press ENTER to continue ...");
Console.ReadLine ();
}
public static void Ondatabasenotification (Object src, Oraclenotificationeventargs args)
{
Console.WriteLine ("Database Change Notification received!");
DataTable changedetails = args. Details;
Console.WriteLine ("Resource {0} has changed.", changedetails.rows[0]["resourcename"]);
Notificationreceived = true;
}

HR must have change notification permission, we use the following command.

grant change notification to hr;

Install odp.net on your computer and add the following using statement where your code just started.

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

Now you can run the routine. The output is as follows:

Waiting for notification...

This time to modify your database, for example, with the following command,

update employees set salary = salary+10
where employee_id = 149;
commit;

You can see the following output,

Database Change Notification received!
Resource HR.EMPLOYEES has changed.