SqlDependency Only Fires On Subscribe

Question

I'm trying to make use of SqlDependancy in a SignalR project, but I can't seem to get the OnChanged event to fire more than once. It fires initially on the subscribe event, but it never fires again after making changes to the underlying database. I've omitted my SignalR and controller code because the problem seems to lie in the repository class. SqlDependancy.Start() is declared in my Global.asax class.

Watching from the SQL server, I can see a notification queue is created when my application starts, and is terminated when I close as well.

    public IEnumerable<Visitor> NotifyAllClients()
    {
        List<Visitor> visitors = new List<Visitor>();
        using (var connection = new SqlConnection(new VisitorLogEntities().Database.Connection.ConnectionString))
        {
            using (var command = new SqlCommand(@"SELECT * FROM dbo.Visitors", connection))
          //using (var command = new SqlCommand(@"SELECT [Id],[AgreeToTerms],[Base64Image],[CheckInDate],[CheckOutTime],[Company],[CountryOfOrigin],[email],[FirstName],[LastName],[IsInBuilding],[MeetingSubject],[MeetingTime],[PatriotHost],[phone],[title] FROM dbo.Visitors", connection))
            {

                var dependency = new SqlDependency(command);
                dependency.OnChange += Database_OnChange;

                if (connection.State == System.Data.ConnectionState.Closed)
                    connection.Open();

                var reader = command.ExecuteReader();

                while (reader.Read())
                {
                 ////compile visitor objects
                 ////visitors.add(new Visitor());
                }
            }
            return visitors.OrderByDescending(x => x.CheckInDate);
        }
    }

    private void Database_OnChange(object sender, SqlNotificationEventArgs e)
    {
        //var dependency = (SqlDependency)sender;
        //dependency.OnChange -= Database_OnChange;

        ////this fires once, with the Type of 'Subscribe', but then never fires on CRUD changes
        if (e.Type == SqlNotificationType.Change)
        {
            VisitorHub.SendVisitors();
        }
        //NotifyAllClients();
    }

edit: lines of code commented out above indicate the changes needed to get this working correctly.

Answer 1

Check this example from msdn http://msdn.microsoft.com/en-US/library/a52dhwx7(v=vs.80).aspx. Download the VS2005_General_en-us.pdf. Page 24636, "Using SqlDependency in a Windows Application" is the section the original link led to. Pay particular attention to step 12 and 13 in the watcher application. In step 12 you will see the removal of the onChange event and then it calls step 13 which sets it up again.

Also, I think you are seeing bad behavior due to your sql statement itself. The sql statement has to follow some rules. See https://technet.microsoft.com/en-us/library/ms181122(v=sql.105).aspx for more info. In particular the Writing Notification Queries section. "The statement may not use the asterisk (*) or table_name.* syntax to specify columns."