我的 .NET 应用程序如何在不使用触发器的情况下接收来自 SQL Server 2008 的插入/更新/删除通知？
我无法更改此数据库的架构。它是一个 ERP 数据库，有自己的触发器，它的版本更新会压垮我的数据库。由于通知服务正在消失或消失，还有其他方法可以做到这一点吗？也许是一些低级 COM API 或 .NET SQL Server Management APi 中的某些内容？
I can't change the schema of this database. It's an ERP database with its own triggers and its version updates would squash mine. Since Notification Services is going away or gone, is there any other way to do this? Perhaps some low level COM API or something in the .NET SQL Server Management APi?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
由于您无法添加触发器，我认为您唯一的选择是定时轮询。当您使用轮询时，您实际上并没有接收更新，而是以半频率的周期查询数据更改，这基本上最终像通知一样工作。
实施起来并不简单，但也不是那么困难。复杂性来自于需要轮询的表的数量和结构。它的最佳工作方式是查询每个表中的 DateTime 列（如果可用）并将最后一个最大时间与当前查询中的最大时间进行比较。如果两者不同，那么就会发生变化，并且您会启动所需的任何业务逻辑。
如果没有可用的 DateTime 列，您还可以测试主键更改（比较自动递增字段的最大值）。对于插入，DateTime 和 int PKey 比较都可以工作。对于更新，只有日期时间比较才有效（更新时没有 pkey 增量），但对于检测删除，如果您实际上从数据库中删除一行，那么它会变得更有趣，因为自您键入的数据以来，没有日期时间更改，也没有 pkey 增量走了。 （如果在轮询查询之间插入和删除相同数量的项目，则对表的行进行计数将破坏逻辑。）
System.Data.SqlClient 命名空间有一些可能有帮助的类，例如 SqlDependency。但要注意：
这意味着，如果您使用 SqlDependency，您应该确保它是一个服务器组件，使用某种机制来通知客户端更改（推送），或发布易于查询的数据以供客户端“拉取”。
Since you can't add triggers I think your only option is timed polling. When you use polling you're not actually receiving updates but querying on a semi-frequent cycle for changes to data which basically ends up working like a notification.
This is not trivial to implement but also not that difficult. The complexity comes from the number and structure of tables that need to be polled. The best way it works is to query on a DateTime column in each table (if one is available) and comparing the last max time against the max time in the current query. If the two are different then a change occurred and you fire off whatever biz logic you need to.
If there is no usable DateTime column you could also test for primary key changes (compare the max of an autoincerementing field.) For inserts the DateTime and int PKey comparisons will both work. For updates only the DateTime comparison will work (no pkey increment on update) but for detecting deletes it gets much more interesting if you actually remove a row from a db since there is no DateTime change and no pkey increment since the data that you key on went away. (Counting a table's rows will break logic if the same number of items get inserted and removed between polling queries.)
The System.Data.SqlClient namespace has some classes that might help such as SqlDependency. Beware though:
This means that if you use SqlDependency you should make sure that it is a server component that uses some mechanism to notify clients of changes (pushing), or publishing easily-queryable data for clients to "pull" on.
我们通过复制来做到这一点，并在订阅者数据库上使用我们自己的触发器。
We do this via replication, with our own triggers on the subscriber database.