22

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#?

Improve this question

5 Answers 5

Reset to default
7

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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

7

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

Improve this answer

Comments

3

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.

Improve this answer

Comments

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.

Improve this answer

Comments

0

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.

Improve this answer

1 Comment

This wouldn't work for fast-changing DBs, because you would need to be constantly monitoring the table, and even worse, there's no guarantee you're not skipping changes between checks.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.