Why does my SqlDependancy OnChange() event not fire?

Question

I am trying to implement a SqlDependancy to fire events when a change is made to one of the tables in MySQL server database, however the event does not seem to fire when I make alterations to the table through SSMS, why could this be?

My implmentation below is inline with Microsofts tuturiol:

public int GetVehicleCount(bool monitorCount)
{
      int count;          
      string query = "SELECT AREA_ID, VEHICLE_COUNT, ADDED_ON FROM CAPACITY_LOG";

      using (var sql = DBClass.Instance.OpenSqlConn())
      using (var cmd = new SqlCommand(query, sql))
      {                  
           SqlDependency sqlDependancy = new SqlDependency(cmd);

           sqlDependancy.OnChange += new ChangeEventHandler(VehicleCount_Changed);

            using (var reader = cmd.ExecuteReader())
            {                            
                //Do something    
            }       

            return 0;
     }
}

private void VehicleCount_Changed(object sender, SqlNotificationEventArgs e)
{
     //throw new NotImplementedException();            
}

I have made sure of the following:

The connection to the database can be opened.
The database has the Service-Broker enabled.
The SqlDependancy has been started.
No exceptions are being thrown during runtime

Note that the event fires once shortly after it has been subscribed to and never again, below are the values of the event args:

@DanielTshuva do you mean that because the sql dependancy is declared within the using block it is being removed by the garbage collecter after that block is executed? — Abs, Apr 04 '18 at 12:24
@Abs, you need to resubscribe after the notification is fired to get notified again. What are the values of the initial SqlNotificationEventArgs? Perhaps it erred. — Dan Guzman, Apr 04 '18 at 12:31
@DanGuzman Please see the image in the update, not entirely sure what the values of those properties are spoused to indicate yet — Abs, Apr 04 '18 at 12:44
The info `Invalid` means the SQL statement isn't valid for notifications. You must use 2-part names, like `dbo.CAPACITY_LOG`. See https://stackoverflow.com/questions/7588572/what-are-the-limitations-of-sqldependency — Dan Guzman, Apr 04 '18 at 12:55
@DanGuzman Thanks that did the trick, it does seem like I have to resubscribe, not sure how to do that without making the sql dependancy object a property of the class. If you create an answer i'll gladly accept it. — Abs, Apr 04 '18 at 13:16

score 3 · Accepted Answer · answered Apr 04 '18 at 13:50

Queries with a SqlDependency have a number of requirements. When a query is executed that doesn't meet these requirements, the ChangeEventHandler fires immediately with Invalid in SqlNotificationEventArs.Info.

In this case, the query is invalid because the table name was not schema-qualified. Specify a 2-part name so that the query is valid for notifications. This example assumes the dbo schema:

string query = "SELECT AREA_ID, VEHICLE_COUNT, ADDED_ON FROM dbo.CAPACITY_LOG";

This change should result in a valid SqlDependency. Note that when the ChangeEventHander is invoked, the normal pattern is to execute the query again with SqlDependency, which will get the latest data and re-subscribe to change notifications.

score 0 · Answer 2 · answered Apr 04 '18 at 12:32

0

The connection is disposed of, so it stops receiving input from the database. I have a code sample available at https://weblogs.asp.net/ricardoperes/broadcasting-database-changes-through-signalr that does work.

answered Apr 04 '18 at 12:32

Ricardo Peres

13,724
5
57
74

Why does my SqlDependancy OnChange() event not fire?

2 Answers2