Hi Niels,
thanks for your answer!
Post by Niels BerglundPost by FlorianI've got a little trouble because updates seem to trigger
SqlDependencies even if the update is already finished.
I don't understand what you mean with this. Isn't the idea that an
update should fire the dependency?
Well, yes, but not if I initiate the Trigger *after* the update.
Post by Niels BerglundPost by FlorianI'm watching data with an SqlDependency which I eventually update
- Release current SqlDependency
- Update Data
- Read data again to aquire a new SqlDependency
Shortly after acquiring the new SqlDependency it fires its change
event for the preceding update statement.
You are releasing the dependency - what do you mean with that? It'd help
if you could post a sample scenario which shows the behavior you are
talking about.
Below I've pasted a quick example to reproduce it. I've used a Form with two
buttons, clicking Button1 will insert a new row and afterwards call a Select
on the table with an attached SqlDependency.
When you click Button2(without the Thread.Sleep) you will receive the
changed event - right *after* the update, which is exactly my problem(or at
least it's what I experience in my environment). You can click Button2
repeatedly which simulates more or less what my Program does.
If you put the Thread.Sleep in it's allright, though.
And it's not due to the ExecNonQuery because originally I'm using a
TableAdapter which doesn't use ExecNonQuery afaik.
Thanks so far,
Florian
Table Structure:
CREATE TABLE [dbo].[Table1](
[ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Table1_ID] DEFAULT
(newid()),
[name] [nvarchar](50) COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Collections;
using System.Data.SqlClient;
namespace SqlDependencyUpdateBugSample
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//variables
private const string CONSTRING = "Data Source=local;Initial
Catalog=Test;User ID=sa";
private Guid guid;
private DataSet1 ds = new DataSet1();
private SqlDependency dep = null;
private void button1_Click(object sender, EventArgs e)
{
//get a connection
SqlConnection con = new SqlConnection(CONSTRING);
con.Open();
//insert a new row
guid = Guid.NewGuid();
SqlCommand cmd = new SqlCommand("INSERT INTO Table1 VALUES ('" +
guid.ToString() + "', 'Hello World')", con);
cmd.ExecuteNonQuery();
//do a select on the table
cmd.CommandText = "SELECT ID, name FROM dbo.Table1";
//with an assigned SqlDependency
this.dep = new SqlDependency(cmd);
this.dep.OnChange += new OnChangeEventHandler(dep_OnChange);
cmd.ExecuteNonQuery();
con.Close();
}
void dep_OnChange(object sender, SqlNotificationEventArgs e)
{
MessageBox.Show("Changed");
}
private void button2_Click(object sender, EventArgs e)
{
//"release" dependency
this.dep.OnChange -= new OnChangeEventHandler(dep_OnChange);
this.dep = null;
SqlConnection con = new SqlConnection(CONSTRING);
con.Open();
//update the row - at this point we won't get any notifications
because the old dep is gone
SqlCommand cmd = new SqlCommand("UPDATE Table1 SET
name='Testing...' WHERE ID = '" + guid.ToString() + "'", con);
cmd.ExecuteNonQuery();
//System.Threading.Thread.Sleep(1000); //sleep a little so the
update can get through
//re-acquire the SqlDependency
//without the Thread.Sleep this dependency object will fire
shortly after the Execute which
cmd.CommandText = "SELECT ID, name FROM dbo.Table1";
this.dep = new SqlDependency(cmd);
this.dep.OnChange += new OnChangeEventHandler(dep_OnChange);
cmd.ExecuteNonQuery();
con.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
SqlDependency.Start(CONSTRING);
}
private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
SqlDependency.Stop(CONSTRING);
}
}
}