Watch for a table new records in sql database

Question

I am using EF in a windows application and I want my application to do some tasks when a new record inserted in a certain table "these new records will be inserted by a website using the same db"
My question is how to watch this table for changes and get notified when a new record come, and can EF help me in this case?

UPDATE: I used the SqlDependency Class and used this in the db

ALTER DATABASE UrDb SET ENABLE_BROKER

And also created a service and a queue in the database http://screencast.com/t/vrOjJbA1y but I never get notified from my windows application.
Also the when i open the queue in sql server it is always empty http://screencast.com/t/05UPDIwC8ck seams that there is something wrong but i don't know.

Answer 1

Here are my propositions:

1. If you are able to add duplicate table to database then here is a solution. You have your table1 and table2 (Copy of table1). When you inserting new records to table1, you can compare them with existing records in your table2 and thus find new records. After comparing you should add all new records to table2. This is some kind of synchronization. This can be made via stored proc or programatically.

2. You don't need any another tables. You can store all your data in your app cache and check with some period of time (for example 5secs) are there any new events, that aren't exist in your cache. If they aren't exist - notify them in your log or somewhere else and add them to cache. But if there are too many records, the processing time will be greatly increased + memory consumption.

3. If you are able to change db then you can add something like 'isNew' column to your table. When a new data came from the website, the column will be 'true', your program can track this, and after processing set this flag to false for each record. (If the website can't set this flag, you can use SQL TRIGGER AFTER INSERT to set the flag value to true. Website can't even know about this feature if it is third-party web site or you don't want to change anything there)

4. Here is article about EF changes tracking: http://blogs.msdn.com/b/adonet/archive/2009/06/10/poco-in-the-entity-framework-part-3-change-tracking-with-poco.aspx

But the problem is that you should check whole table for changes via EF that will hit your app performance.

Here are useful info about SQL Server side change tracking and implementation concepts: http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/ http://msdn.microsoft.com/en-us/library/bb933994.aspx

Answer 2

See Change Data Capture, specifically sys.fn_cdc_get_max_lsn. That will create a place to look for changes.

Then, write a stored procedure to poll that view. Have it capture the previous value, and loop looking for a change with a WAITFOR delay appropriate to your data arrival rate.

When the procedure notices the highest LSN has changed, have it do something. It could select the row(s). It could also just print a message. Then it returns to its polling station (as it were).

Printing a message might sound odd and, not knowing the first thing about EF (and being severely allergic to such things) I don't know if it will help you here. But in principle it should because the underlying communication is present.

From your application, invoke the procedure. When the T-SQL PRINT (orRAISERROR) statement is executed by the procedure, a message will be sent to the client. Where it goes in EF or how you'd handle it, I can't say, but it should go somewhere useful because in ordinary circumstances it would be displayed to the user. In your case, your application will be looking for that particular message number or text, and react accordingly. (Other messages, naturally, you'd want to handle normally.)

This approach is very cheap and very simple. It uses almost no server resources, sends messages to the client only when changes occur, uses no extra tables, relies on almost no user-written code, and can be verified as running by looking at sysprocesses.