OnChangeEventHandler event not firing using OracleDependency
512977May 15 2006 — edited Aug 4 2006I am using VSTS 2005 and am connecting to Oracle Server via ODP.Net.
I want to recieve notification whenever any new row is inserted in a table.
But event OnChangeEventHandler is not getting fired .
I am using following code.Your inputs on issue will be highly useful.
Thanks
Jignesh
using System;
using System.Threading;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;
//This sample shows the database change notification feature in ODP.NET.
//Application specifies to get a notification when emp table is updated.
//When emp table is updated, the application will get a notification
//through an event handler.
namespace NotificationSample
{
public class MyNotificationSample
{
public static bool IsNotified = false;
public static void Main(string[] args)
{
//To Run this sample, make sure that the change notification privilege
//is granted to scott.
string constr = "User Id=HW2;Password=bestshore;Data Source=HWDV_LB";
OracleConnection con = null;
OracleDependency dep = null;
try
{
con = new OracleConnection(constr);
OracleCommand cmd = new OracleCommand("select * from Test", con);
con.Open();
// Set the port number for the listener to listen for the notification
// request
//OracleDependency.Port = 1005;
// Create an OracleDependency instance and bind it to an OracleCommand
// instance.
// When an OracleDependency instance is bound to an OracleCommand
// instance, an OracleNotificationRequest is created and is set in the
// OracleCommand's Notification property. This indicates subsequent
// execution of command will register the notification.
// By default, the notification request is using the Database Change
// Notification.
dep = new OracleDependency(cmd, false, 50000, false);
// Add the event handler to handle the notification. The
// OnMyNotification method will be invoked when a notification message
// is received from the database
dep.OnChange +=
new OnChangeEventHandler(MyNotificationSample.OnMyNotificaton);
// The notification registration is created and the query result sets
// associated with the command can be invalidated when there is a
// change. When the first notification registration occurs, the
// notification listener is started and the listener port number
// will be 1005.
cmd.ExecuteNonQuery();
// Updating emp table so that a notification can be received when
// the emp table is updated.
// Start a transaction to update emp table
//OracleTransaction txn = con.BeginTransaction();
//// Create a new command which will update emp table
//string updateCmdText =
// "insert into test values(1)";
//OracleCommand updateCmd = new OracleCommand(updateCmdText, con);
//// Update the emp table
//updateCmd.ExecuteNonQuery();
////When the transaction is committed, a notification will be sent from
////the database
//txn.Commit();
while (MyNotificationSample.IsNotified == false)
{
Console.WriteLine("No Notification Received");
Thread.Sleep(10000);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
// Loop while waiting for notification
con.Close();
}
public static void OnMyNotificaton(object src, OracleNotificationEventArgs arg)
{
Console.WriteLine("Notification Received");
DataTable changeDetails = arg.Details;
Console.WriteLine("Data has changed in {0}",
changeDetails.Rows[0]["ResourceName"]);
MyNotificationSample.IsNotified = true;
}
}
}