SQL Server Database Change Listener C#

Question

I want to listen for changes to data in a SQL Server database from C#. I was hoping that there would be some sort of listener which I could use to determine if data that I have is stale. Despite being a fairly common scenario I can't find any solutions which aren't to simply poll the database.

I use Linq-To-SQL to access the data and hence have a DataContext object, I was hoping I could listen for an on data changed event but I can't seem to find one.

I appreciate that it's a non-trivial barrier (From C# method to SQL Server DB), the reason I expected this to be a solved problem is that it's a common requirement for GUIs. If it's not possible to Listen for updates how to you keep the Data displayed in a GUI fresh (When it's backed by a SQL Server data source).

Although this isn't for GUI work I was expecting to adapt something from that realm.

Is there a way to subscribe to SQL Server database change events in C#?

Answer 1

If you are using SQL Server 2008, there is a built in Change Data Capture that's pretty handy.

http://msdn.microsoft.com/en-us/library/bb522489.aspx

You can read the CDC data.

Answer 2

I've never used them before, but have you tried SQL Server Events notifications? See this article: Getting Started with SQL Server Event Notifications

Answer 3

Mate, Unfortunately there's no easy way around to it, but having said that its not impossible because websites like facebook and gamil do this.

I would suggest you to search for keywords "HTTP push" on google. I am sure you will get some thing.

I have done this kind of thing previously. Here's a small explanation of how i did it.

On body load of my webform i inserted a ifram which will be invisible and sets its source for another wepage and on the page load(server side) of that web page i calle a method which goes to the Db and gets the changes for me.

le me know if this helps i can also provide you with sample application of what i said above

Cheers Amit

Answer 4

You're looking for the SqlDependency class, which allows you to listen for changes to the resultset of a SQL query.

Answer 5

The DataContext won't offer you any type of listener functionality with SQL Server. Your best bet is to create a polling application, or even a separate thread which polls the database periodically for changes and expose an event which your main application can listen to.

Answer 6

I would use a table with a single row in the db to catalog last updated, inserted, or deleted events and then create triggers on each table of importance to update this table and then poll this table for changes.