- .NET 3.5
- Visual Studio 2008
- ODP.NET 11g Latest Client
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.
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
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.
public interface IWCFQNTableSubscribe
public interface IWCFQNTableCallback
[OperationContract(IsOneWay = true)]
void Callback( DataSet data);
There is a class called
WCFQNTableSubscription which implements the
- This method registers the calling client with the server.
- It then instantiates a class called
WCFQNRequestState by passing the client's callback information.
- It finally calls the Class's method
SubmitDataRequest to register the hardcoded query for notification of any
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 =
WCFQNRequestState subscription = new WCFQNRequestState(callback);
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;
_callback = callback;
The class contains a procedure called
- It connects to Oracle and uses OracleDependency instance for the Oracle Database Change Notification.
- 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.
- 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);
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;
Flow of the Application
- 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.
subscribe method on the WCF service registers the client by storing the client callback details.
- It then passes the client's CALLBACK information to
WCFQNRequestState class and calls a method called
SubmitDataRequest opens connection to Oracle.
- 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
Notification property. This indicates subsequent execution of command will register the notification.
- Allow the change notification handler in the database to persist even after the first database change.
cmd.Notification.IsNotifiedOnce = false;
- It then adds the event handler to handle the notification.
OnMyNotification method will be invoked when a notification message is sent from the database.
- 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.
- Now at this point, you can see the resultset in a datagrid.
- Next I will log into
SqlDeveloper tool to open the Oracle Database schema and then open up a table and update some rows manually.
- 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.
- Go and play around with it and have fun.
- PLEASE DON'T FORGET TO LEAVE A RATING OR COMMENT.
- 6th March, 2009: Initial post
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.