Returning data from sqldependency

Question

I'm trying to write a vb.net application that uses an sql dependancy.

The dependancy will be triggered when data is added to the database.

I'm wondering if it's possible to have the dependancy return the data/query that triggered it.

currently i have to have the onchange event trigger a select statement to refresh the data i have cached. Is it possible to just have the dependancy return the new data that triggered it

i.e.

the following is added to a table, the table has 2 fields (id, data)

id: 1
data: hello

when this is added the dependancy onchange event will be fired and

id: 1
data: hello

will be returned

Answer 1

No. I don't think that's possible with SqlDependency. You could either use triggers, or use the change tracking in SQL 2008.

Answer 2

See http://rusanu.com/2006/06/17/the-mysterious-notification/ for an explanation how SqlDependency works under the covers. It is not possible to get the data changes along with the notification. While there are technologies around Change Tracking and Change Data Capture, but for a multitude of reasons, they are not appropiate for applications monitoring changes. They are intended for Data Synchronization (Sync Framework) and would have to be pulled for change, which in effect kills any attempt to use them for caching.

The ONLY proactive notification change technology is Query Notificatio, see more details in my link.