我希望我的数据库 (SQL) 通知或推送更新到客户端应用程序 [英] I want my database (SQL) to notify or push updates to client application

问题描述

我在 VB.net 2010 和 SQL 2008 上开发这个应用程序.
我希望通知客户端数据库的更新，并且应用程序用于使用计时器检查数据库在指定分钟内的更改，这确实效率不高.我读过查询通知、sqldependency、service broker，但后来我读到一些内容，说如果我有 100 个客户端，它们可能效率不高我正在使用查询通知将通知推送到我的应用程序.
有人会帮助我了解我应该做什么，以及我该怎么做(如果有可用的示例，这将非常有帮助).提前致谢！

I was developing this application on VB.net 2010 and SQL 2008.
I wanted the clients to be notified for updates on the db, and the application used to check the db for changes in the specified minute using a timer, which really is not efficient. I read about query notification, sqldependency, service broker, but then I read something that said they might not be efficient if I have 100 clients and I'm using query notifications to push notifications to my application.
Would someone help out on what I should do, and how I can do it (would be really helpful if examples are available). Thanks in advance!

推荐答案

查询通知将推送到 Service Broker 服务，而不是直接推送到您的应用程序.请参阅神秘通知以了解其工作原理.您的应用程序正在通过发布 WAITFOR(RECEIVE) 语句在数据库上.这意味着 100 个客户端中的每一个都占用一个 SQL Server 工作线程(这是有限的，请参阅 最大工作线程 选项).我已经看到这在生产中与 +1000 个客户端一起工作(在增加了最大工作线程选项之后)，但我建议反对它.

Query Notification will push to a Service Broker service, not directly to your application. See The Mysterious Notification to understand how it works. Your application is waiting for notifications by posting a WAITFOR(RECEIVE) statement on the database. Which implies that each of the 100 clients is occupying one SQL Server worker thread (which are limited, see max worker threads option). I've seen this working in production with +1000 clients (after bumping up the max worker threads option) but I would advise against it.

我的建议是使用 SqlDependency/QueryNotifications 对更改进行一个服务监控.然后，此服务将使用 WCF 等将通知推送到您正在运行的所有应用程序.您将订阅通用更改(表 Foo 已更改)，而不是特定更改(表 Foo 中的行 x 已插入).

My recommendation would be to have one service monitoring for change, using SqlDependency/QueryNotifications. This service would then push notifications, using WCF for instance, to all your running apps. You would subscribe to generic changes (the table Foo was changed), not to specific ones (the row x in table Foo was inserted).

作为一般规则，SqlDependency/Query Notifications 只能通知您数据已更改，但不会推送新数据.一旦收到通知，应用程序必须通过再次运行查询来刷新其本地数据集.

As a general rule SqlDependency/Query Notifications can only inform you that data has changed, but it won't push the new data. The application must refresh its local datasets by running the queries again, once notified.

这篇关于我希望我的数据库 (SQL) 通知或推送更新到客户端应用程序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！