CODEWITHSUNDEEP
sql-server-2008
Ian
Ian

Reputation: 4909

sql server change notification and SqlDependency

I was looking for a better mechanism for notifying my desktop clients that a sql server table has changed. Exclude the option of an app server tier, I'm looking at solutions that fit into the existing thick client->Sql server model. I'm familiar with triggers and polling, but was hoping for something a bit smarter.

One option seems to be SqlDependency. I'm looking at that at the moment, but have seen a few mentions that it has "restrictions" and may be "unsuitable" for large numbers of changes. I've not found a lot of information on that, or many recent code examples.

What are you using for notification that a sql server table has been amended?

Upvotes: 1

Views: 528

Answers (1)

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

Unless you have a service, ie all chnages to tables go through it, then you are down to polling or dependancy. All dependancy does is hook into sql servers own table change code and fire a change event. The underlying mechanism is very simple, and can get swamped by a large number of changes, attempting to rationalise the changes in the event handler is problematic at best.

You might get somewhere with triggers to a "communications table", where you could add the rationalisation logic, then use dependancy from there.

So instead of detecting a simple change to column1 in table1 you trigger an insert to an event record in your comms table.

It's going to be a PIA because you've excluded an app server tier, you've also drastically constrained your options for doing something efficient and nice.

Upvotes: 2

Related Questions