SQL Server Database Change Listener C#

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开发者_StackOverflow中文版 in C#?

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

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

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.

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.

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.