更新后的代码,始终读取最后更新的数据
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
private SqlConnection sqlcon = new SqlConnection("Data Source=192.168.1.102;user id=sa;password=sa;Initial Catalog=JG;Pooling=False;");
private SqlCommand sqlcom = new SqlCommand();
public Form1()
{
InitializeComponent();
Control.CheckForIllegalCrossThreadCalls = false;
SqlDependency.Start(sqlcon.ConnectionString);
}
private void Form1_Load(object sender, EventArgs e)
{
sqlcon.Open();
sqlcom.Connection = sqlcon;
update();
}
void OnDependencyChange(object sender, SqlNotificationEventArgs e)
{
// Handle the event (for example, invalidate this cache entry).
update();
}
private void Form1_FormClosed(object sender, FormClosedEventArgs e)
{
SqlDependency.Stop(sqlcon.ConnectionString);
}
private void update()
{
SqlCommand command1 = new SqlCommand("SELECT ID FROM dbo.gz", sqlcon);
SqlCommand command2 = new SqlCommand("SELECT ID FROM dbo.xm", sqlcon);
SqlDependency dependency = new SqlDependency();
dependency.AddCommandDependency(command1);
dependency.AddCommandDependency(command2);
dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
command1.ExecuteNonQuery();
command2.ExecuteNonQuery();
//根新界面,这里始终读取表最后更新的数据
sqlcom.Parameters.Clear();
sqlcom.CommandText = "SELECT 工资 from gz where ID=17";
object j = sqlcom.ExecuteScalar();
label2.Text = j.ToString();
for (int i = 0; i < 9999; i++) { label1.Text = i.ToString(); }
//////
}
private void button1_Click(object sender, EventArgs e)
{
sqlcom.CommandText = "select * from xm";
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(sqlcom);
da.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
在多次通知的情况下即数据库改变多次的情况,则只执行一次
for (int i = 0; i < 9999; i++) { label1.Text = i.ToString(); }
OnDependencyChange不能访问UI线程,所以在窗体构造函数中使用
Control.CheckForIllegalCrossThreadCalls = false;
SqlDependency不影响其他数据库操作,界面更新会阻塞OnDependencyChange
private void button1_Click(object sender, EventArgs e) { sqlcom.CommandText = "select * from xm"; DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(sqlcom); da.Fill(dt); dataGridView1.DataSource = dt; }