Introduction

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) 

QCN(Query Change Notification)

In Query change notification user can register the notification on a query result set. whenever 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);
      }
   }

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

Hello vishal, thanks for this article :-) I´ve implemented QCN within my project and so far it works fine - except that I´ve registered a query ("Select * from spv_anfrage where clientid = '{0}' and status = '0') and I get an OnChange-event no matter which clientid I insert into my SQL-string. But my purpose is only to get an OnChange if a particular value in clientid is inserted. Here´s a part of my code: ---------------------SNIP--------------------------- m_cnnDCN = new OracleConnection(conn_string); try { m_cnnDCN.Open(); OracleDependency.Port = 1005; String sql = String.Format("Select * from spv_anfrage where clientid = '{0}' and status = '0'", Properties.Settings.Default.ClientID); OracleCommand selectCmd = new OracleCommand(sql, m_cnnDCN); // Create an OracleDependency instance and bind it to an OracleCommand instance. m_oraDepDCN = new OracleDependency(); // Add the event handler to handle the notification. m_oraDepDCN.OnChange += oraDependency_Change; m_oraDepDCN.AddCommandDependency(selectCmd); selectCmd.Notification.IsNotifiedOnce = false; selectCmd.AddRowid = true; // Rgistrierung in der Datenbank int result = selectCmd.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message + "\n" + ex.StackTrace, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { if (m_cnnDCN != null && m_cnnDCN.State != ConnectionState.Closed) { m_cnnDCN.Close(); //m_cnnDCN.Dispose(); } } ---------------------SNAP-------------------------- Any idea why OnChange get´s fired on everey INSERT into the table spv_anfrage??? Any hint or help is highly welcome :-) Regards, Andre

Posted by: duetscha   Posted Date :12-Apr-13

About This Article

Copyrights 2018, www.expertsupdates.com