How To Receive Oracle Database Change Notification using WCF SOA Technology with netTCPBinding, netNamedPipeBinding and wsdualHTTPBinding

• Download source - 708.86 KB

Prerequisites

1. .NET 3.5 
2. Visual Studio 2008 
3. ODP.NET 11g Latest Client

Introduction

This application shows how to use Oracle's Database Change Notification technology with WCF to call back to WCF Service, then WCF service calls back to multiple registered clients via different protocols such as TCP, IPC and HTTP where the data in the grid gets refreshed.

Background

I tried to search for an example on Google in which WCF technology has been used that subscribes the client for the Oracle 10g/11g Database Change Notification technology. However, I did not find an example in which ODP.NET is used, therefore here it is for you guys. The Database Change Notification technology increases the performance of the application, provides scalability, etc. as compared to polling the database every few minutes/seconds or hours to get the latest data available.

Using the Code

WCFService Details

The important thing to notice here is the Callback Contract attribute specifying the contract the WCF service should use to callback on the client and the IsOneWay attribute on the two contracts to specify an asynchronous bidirectional contract.

Since we're going to callback the client each time the data is changed, the new dataset is sent with each callback. The client subscribes to the data and does not get back any data in the Subscribe call, but the WCF service will immediately call him back on the supplied callback, providing the dataset with the current state of the data.  After that, each time the data is changed, the WCF service will again callback the client with a new dataset.

[ServiceContract(
        Name="WCFQNTableSubscribe",
        Namespace="WCFOracleDatabaseChangeNotification",
        CallbackContract=typeof(IWCFQNTableCallback),
        SessionMode=SessionMode.Required)]
    public interface IWCFQNTableSubscribe
    {
        [OperationContract(IsOneWay=true)]
        void Subscribe();
    }

    /// <summary>
    /// The callback contract. The dataset is 
    /// sent back to client using this contract
    /// </summary>
    public interface IWCFQNTableCallback
    {
        [OperationContract(IsOneWay = true)]
        void Callback( DataSet data);
    }

WCFQNTableSubscription Class

There is a class called WCFQNTableSubscription which implements the subscribe method.

Subscribe() Procedure

1. This method registers the calling client with the server.
2. It then instantiates a class called WCFQNRequestState by passing the client's callback information.
3. It finally calls the Class's method SubmitDataRequest to register the hardcoded query for notification of any INSERT, UPDATE, DELETE, etc. changes by the Oracle database to be sent back to the calling client and then to refresh the datagrid on the front end. 

public void Subscribe()
	{
	    IWCFQNTableCallback callback = 
             OperationContext.Current.GetCallbackChannel<IWCFQNTableCallback>();
	    WCFQNRequestState subscription = new WCFQNRequestState(callback);
	    subscription.SubmitDataRequest();
	}

WCFQNRequestState Class

There is an internal class called WCFQNRequestState used which is comprised of a constructor that takes in the callback information of the client.

private IWCFQNTableCallback _callback;

        public WCFQNRequestState(
            IWCFQNTableCallback callback)
        {
            _callback = callback;
        }

The class contains a procedure called SubmitDataRequest().

SubmitDataRequest() Procedure

1. It connects to Oracle and uses OracleDependency instance for the Oracle Database Change Notification.
2. It adds the event handler to handle the notification. The OnMyNotification method will be invoked when a notification message is sent from the database only when the registered query's row is updated, inserted, deleted, etc.
3. It also populates the queried resultset into the dataset and callbacks to the WCF client to refresh the datagrid.

public void SubmitDataRequest()
   {
   string constr = " your connection string for Oracle Database"
   string sqlSelect = "select * from sched_generation_process ";
   string sql = sqlSelect + "where sched_gen_sid = 3";
 string tablename = "sched_generation_process";
 DataSet ds = new DataSet();
   OracleConnection con = new OracleConnection(constr);
   OracleCommand cmd = new OracleCommand(sql, con);
   con.Open();
   cmd.AddRowid = true;
   OracleDependency dep = new OracleDependency(cmd);
   cmd.Notification.IsNotifiedOnce = false;
   dep.OnChange += new OnChangeEventHandler(dep_OnChange);
   OracleDataAdapter da = new OracleDataAdapter(cmd);
   da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
   da.Fill(ds, tablename);
       _callback.Callback( ds);
   }

Flow of the Application

1. When the "subscribe button" is pressed on the client side (Note: I have included 3 client projects for TCP, HTTP and IPC), the client calls a "Subscribe" method of the WCF service.
2. The subscribe method on the WCF service registers the client by storing the client callback details.
3. It then passes the client's CALLBACK information to WCFQNRequestState class and calls a method called SubmitDataRequest.
4. SubmitDataRequest opens connection to Oracle.
5. It then registers notification with command object if result changes. 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.
6. Allow the change notification handler in the database to persist even after the first database change.
   cmd.Notification.IsNotifiedOnce = false;
7. It then adds the event handler to handle the notification. 
   The OnMyNotification method will be invoked when a notification message is sent from the database.
8. Finally the current result as dataset from the query is returned back via the client callback method and then the dataset gets bound to the datagrid.
9. Now at this point, you can see the resultset in a datagrid.
10. Next I will log into SqlDeveloper tool to open the Oracle Database schema and then open up a table and update some rows manually.

11. As soon as I update 1 or more rows, the client immediately gets notified by the database change notification system. The datagrid gets refreshed with the new data.

12. Go and play around with it and have fun.
13. PLEASE DON'T FORGET TO LEAVE A RATING OR COMMENT.

History

• 6^th March, 2009: Initial post