Receiving SqlDependency notifications for use outside XPO

Well, there's obviously one really easy way to do it: just hook into the SqlDependency feature independently from XPO and receive the notifications that way. But there's an easier way to do it if XPO is hooking these notifications anyway. I got some code from an XPO developer and I created a sample around it to show how it works.

Note: Before that developer agreed to give me his code, he made me promise to say this: we really don't recommend you use this! It is an approach that will have severe performance drawbacks in many multi-user scenarios. If you end up actually using it, please make sure you do testing on your application in circumstances that resemble production use. If you find that you have performance problems - which we believe is very likely -, we will not be able to do anything about it on the XPO level. Please consider yourself warned.

Now you're probably asking: "why is he going to post a sample if he doesn't want me to use it?" Well, I believe that it is better to explain things to people, including (potentially) bad things, and let them make up their own minds. So here we go.

The sample I'm using is the same I showed in the previous post about the SqlDependency feature. You can download the whole source code below. For the purpose of notification, the sample now includes a new class called DataStoreChangeTrackingWrapper, which has two important (and a bunch of not as important) methods:

	...
	protected void ProcessChanges(DataCacheResult result) {
		CurrentCookie = result.Cookie;
		if (result.UpdatedTableAges == null)
			OnEverythingChanged( );
		else if (result.UpdatedTableAges.Count > 0)
			OnTablesChanged(result.UpdatedTableAges.Keys);
	}
	...
	public void CatchUp( ) {
		DataCacheResult result = Root.NotifyDirtyTables(CurrentCookie);
		ProcessChanges(result);
	}
	...

The DataStoreChangeTrackingWrapper class is a data store implementation. It overrides the ProcessUpdateSchema, ProcessSelectData and ProcessModifyData methods and makes sure that ProcessChange is called during the execution of all these methods. So if the application accesses the data store regularly (like the sample application does in its loop), the OnEverythingChanged and OnTablesChanged methods are going to be called automatically. The CatchUp method is for those cases where the regularity is not there - a timer could be used to call into CatchUp regularly to make sure that updates are passed through.

The two methods OnEverythingChanged and OnTablesChanged are those that get called once changes have been detected in the database. OnTablesChanged is the more common of the two, and it gets more detailed information as well. OnEverythingChanged is called only in cases where the cache gets disconnected - for instance when the cache root service is stopped in the case of a remotely published cache root. 

If you run the sample application and follow the same steps as in the previous article, you should see that at the point where external changes are being discovered, information about them will appear in the console. The sample uses a 5 second sleep in the main loop (previously one second), so that it's easy to spot the additional output line.

Download the sample here: XPOSqlServer2005CacheAndNotification2.zip (4640 bytes)

Free DevExpress Products – Get Your Copy Today