SqlDependency not firing

Question

I am trying SqlDepenedency for the first time. I am not getting any notifications on Database Update.

I am placing breakpoints inside : OnChange(object sender, SqlNotificationEventArgs e),

but it never gets hit.

Here is my code :

    protected void Page_Load(object sender, EventArgs e)
    {

        Label1.Text = "Cache Refresh: " + DateTime.Now.ToLongTimeString();
        DateTime.Now.ToLongTimeString();
        // Create a dependency connection to the database.
        SqlDependency.Start(GetConnectionString());

        using (SqlConnection connection = new SqlConnection(GetConnectionString()))
        {
            using (SqlCommand command = new SqlCommand(GetSQL(), connection))
            {

                SqlDependency dependency =
                        new SqlDependency(command);

                    // Refresh the cache after the number of minutes
                    // listed below if a change does not occur.
                    // This value could be stored in a configuration file.
                                  connection.Open();

                dgHomeRequests.DataSource = command.ExecuteReader();
                    dgHomeRequests.DataBind();

            }
        }   
    }


    private string GetConnectionString()
    {
        // To avoid storing the connection string in your code,
        // you can retrieve it from a configuration file.
        //return "Data Source=(local);Integrated Security=true;" +"Initial Catalog=AdventureWorks;";
        return ConfigurationManager.ConnectionStrings["TestData"].ConnectionString;
    }
    private string GetSQL()
    {
        return "Select [Address] From [UserAccount1]";
    }


    void OnChange(object sender, SqlNotificationEventArgs e)
    {
        // have breakpoint here:
        SqlDependency dependency = sender as SqlDependency;

        // Notices are only a one shot deal
        // so remove the existing one so a new 
        // one can be added

        dependency.OnChange -= OnChange;

        // Fire the event
       /* if (OnNewMessage != null)
        {
            OnNewMessage();
        }*/
    }

I have also placed some code in Global.asax file :

public class Global : HttpApplication
{
    void Application_Start(object sender, EventArgs e)
    {
        // Code that runs on application startup
        RouteConfig.RegisterRoutes(RouteTable.Routes);
        BundleConfig.RegisterBundles(BundleTable.Bundles);
        SqlDependency.Start(ConfigurationManager.ConnectionStrings["TestData"].ConnectionString);
    }
    protected void Application_End()
    {
        // Shut down SignalR Dependencies
        SqlDependency.Stop(ConfigurationManager.ConnectionStrings["TestData"].ConnectionString);
    }
}

The SQL server is on local machine. I am running the code through Visual Studio(IIS Express).

1. To enable service broker on database:

   ALTER DATABASE SET ENABLE_BROKER GO

2. To subscribe query notification, we need to give permission to IIS service account

   GRANT SUBSCRIBE QUERY NOTIFICATIONS TO “<serviceAccount>”
   

I guessed the 2nd point is not needed as it is local. But I tried giving it some permissions. Don't know if they are right as I don't think it is using app pool.And don't need the permission on local env. if I am the user myself and created the schema myself.

One of the questions that I saw was granting :

alter authorization on database::<dbName> to [sa];

I gave that permission too.

Answer 1

I was missing : dependency.OnChange += new OnChangeEventHandler(OnChange); The new code would look like :

               SqlDependency dependency = new SqlDependency(command);

               dependency.OnChange += new OnChangeEventHandler(OnChange);

now I can fire void OnChange(object sender, SqlNotificationEventArgs e)