SQL Server 通知 - 我的 OnChange 不触发
我想利用 SQL Server 通知来捕获 winforms 应用程序中数据库中的插入事件。我正在尝试使用 SQLDependency 对象。 MSDN 文章使这看起来非常简单。所以我创建了一个小示例应用程序来尝试一下。该事件似乎仅在我第一次进入应用程序时才会触发（出现消息框）。将数据插入表中不会引发 OnChange 事件（看起来似乎如此）。有人能告诉我我错过了什么吗？谢谢！
public Main()
{
InitializeComponent();
var check = EnoughPermission();
SqlDependency.Stop(constr);
SqlDependency.Start(constr);
if(connection == null)
{
connection = new SqlConnection(constr);
}
if(command == null)
{
command = new SqlCommand("Select ID, ChatMessage FROM dbo.Chat",connection);
}
connection.Open();
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
command.ExecuteReader();
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
MessageBox.Show("Change!");
}
I would like to make use of SQL Server notifications to capture insert events at my database within a winforms app. I am attempting to use the SQLDependency object. The MSDN articles make this seem pretty straight forward. So I have created a little example application to give it a try. The event only seems to fire as I enter my application the first time(MessageBox appears). Inserting data into the table does not raise the OnChange event it would seem. Can someone tell me what I'm missing? Thanks!
public Main()
{
InitializeComponent();
var check = EnoughPermission();
SqlDependency.Stop(constr);
SqlDependency.Start(constr);
if(connection == null)
{
connection = new SqlConnection(constr);
}
if(command == null)
{
command = new SqlCommand("Select ID, ChatMessage FROM dbo.Chat",connection);
}
connection.Open();
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
command.ExecuteReader();
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
MessageBox.Show("Change!");
}
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当我致力于实现查询通知时，我遇到了确切的问题。我检查了所有配置、代码片段，甚至 TCP 设置，但没有任何帮助。然后，我想出了在数据库上运行以下查询，它解决了我的问题。也许你可以尝试一下。
While I was working in the implementation of query notification, I got the exact problem. I checked all configurations, code pieces, and even TCP settings, but nothing helped. Then, I figured out the following query to run on database and it solved my problem. Maybe you can try it.
您的第一个通知是您将收到的唯一通知。查询通知不是对更改的订阅，一旦触发通知，它也会失效。您应该重新提交新的通知订阅。
如果您的查询立即收到通知，则意味着您没有收到更改通知，而是收到无效查询的通知。检查您收到的 SqlNotificationEventArgs 参数的值。检查要插入/更新的信息删除，勾选Source为Data，检查类型更改。
查看Watcher 应用程序示例以更好地了解了解收到通知后您应该如何重新订阅。要更好地了解查询通知的工作原理，请参阅神秘通知。
Your first notification is the only notification you'll get. Query Notifications are not a subscription for changes, once a notification is fired it is also invalidate. You are supposed to re-submit a new notification subscription.
If your query is notified immedeatly it means you did not get a notification for a change, but one for an invalid query. Check the values of the SqlNotificationEventArgs argument you receive. Check the Info to be Insert/Update/Delete, check the Source to be Data, check the Type to be Change.
Have a look at the Watcher Application example to better understand how you are supposed to re-subscribe when notified. For a better understanding of how the Query Notifications work, see The Mysterious Notification.