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!
我在VB.net 2010和SQL 2008中开发了这个应用程序。我希望客户端得到关于db的更新的通知，并且应用程序使用计时器在指定的分钟内检查db的更改，这确实是不高效的。我读了关于查询通知、sqldependency、service broker的内容，但是我读了一些内容，说如果我有100个客户端，并且我正在使用查询通知将通知推送到我的应用程序，那么它们可能没有效率。是否有人能帮助我做我应该做的事情，以及我如何做(如果有例子的话，这将非常有帮助)。提前谢谢!
3 个解决方案
#1
16
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.
查询通知将推送到服务代理服务，而不是直接应用到您的应用程序。查看神秘的通知以了解它是如何工作的。您的应用程序通过在数据库上发布WAITFOR(RECEIVE)语句来等待通知。这意味着每个100个客户机都占用了一个SQL Server工作线程(这是有限的，见max worker线程选项)。我已经看到在生产中使用+1000客户端(在增加了max worker线程选项之后)，但是我建议不要这样做。
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/QueryNotifications来进行一次服务监视更改。然后，该服务会将通知推送给所有正在运行的应用程序，例如使用WCF。您将订阅通用更改(表Foo被更改)，而不是特定的更改(表Foo中的第x行被插入)。
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.
作为一个通用规则，SqlDependency/Query通知只能通知您数据已经更改，但它不会推送新的数据。应用程序必须在收到通知后再次运行查询来刷新其本地数据集。
#2
3
Be careful using SqlDependency class - it has the problems with memory leaks. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:
使用SqlDependency类时要小心——它有内存泄漏的问题。Hovewer可以使用SqlDependency类- SqlDependencyEx的开源实现。它使用数据库触发器和本地服务代理通知接收关于表更改的事件。这是一个用法示例:
int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME))
{
sqlDependency.TableChanged += (o, e) => changesReceived++;
sqlDependency.Start();
// Make table changes.
MakeTableInsertDeleteChanges(changesCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(changesCount, changesReceived);
With SqlDependecyEx you are able to monitor just UPDATE, avoiding DELETE and INSERT. Hope this helps.
使用SqlDependecyEx，您可以监控更新，避免删除和插入。希望这个有帮助。
#3
0
If you don’t update databse manually and all data manipulation are in your application, you should to detect changes in application service layer or business layer instead of db in case you depend on database technology and going to other databases will be difficult. In other hand if you have manual update on db or dependency to db is not important you can use CDC (chane data capture) and push changes into service broker and your application pop changes from service broker then send them to client by bidirectional http communication technology same as SignalR.
如果您不手动更新数据库，并且所有的数据操作都在您的应用程序中，那么您应该检测应用程序服务层或业务层的更改，而不是db，以防止您依赖于数据库技术并访问其他数据库将会很困难。在另一方面,如果你手动更新数据库或依赖db并不重要你可以使用CDC(查恩数据捕获),推动服务代理和应用程序的流行变化从服务代理然后寄给客户通过双向http通信技术SignalR一样。
#1
16
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.
查询通知将推送到服务代理服务，而不是直接应用到您的应用程序。查看神秘的通知以了解它是如何工作的。您的应用程序通过在数据库上发布WAITFOR(RECEIVE)语句来等待通知。这意味着每个100个客户机都占用了一个SQL Server工作线程(这是有限的，见max worker线程选项)。我已经看到在生产中使用+1000客户端(在增加了max worker线程选项之后)，但是我建议不要这样做。
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/QueryNotifications来进行一次服务监视更改。然后，该服务会将通知推送给所有正在运行的应用程序，例如使用WCF。您将订阅通用更改(表Foo被更改)，而不是特定的更改(表Foo中的第x行被插入)。
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.
作为一个通用规则，SqlDependency/Query通知只能通知您数据已经更改，但它不会推送新的数据。应用程序必须在收到通知后再次运行查询来刷新其本地数据集。
#2
3
Be careful using SqlDependency class - it has the problems with memory leaks. Hovewer, you can use an open source realization of the SqlDependency class - SqlDependencyEx. It uses a database trigger and native Service Broker notification to receive events about the table changes. This is an usage example:
使用SqlDependency类时要小心——它有内存泄漏的问题。Hovewer可以使用SqlDependency类- SqlDependencyEx的开源实现。它使用数据库触发器和本地服务代理通知接收关于表更改的事件。这是一个用法示例:
int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME))
{
sqlDependency.TableChanged += (o, e) => changesReceived++;
sqlDependency.Start();
// Make table changes.
MakeTableInsertDeleteChanges(changesCount);
// Wait a little bit to receive all changes.
Thread.Sleep(1000);
}
Assert.AreEqual(changesCount, changesReceived);
With SqlDependecyEx you are able to monitor just UPDATE, avoiding DELETE and INSERT. Hope this helps.
使用SqlDependecyEx，您可以监控更新，避免删除和插入。希望这个有帮助。
#3
0
If you don’t update databse manually and all data manipulation are in your application, you should to detect changes in application service layer or business layer instead of db in case you depend on database technology and going to other databases will be difficult. In other hand if you have manual update on db or dependency to db is not important you can use CDC (chane data capture) and push changes into service broker and your application pop changes from service broker then send them to client by bidirectional http communication technology same as SignalR.
如果您不手动更新数据库，并且所有的数据操作都在您的应用程序中，那么您应该检测应用程序服务层或业务层的更改，而不是db，以防止您依赖于数据库技术并访问其他数据库将会很困难。在另一方面,如果你手动更新数据库或依赖db并不重要你可以使用CDC(查恩数据捕获),推动服务代理和应用程序的流行变化从服务代理然后寄给客户通过双向http通信技术SignalR一样。