Watch for a table new records in sql database

问题:

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.

回答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

回答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.

回答3:

You can create an SQLCLR trigger FOR INSERT to table. This trigger can call a simple WCF Service that you can host in your application. THe assembly with the trigger needs to be registered in SQL Server. See here how to do it.

Another approach (the simplest one): use ordinary SQL trigger that monitors INSERTs to the table and executes xp_cmdshell with a command something like "copy nul /Y some_file_with_path.txt" Your application can use FileSystemWatcher class to watch for modifications to the same file. Configure the filter to watch for last access to this particular file only. See here how to do it. Proxy account for xp_cmdshell needs to be defined on SQL Server.

I faced the same problem a while ago. I also wasn't able to make SQl Notifications work.

回答4:

Another suggestion is to use hooks where you override the SaveChanges and do your custom logic.

https://stackoverflow.com/a/7654423/989679, https://github.com/kmckelvin/EFHooks/blob/master/EFHooks/HookedDbContext.cs http://thedatafarm.com/blog/data-access/objectcontext-savechanges-is-now-virtual-overridable-in-ef4/ (archive)

回答5:

How about a transaction table where you store the information of the transaction inserted by the website.

On the insertion table you create a trigger for insert that puts a new record in the transactions table.

Then you only need your windows app to periodically check the transaction table, when it finds something do what ever you want and then clear the record on the transaction table.

It is not very elegant, but it may work. I am not familiar with EF and I don't know if it has a better solution for this.

回答6:

Or use SqlTableDependency: https://tabledependency.codeplex.com/. This open source component send you an event with record detail information about the new row in table