XPO Beta feature: SqlDependency support

XPO Team Blog
16 May 2007

SqlDependency is a SQL Server 2005 feature that allows the database server to notify a client when changes occur in the database. XPO already has a strong caching infrastructure, but up until now it didn't have support for the SqlDependency feature. We will introduce this support in our DXperience 7.2 release, and the recent 7.1.3 release includes the feature as a beta. Please go ahead and test it and let us know of any problems you may find, but don't rely on its stability until 7.2 is released.

Prerequisites

To make the SqlDependency feature work, there are some requirements that have to be kept. Microsoft has all the information about this on MSDN.

A test program

To demonstrate how the feature in XPO works, I have created a small test program. It starts out like this (I left out the Person class - it's a standard persistent class, please download the source from below if you need it):

static void Main(string[] args) {
	XpoDefault.DataLayer =
		new SimpleDataLayer(new DataCacheNode(new DataCacheRoot(
		XpoDefault.GetConnectionProvider(
		MSSqlConnectionProvider.GetConnectionString(
		"(local)", "XPOSqlServer2005CacheAndNotification"),
		AutoCreateOption.DatabaseAndSchema))));

	using (Session initSession = new Session( )) {
		initSession.UpdateSchema(typeof(Person), typeof(XPObjectType));
	}

	for (; ; ) {
		using (UnitOfWork uow = new UnitOfWork( )) {
			Console.Clear( );
			XPCollection people = new XPCollection(uow);
			if (people.Count > 0)
				foreach (Person p in people)
					Console.WriteLine("Key: {0} Person: {1}", p.Oid, p.Name);
			else
				Console.WriteLine("Empty collection");
			Thread.Sleep(1000);
		}
	}
}

As you can see, the program simply sets up a cached connection provider, creates all the table structures for the Person class, and then goes into a loop, querying again and again for all the Person instances in the database. Now, of course I am interested to see what queries actually go through to the database, so I also activate logging in the App.config (see here for the background on that):

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<system.diagnostics>
		<switches>
			<add name="XPO" value="3" />
		</switches>
	</system.diagnostics>
</configuration>

Now I run the application, and of course there's nothing in the database. The console shows "Empty collection" and the log output is also restricted to the minimum - note how the queries against the database are run just once, as the caching layer catches all the rest.

 

Now, the interesting thing here is that when I go and add a record to the Person table in SQL Server Management Studio (while leaving the application running), the change will not be picked up by the application. So effectively the application is suddenly running with invalid data, although it's requerying all the time, and even using new sessions as well. This is the problem that the SqlDependency feature can solve.

 

Using SqlDependency

Using the SqlDependency support requires just one change to the source code of the test program: instead of calling the standard GetConnectionString method on the MSSqlConnectionProvider class, I use the method GetConnectionString2005WithCache instead. Because the connection provider that is created under these circumstances includes caching by default, I don't need to create the DataCacheNode and DataCacheRoot classes myself. So the line that sets the data layer looks like this:

	XpoDefault.DataLayer =
		new SimpleDataLayer(XpoDefault.GetConnectionProvider(
			MSSqlConnectionProvider.GetConnectionString2005WithCache(
			"(local)", "XPOSqlServer2005CacheAndNotification"),
			AutoCreateOption.DatabaseAndSchema));

Now when I rerun the application, I still get the caching behaviour, so the log output in VS looks much the same as before. The difference is that as soon as a record is added from SQL Server Management Studio, the change is picked up automatically. The next XPCollection that the test program creates results in an actual database query and the changed data is correctly retrieved. I can see in the VS log output how the query is executed - just once, as needed.

 

Publishing the service

It is important to consider that the SqlDependency feature uses resources on the server. Basically, every time a change happens in a table that is being monitored, every subscribed client must be notified. In conjunction with XPO, it seems advisable to make use of the publishing infrastructure, for instance via .NET Remoting, so that only one subscriber to the server's change notification exists. For this purpose there's another helper method on the MSSqlConnectionProvider class, called GetConnectionString2005CacheRoot. Using GetConnectionProvider with a connection string returned by GetConnectionString2005CacheRoot results in an object that implements ICachedDataStore. When this object is published and a client connects to it using a connection string of http://host:port/servicename or tcp://host:port/servicename format, XPO creates a DataCacheNode on the client automatically. The result is a setup that still benefits from change notifications on the database server, but doesn't have great resource requirements.

As you have seen, the creation of objects that activate the SqlDependency feature is based on the connection strings. Incidentally, my own XPO publication service also uses connection strings in its configuration file, so it is compatible with this new feature. A connection string that would be returned by GetConnectionString2005CacheRoot would look like this, for example: XpoProvider=MSSqlServer2005CacheRoot;data source=(local);integrated security=SSPI;initial catalog=XPOSqlServer2005CacheAndNotification

Download

Here's the test program I created, if you want to try out everything for yourself: XPOSqlServer2005CacheAndNotification.zip (4109 bytes)

Free DevExpress Products – Get Your Copy Today

The following free DevExpress product offers remain available. Should you have any questions about the free offers below, please submit a ticket via the DevExpress Support Center at your convenience. We’ll be happy to follow-up.
Tags
Luposky
Luposky

This is fantastic !!

Would be nice to have a way to subscribe to notifications events from our program... or at least to check if some notifications has occoured ...

Small note on line 16 (some cut & paste error I suppose):

XPCollection<Person> people = new XPCollection<Person>(uow);

16 May 2007
Robert Kozak
Robert Kozak

Will this work with the DevX Grid component? Will it be able to respond to these notifications?

-- Robert Kozak

Senior Software Architect

Nowcom Corporation

16 May 2007
Oliver Sturm (DevExpress)
Oliver Sturm (DevExpress)

Hi guys

My apologies for the delay - apparently our comment notification is not currently working, so I wasn't aware something was waiting for me.

Robert - sure it will work with the grid, why not?

Luca - there's no really easy way to do it - events are not possible because that's not how the SqlDependency system works. I'm going to create a sample and show how you can hook into the change tracking, but let me mention right now that this approach doesn't seem like a very good one performance-wise - I will most probably recommend against using it.

Regards

Oliver

24 May 2007
Anonymous
XPO

Well, there's obviously one really easy way to do it: just hook into the SqlDependency feature independently

25 May 2007
Robert Kozak
Robert Kozak

Thanks Oliver,

This is something I wanted for awhile now and I can't wait to try it out to see if it will do what I am expecting from it.

6 June 2007
Anonymous
eXpress App Framework Team

This is post no. 10 in the mini series "10 exciting things to know about XAF". You can find

31 May 2008
Anonymous
eXpress App Framework Team

This is post no. 10 in the mini series "10 exciting things to know about XAF". You can find

31 May 2008
Alex Hoffman
Alex Hoffman

Readers should note the comment at http://tinyurl.com/6ojd8u.  It would be great if further detail on DevExpress's testing of SqlDependency could be published.

31 May 2008
Anonymous
XPO

Introduction First, we would like to refresh your memory and return to the distant past and our old posts

17 May 2011
jens theisen
jens theisen

I'm reviving this thread because I'm interested in whether SqlDependency is now supported by the DXGrid as Robert asked in 2007. Oliver didn't quite got what was asked I believe because he said it would be no problem and at the same time suggested that eventing is difficult.

I'm not familiar with the state-of-the-art of WPF/Forms database development and I'm just wondering:

Are we still in a world where clients need to pull data periodically or do we indeed have a live view in something like the DXGrid on a query to a remote database?

13 November 2012

Please login or register to post comments.