SQLDependency_OnChange-Event仅触发一次 [英] SQLDependency_OnChange-Event fires only one single Time
问题描述
我正在使用SQLDependency来通知我数据库中是否有更改.程序启动后,它可以正常工作.当我进行第一次更改时,将触发该事件.哇,太好了.但是,如果我进行了第二次更改,则该事件不会再次触发.我搜索了我认为的所有网络,但未找到有关此问题"的任何信息.仅发现OnChange-Event在循环中触发的问题.谁能帮我吗?
I'm working with SQLDependency to notify me if there is a change in the Database. After Program Start-Up it works just fine. When I make a first change the Event fires. Wohoo... that's great. But if I made a second change the event doesn't fire again. I've searched all the web I think but haven't found anything about THIS Problem. Only found problems where the OnChange-Event fires in a Loop. Can anyone help me?
这里有一段代码:
private void GetStates()
{
if (!DoesUserHavePermission())
return;
SqlDependency.Stop(con);
SqlDependency.Start(con);
using (SqlConnection cn = new SqlConnection(con))
{
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT Bla, Bla2, ..FROM dbo.[BLA3]"
cmd.Notification = null;
cmd.Dispose();
SqlDependency dep = new SqlDependency(cmd);
dep.OnChange += new OnChangeEventHandler(dep_OnChange);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
state.Clear(); //In this Case "state" is a List<string>
while (dr.Read())
{
state.Add(dr.GetString(0) + "|" + dr.GetInt32(3));
}
dr.Dispose();
dr.Close();
}
}
}
}
我的OnChange-Event看起来像这样:
my OnChange-Event looks like this:
private void dep_OnChange(object sender, SqlNotificationEventArgs e)
{
SqlDependency dep = sender as SqlDependency;
dep.OnChange -= this.dep_OnChange;
using (SqlConnection cn = new SqlConnection(con))
{
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT Bla, Bla2, ..FROM dbo.[BLA3]";
cmd.Notification = null;
if (e.Type == SqlNotificationType.Change)
{
if (cn.State != ConnectionState.Open)
{
cn.Open();
}
using (SqlDataReader dr = cmd.ExecuteReader())
{
state.Clear(); // Clear and Refill the stringlist "state"
while (dr.Read())
{
state.Add(dr.GetString(0) + "|" + dr.GetInt32(3));
}
}
}
cn.Close();
}
}
this.GetStates(); //to go ahead and wait for a new change
}
问题出在哪里?
推荐答案
我也遇到了这个问题.您需要创建一个新的SqlDependency实体(在OnChange事件中取消现有的实体的订阅后),然后运行新的ExecuteReader命令.我从这篇文章中得到了这个主意:
I was running into this issue as well. You need to create a new SqlDependency entity (after unsubscribing the existing one from the OnChange event) and then run a new ExecuteReader command. I got the idea from this post:
http://www.codeproject.com/Articles/12335/使用-SqlDependency-for-data-change-events
这通常是有道理的,因为一旦收到更改通知,您通常将希望重新查询数据.
This usually makes sense, as once you have been notified of a change you will normally want to re-query the data.
这篇关于SQLDependency_OnChange-Event仅触发一次的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!