I'm new to the concept of query notifications with SQL Server and it's going to take some time for me to wrap my head around it.
My objective is to create a Windows service application that is notified when a change has been made to a SQL Server table. I followed this guide which was helpful in getting me started.
However I'm not able to get the expected result. The OnStart()
method in my windows service app looks like so:
protected override void OnStart(string[] args)
{
eventLog1.WriteEntry("Service Started");
serviceRun = false;
SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
try
{
perm.Demand();
eventLog1.WriteEntry("permission granted");
}
catch (System.Exception)
{
eventLog1.WriteEntry("permission denied");
}
try
{
connstr = "Data Source=THSSERVER-LOCAL;Initial Catalog=ET;User ID=mujtaba;Password=ths123";
connection = new SqlConnection(connstr);
SqlCommand command = new SqlCommand("select * from dbo.Customer_FileUploads", connection);
// Create a dependency and associate it with the SqlCommand.
SqlDependency dependency = new SqlDependency(command);
// Maintain the reference in a class member.
// Subscribe to the SqlDependency event.
dependency.OnChange += Dependency_OnChange;
SqlDependency.Start(connstr);
connection.Open();
// Execute the command.
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
//eventLog1.WriteEntry("reading data");
}
}
else
{
eventLog1.WriteEntry("No rows found.");
}
reader.Close();
}
}
catch (Exception e)
{
eventLog1.WriteEntry("Error Message: " + e.Message);
}
}
The event SqlDependency
is subscribed to, looks like so:
private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
// Handle the event.
eventLog1.WriteEntry("data changed");
}
The OnStop()
method looks like so:
protected override void OnStop()
{
SqlDependency.Stop(connstr);
connection.Close();
eventLog1.WriteEntry("In onStop.");
}
I have ENABLE_BROKER
set to true in my database. The end result is, The service runs and the followings logs are created:
"Service Started"
"permission granted"
"data changed"
However when I insert new data into the table, the OnChange()
event doesn't fire and no new log is created. Also when I stop and start the service again, the OnChange()
is triggered even though there was no new data inserted.
Can anyone help me understand the process?
See Question&Answers more detail:
os 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…