Receiving SqlDependency notifications for use outside XPO

XPO Team Blog
25 May 2007

 

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)

Tags
4 comment(s)
Luposky
Luposky

MANY THANKS !

But, maybe I'm wrong, the DataStoreChangeTrackingWrapper class is missing from the sample ...

For reducing the performanche hit (anyway I will use your Pubblication Service) will be nice to activate SqlDependency only for some class ... but i think you already considered this ...

Best regards,

Luca

25 May, 2007
Oliver Sturm (DevExpress)
Oliver Sturm (DevExpress)

Hi Luca,

my apologies about the delay in replying. We still seem to be having problems with comment notifications, so I wasn't aware you had commented.

Now, I'm confused about your question - the DataStoreChangeTrackingWrapper class is certainly there in my sample (and I checked the zip file as well) - otherwise the sample wouldn't build, right? The one thing I guess may confuse you is that the project isn't "well-structured", and so that class (and the persistent class used for the sample, too) is in the Program.cs file as opposed to its own source code file. Still, if you just build the sample, everything should be fine. Isn't that what's happening for you?

13 June, 2007
Oliver Sturm (DevExpress)
Oliver Sturm (DevExpress)

Okay, second update: we investigated further and it turns out that the download link for the sample in this post was wrong. It downloads you a file called "XPOSqlServer2005CacheAndNotification.zip", while the sample should really be called "XPOSqlServer2005CacheAndNotification2.zip" - note the "2" at the end. I have now corrected the link, so please re-download the file and you should get the updated version of the sample that includes the class in question.

Also, congrats on being apparently the first person to download and try the sample! :-)

13 June, 2007
Luposky
Luposky

Thanks for your attentions ...

Anyway you have anticipated me for few minutes ... here there is the proof of the bad link: sql.xpsoft.it/.../ops.jpg

Ok this is the hystory .. now I have downloaded the file and i'm trying to learn somethings from it ...

Best regards,

Luca

13 June, 2007

Please login or register to post comments.