Init SqlDependency and notify changes

Question

I need something telling to my app when someone has updated data in the database. If I don't have misunderstood, SqlDependancy is what I need. I have followed this tutorial and write this code:

class dbListener
{
    public dbListener() 
    {
        Debug.WriteLine(MainWindow.dbContext.Database.Connection.ConnectionString + "Password=12345;");

        SqlDependency.Start(MainWindow.dbContext.Database.Connection.ConnectionString + "Password=12345;");
        connection = new SqlConnection(MainWindow.dbContext.Database.Connection.ConnectionString + "Password=12345;");
        connection.Open();
        SomeMethod();
    }
    SqlConnection connection;
    void SomeMethod()
    {
        // Assume connection is an open SqlConnection.
        // Create a new SqlCommand object.

        //{
            using (SqlCommand command = new SqlCommand("SELECT * FROM dbo.ArchivioErogazioni", connection))
            {
                // Create a dependency and associate it with the SqlCommand.
                SqlDependency dependency = new SqlDependency(command);
                // Maintain the refence in a class member.
                // Subscribe to the SqlDependency event.
                dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
                // Execute the command.
                command.ExecuteReader();
          //  }
        }
    }
    // Handler method
 void OnDependencyChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change) 
        { 
            // Handle the event (for example, invalidate this cache entry).
            MessageBox.Show("ikjkjkj");
            Debug.WriteLine("fkldjkfjklgjf");
            SqlDependency dependency = (SqlDependency)sender;
            dependency.OnChange -= OnDependencyChange; 
            SomeMethod();
        }
    }

    void Termination()
    {
        // Release the dependency.
        SqlDependency.Stop(MainWindow.GetConnectionString("Model"));
    }

}

but it doesn't work. I mean, it runs without errors, but when I try to test it, updating some values from the SQL Server 2008 Management Studio, nothing happens. I have put a breakpoint in the function that would manage the event, but it is fired only in the init phase.

Have I done mistakes? How can I reach my goal?

Answer 1

but it is fired only in the init phase

It fires because your query notification subscription is invalid. You must inspect the SqlNotificationEventArgs members. Only the Change type is a notification for change, you are probably getting a Subscribe type with Statement source. Your query does not meet the criteria described in Creating a Query for Notification:

• The statement may not use the asterisk (*) or table_name.* syntax to specify columns.