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.