I'm trying to make a C# Forms program that views data from a database. It needs to request data on startup, cache it, then update it if the DB is updated. I'm trying to do it using SqlDependency. Here's the code:
private const string connectionString = "Data Source=DESKTOP-VT1F04F\\MSSQLSERVER14;Initial Catalog=test1;Trusted_Connection=True";
private void button1_Click(object sender, EventArgs e)
{
SqlDependency.Stop(connectionString);
SqlDependency.Start(connectionString);
ExecuteWatchingQuery();
}
private void ExecuteWatchingQuery()
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand("select nbase, name from dbo.filial", connection);
var sqlDependency = new SqlDependency(command);
sqlDependency.OnChange += new OnChangeEventHandler(OnDatabaseChange);
command.ExecuteReader();
}
}
private void OnDatabaseChange(object sender, SqlNotificationEventArgs args)
{
//MessageBox.Show("?");
SqlNotificationInfo info = args.Info;
if (SqlNotificationInfo.Insert.Equals(info)
|| SqlNotificationInfo.Update.Equals(info)
|| SqlNotificationInfo.Delete.Equals(info))
{
MessageBox.Show("!");
//todo
}
ExecuteWatchingQuery();
}
It doesn't do anything. ExecuteWatchingQuery competes fully, but OnDatabaseChange never fires. However, if I replace database test1 with a freshly created database watcher_test with a freshly created table, it works as intended. I have tried the following SQL commands:
alter database test1 set enable_broker
CREATE QUEUE ContactChangeMessages;
CREATE SERVICE ContactChangeNotifications ON QUEUE ContactChangeMessages
Both databases have the same owner (as select name, suser_sname(owner_sid) from sys.databases shows), same permissions, they're on the same server, and I can't see any difference between them, in settings, or anywhere else. The program isn't able to access any table in the first database, but is able to access a copy of a table done with insert into from the first into the second table. The program behaves identically on a different computer that uses the same database restored from a backup.
