CODEWITHSUNDEEP
c#sqlsql-server
Abs
Abs

Reputation: 165

Why does my SqlDependancy OnChange() event not fire?

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:

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

Upvotes: 1

Views: 226

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46213

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.

Upvotes: 3

Ricardo Peres
Ricardo Peres

Reputation: 14535

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.

Upvotes: 0

Related Questions