用sqlDependency来监控数据库是否有更新,数据库是本机的,也没有用任何程序进行更改,
测试代码也是在网上找的公用代码,但是sqlDependency的onChange事件总是一直不停的触发,就大神帮忙解决一下。
代码如下:
class Program
{
private static string _connStr= "data source=.;initial catalog=isa;uid=sa;pwd=zxcvbnm;"; static void Main(string[] args)
{
SqlDependency.Start(_connStr);//传入连接字符串,启动基于数据库的监听
UpdateGrid(); Console.Read(); } private static void UpdateGrid()
{
using (SqlConnection connection = new SqlConnection(_connStr))
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand("select FLTID,LEGNO,DEPSTN,ARRSTN,[STATUS] From [dbo].[FLIGHTS] where fltid<10", connection))
{
command.CommandType = CommandType.Text;
connection.Open();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); SqlDataReader sdr = command.ExecuteReader();
Console.WriteLine();
while (sdr.Read())
{
Console.WriteLine("FLTID:{0}\t LEGNO:{1}\t DEPSTN:{2}\t ARRSTN:{3}\t STATUS:{4}", sdr["FLTID"].ToString(), sdr["LEGNO"].ToString(), sdr["DEPSTN"].ToString(), sdr["ARRSTN"].ToString(), sdr["STATUS"].ToString());
}
sdr.Close();
}
}
} private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
//SqlDependency dependency = sender as SqlDependency;
//dependency.OnChange -= dependency_OnChange;
UpdateGrid();
} }
测试代码也是在网上找的公用代码,但是sqlDependency的onChange事件总是一直不停的触发,就大神帮忙解决一下。
代码如下:
class Program
{
private static string _connStr= "data source=.;initial catalog=isa;uid=sa;pwd=zxcvbnm;"; static void Main(string[] args)
{
SqlDependency.Start(_connStr);//传入连接字符串,启动基于数据库的监听
UpdateGrid(); Console.Read(); } private static void UpdateGrid()
{
using (SqlConnection connection = new SqlConnection(_connStr))
{
//依赖是基于某一张表的,而且查询语句只能是简单查询语句,不能带top或*,同时必须指定所有者,即类似[dbo].[]
using (SqlCommand command = new SqlCommand("select FLTID,LEGNO,DEPSTN,ARRSTN,[STATUS] From [dbo].[FLIGHTS] where fltid<10", connection))
{
command.CommandType = CommandType.Text;
connection.Open();
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange); SqlDataReader sdr = command.ExecuteReader();
Console.WriteLine();
while (sdr.Read())
{
Console.WriteLine("FLTID:{0}\t LEGNO:{1}\t DEPSTN:{2}\t ARRSTN:{3}\t STATUS:{4}", sdr["FLTID"].ToString(), sdr["LEGNO"].ToString(), sdr["DEPSTN"].ToString(), sdr["ARRSTN"].ToString(), sdr["STATUS"].ToString());
}
sdr.Close();
}
}
} private static void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
//SqlDependency dependency = sender as SqlDependency;
//dependency.OnChange -= dependency_OnChange;
UpdateGrid();
} }
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);但是更好的办法是搞明白实际应该暴露的方法是什么(不应该暴露的方法应该声明为private等等),正确设计流程。
我是要一直监听数据库库的表变化啊
关键是现在数据库里面的数据没变化,就不应该会到dependency_OnChange方法里面,这个问题出在哪儿??
{
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= dependency_OnChange;
RegisterOnChangeEvent();
}只“+”不“-”?
另外
using (SqlCommand command = new SqlCommand("select FLTID,LEGNO,DEPSTN,ARRSTN,STATUS From dbo.FLIGHTS where fltid<10", connection))
public partial class Form1 : Form
{
private string conStr = "Data Source=.;Database=AdventureWorks;Uid=sa;Pwd=wxh;Pooling=true;Connect Timeout=60;";
private string cmdStr = "SELECT ProductID, [Name], ProductNumber, Color, SafetyStockLevel, ListPrice FROM Production.Product";
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
SqlDependency.Start(conStr);
UpdateData();
}
private void UpdateData()
{
SqlDataAdapter da = new SqlDataAdapter(cmdStr, conStr);
SqlDependency dependency = new SqlDependency(da.SelectCommand);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
DataTable dt = new DataTable();
da.Fill(dt);
this.dataGridView1.Invoke((MethodInvoker)delegate
{
this.dataGridView1.DataSource = dt;
});
}
private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
if (e.Info == SqlNotificationInfo.Insert ||
e.Info == SqlNotificationInfo.Update ||
e.Info == SqlNotificationInfo.Delete)
{
UpdateData();
}
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
SqlDependency.Stop(conStr);
}
}没更改之前的cmdStr是:
private string cmdStr = "SELECT ProductID, [Name], ProductNumber, Color, SafetyStockLevel, ListPrice FROM AdventureWorks.Production.Product";
dataGridView1就一直在闪。
//{
// UpdateGrid();
//}你这样 能触发? 我测试不可能的,人家本来就要不断的查询