Click here to Skip to main content
Click here to Skip to main content

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

, 6 Mar 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
WCF Oracle Database Change Notification Application

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.

OracleDataChanged_small.JPG

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.

OracleDataAboutToChange_small.JPG

  1. 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.

OracleDataChanged_small.JPG

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

History

  • 6th March, 2009: Initial post 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

kamran anwar (Software Engineer)
Architect BAE Systems IT
United States United States
BAE Systems IT (Client – US BLS) (Washington, DC), SEP 2004 – Present
Senior Software Architect
Duties and Accomplishments – Designed, Documented, Implemented, & Tested:
1. Currently in Progress: IPS Collection System (Java 6, Oracle 11g, WebLogic 11g, SOA, JNDI, JMS, EJB, SQL Developer, Star UML, Adobe Flex/AIR, DI/IOC Parsley Framework Container, Live Cycle Data Services DMS, SQLite & RIA Online/Offline Synchronization with Distributed Transactions).
 
2. Currently in Progress: Additional 10 hours/week from home for a different department (Sean Curran’s) in order to implement Jasper Reports with Spring RESTFUL services according to user’s requirements by utilizing Spring toolkit IDE, IReport Designer for Jasper Reports, Weblogic and Oracle PL/SQL.
 
3. In Production: JANUS Forms & Batch Processing System (.NET 3.5, WCF, Win Forms, WPF, Oracle 11g, ODP.NET, SOA, SQL Developer, Visio, Visual Studio Team Architect 2010).
 
4. On Going: Conducting .NET/Java resource interviews for BAE Systems
 
5. On Going: Successfully helping other .NET/Java developers in order to troubleshoot the issues they are facing.
 
6. On Going: Support for Fairfax Imaging Fax/Scanner Software solutions with Brooktrout Fax.

Comments and Discussions

 
QuestionDCN is not received if more than 80 rows are updated in single transaction PinmemberMember 929210322-Jul-12 21:24 
GeneralMy vote of 5 Pinmemberedmondng424-Nov-11 19:29 
GeneralGreat article, but I have a privilege issue. Pinmemberpc.huff25-Feb-10 12:24 
GeneralRe: Great article, but I have a privilege issue. Pinmemberkamran anwar (Software Engineer)8-Mar-10 18:52 
GeneralRe: Great article, but I have a privilege issue. PinmemberManish7928-Jul-10 5:38 
GeneralRe: Great article, but I have a privilege issue. Pinmemberkamran anwar (Software Engineer)8-Nov-10 11:11 
GeneralChange Notification PinmemberCode Animal26-Jan-10 5:40 
Generalgreate Article ,thinks! Pinmembergangzhiyong10-Jan-10 16:42 
Generalwemasterit , I tried the link to download the code and it is working fine Pinmemberkamran anwar (Software Engineer)13-Mar-09 2:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141223.1 | Last Updated 6 Mar 2009
Article Copyright 2009 by kamran anwar (Software Engineer)
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid