When a new record is added to a specific table, I want my Windows application to perform a number of actions. "These new entries will be added by a website using the same db," I say.
How can I monitor this table for changes and be notified when a new record is added? Can EF assist me in this situation?
UPDATE: I used this in the db and the Class SqlDependency.
ALTER DATABASE UrDb SET ENABLE_BROKER
Additionally, I added a service and a queue to the database with the IDs http://screencast.com/t/vrOjJbA1y, but I never received any notifications from my Windows application.
Additionally, the queue is always empty when I open it in SQL Server, which suggests that something is wrong but I'm not sure what.
Here are some ideas I have:
Here is a solution if you can add duplicate tables to databases. Your table1 and table2 are available (Copy of table1). You can find new records by comparing the new records you insert into your table1 with those already present in your table2. You should add all new records to table2 after comparing. This is synchronization of some sort. This can be done programmatically or with a stored procedure.
There is no need for additional tables. You can keep all of your data in your app's cache and check after a set amount of time (for instance, 5 seconds) to see if any new events have occurred that aren't already recorded there. If they don't already exist, add them to the cache and note it in your log or some other place. However, if there are too many records, processing time and memory usage will both significantly increase.
You can add a column to your table called "isNew" if you have the ability to alter the database. When fresh data is received from the website, the column will be updated to "true." Your software can keep track of this and, after processing each item, set the flag to "false." (You can use SQL AFTER INSERT TRIGGER to set the flag value to true if the website is unable to set the flag. If it is a third-party website or you do not want to make any changes there, the website cannot even be aware of this feature.
The following article is about tracking EF changes: http://blogs.msdn.com/b/adonet/archive/2009/06/10/poco-in-the-entity-framework-part-3-change-tracking-with-poco.aspx
However, the issue is that you must use EF to check the entire table for updates, which may slow down your app.
Information on SQL Server side change tracking and implementation techniques is provided below: http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/http://msdn.microsoft.com/en-us/library/bb933994.aspx