监视sql数据库中的表新记录 [英] Watch for a table new records in sql database

查看:25
本文介绍了监视sql数据库中的表新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在 Windows 应用程序中使用 EF,我希望我的应用程序在某个表中插入新记录时执行一些任务这些新记录将由使用相同数据库的网站插入"
我的问题是如何查看此表的更改并在新记录出现时得到通知,在这种情况下,EF 可以帮助我吗?

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?

更新:我使用了 SqlDependency Class 并在 db 中使用了这个

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

ALTER DATABASE UrDb SET ENABLE_BROKER

并且还在数据库中创建了一个服务和一个队列http://screencast.com/t/vrOjJbA1y 但我从未从我的 Windows 应用程序中收到通知.
此外,当我在 sql server 中打开队列时,它始终为空 http://screencast.com/t/05UPDIwC8ck 接缝有问题,但我不知道.

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. 如果您能够将重复表添加到数据库,那么这里有一个解决方案.您有 table1table2(table1 的副本).当您向 table1 插入新记录时,您可以将它们与 table2 中的现有记录进行比较,从而找到新记录.比较后,您应该将所有新记录添加到 table2.这是某种同步.这可以通过存储过程或以编程方式进行.

  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.

您不需要任何其他表.您可以将所有数据存储在应用程序缓存中,并在一段时间内(例如 5 秒)检查是否有任何新事件,这些事件不存在于您的缓存中.如果它们不存在 - 在您的日志或其他地方通知它们并将它们添加到缓存中.但是如果记录太多,处理时间会大大增加+内存消耗.

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.

如果您能够更改数据库,那么您可以向您的表中添加类似isNew"列的内容.当网站有新数据时,该列将为真",您的程序可以跟踪这一点,并在处理后为每条记录将此标志设置为假.(如果网站不能设置这个标志,你可以使用SQL TRIGGER AFTER INSERT将标志值设置为true.如果是第三方网站,网站甚至不知道这个功能或者你不想在那里改变任何东西)

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)

这是关于 EF 更改跟踪的文章:http://blogs.msdn.com/b/adonet/archive/2009/06/10/poco-in-the-entity-framework-part-3-change-tracking-with-poco.aspx

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

但问题是您应该通过 EF 检查整个表是否有会影响您的应用程序性能的更改.

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

以下是有关 SQL Server 端更改跟踪和实现概念的有用信息:http://www.mssqltips.com/sqlservertip/1819/using-change-tracking-in-sql-server-2008/http://msdn.microsoft.com/en-us/library/bb933994.aspx

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

这篇关于监视sql数据库中的表新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆