Data base change Notification In Oracle 11g with odp.net 11

Database Change Notification is a new feature in oracle 11 g that enables client applications to register queries with the database and receive notifications in response to DML or DDL changes on the objects associated with the queries. The notifications are published by the database when the DML or DDL transaction.
Data base change notification can be implementd by two way:

1. QCN(Query Change Notification)
2. OCN (Object Change Notification)

1.QCN(Query Change Notification)

In Query change notification user can register the notification on a query result set.when ever there is any insert/update/delete command execute on this result set , client will get change notification.Sample Code:

OracleConnection con = new OracleConnection(constr);

con.Open();
OracleDependency dependency = new OracleDependency();

OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "Select * from Test_dummy where rownum > 5";

dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);

dependency.AddCommandDependency(command);
command.Notification.IsNotifiedOnce = false;
command.AddRowid = true;
command.ExecuteNonQuery();
con.Close();
con.Dispose();
Console.ReadLine();

static void dependency_OnChange(object sender, OracleNotificationEventArgs eventArgs)
{
try
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Table has been Changed");
Console.WriteLine(eventArgs.Source.ToString());
Console.WriteLine(eventArgs.Info.ToString());
Console.WriteLine(eventArgs.Source.ToString());
Console.WriteLine(eventArgs.Type.ToString());
DataTable dt = eventArgs.Details;
PrintDataTable(dt);
Console.ForegroundColor = ConsoleColor.White;
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
}

2.OCN(Object Change Notification)

If an application registers a query for object change notification (OCN), the database sends the application an OCN whenever a transaction changes an object associated with the query and commits, whether or not the result of the query changed.Sample Code

OracleConnection con = new OracleConnection(constr);
con.Open();

OracleDependency dependency = new OracleDependency();

dependency.QueryBasedNotification = false;
OracleCommand cmd = con.CreateCommand();
cmd.CommandText = "Select * from test_dummy";
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
dependency.AddCommandDependency(command);
command.Notification.IsNotifiedOnce = false;
command.AddRowid = true;
command.ExecuteNonQuery();

con.Close();
con.Dispose();

static void dependency_OnChange(object sender, OracleNotificationEventArgs eventArgs)
{
try
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Table has been Changed");
Console.WriteLine(eventArgs.Source.ToString());
Console.WriteLine(eventArgs.Info.ToString());
Console.WriteLine(eventArgs.Source.ToString());
Console.WriteLine(eventArgs.Type.ToString());
DataTable dt = eventArgs.Details;
PrintDataTable(dt);
Console.ForegroundColor = ConsoleColor.White;
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
}
}

For more Information http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28424/adfns_cqn.htm#CHDEFIFJ