This is the first time I've ever needed to use an SqlDependency so I am hoping that its a stupid mistake I've made.
The problem I'm having is that the OnChanged event doesn't fire when the sql table changes. No errors or anything just it doesn't fire.
Here is the code
public class SqlWatcher
{
private const string SqlConnectionString = "Data Source = CN-PC08\\DEV; Initial Catalog=DEP; User = sa; Password=******";
public SqlWatcher()
{
SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
perm.Demand();
SqlCommand cmd = new SqlCommand("SELECT [DataAvaliable], [RowNumber] FROM [dbo].[Trigger]", new SqlConnection(SqlConnectionString));
SqlDependency sqlDependency = new SqlDependency(cmd);
sqlDependency.OnChange += On_SqlBitChanged;
}
private void On_SqlBitChanged(object sender, SqlNotificationEventArgs sqlNotificationEventArgs)
{
SqlDependency dependency = (SqlDependency)sender;
dependency.OnChange -= On_SqlBitChanged;
// Fire the event
if (NewMessage != null)
{
NewMessage(this, new EventArgs());
}
}
public void Start()
{
SqlDependency.Start(SqlConnectionString);
}
public void Stop()
{
SqlDependency.Stop(SqlConnectionString);
}
public event EventHandler NewMessage;
And in my main window I have this
public partial class MainWindow : Window
{
public MainWindow()
{
InitializeComponent();
try
{
SqlWatcher sqlWatcher = new SqlWatcher();
sqlWatcher.Start();
sqlWatcher.NewMessage += On_NewMessage;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
private void On_NewMessage(object sender, EventArgs eventArgs)
{
MessageBox.Show("Message Received");
}
}
So the expected behaviour is that if I run the following sqlQuery a messageBox will be displayed saying "Message Received"
INSERT INTO [DEP].[dbo].[Trigger] Values(0,3)
Could anyone give me a hint on what to check/change?
I'm aware that only a subset of Sql features can be used in dependencies but I don't think I'm trying to do anything to fancy here.
I'm hoping that its a stupid mistake I've made.
Unfortunately (or fortunately?) you are making several mistakes.
First is you need to understand that Query Notifications will invalidate one query. So you will only be notified at most once and you have to re-subscribe again (re-submit the query) if you want to receive further notifications.
Next you need to understand that you will be notified for any reason, not only for changes. In your callback you must check the reason you're notified, which are passed in via the SqlNotificationEventArgs
.
Next you need to understand asynchronous programming basic principles: if you subscribe for an event make sure you subscribe before the event can happen first time. Case in point: the On_SqlBitChanged
can fire as soon as you submit the query. This should happen in the SqlWatcher.SqlWatcher
constructor, but you subscribe to the sqlWatcher.NewMessage
after the constructor runs. On_SqlBitChanged
can be invoked between the constructor finishes before you hook up the NewMessage
event callback in which case the notification is silently ignored.
If you want to use a service make sure you start it before you use it. You are using SqlDependency in SqlWatcher.SqlWatcher
but you start it after that when you call SqlWatcher.Start()
.
Finally, if you want to be notified of changes on a query you have to submit the query. You are constructing the SqlCommand
object, set up the notification and then... discard the object. Unless you actually submit the query, you did not yet subscribed to anything.
Suggestions for fix:
Start
and Stop
statics, call Start
in application start up.NewMessage
before you submit the querySqlComamnd.ExecuteQuery()
)Info
, Type
and Source
in the On_SqlBitChanged
callback, if your submission contains an error this is the only way to learn (the SqlComamnd.ExecuteQuery() will succeed even if the notification request is invalid)One more thing: don't invoke UI code in background callbacks. You cannot call MessageBox.Show("Message Received");
from a callback, you must route through the form main thread via Form.Invoke
. YEs, I know that strictly speaking MessageBox.Show
does work on a non-UI thread but you will soon move away from alert boxes to actually form interaction and then things will break.