Why doesn't SQL Server Service Broker notify me of an insert to a table?

Question

I have set up a list that should be aware to changes to it's data source as follows, more or less:

public class SharedList<T>: ObservableCollection<T> where T: XTimeEntity
{
    private const string DependencyQuery = "select TITLE_ACTIVE, TITLE_NAME from TITLE";
    private readonly SqlDependency _dependency = new SqlDependency();

    public SharedList()
    {
        var connectionString = ConfigurationManager.ConnectionStrings["XTime900Context"].ConnectionString;

        SqlDependency.Stop(connectionString);
        using (var sqn = new SqlConnection(connectionString))
        {
            using (var cmd = new SqlCommand(DependencyQuery, sqn))
            {
                _dependency.AddCommandDependency(cmd);
            }
        }
        _dependency.OnChange += DependencyOnOnChange;
        PopulateList();
        SqlDependency.Start(connectionString);
    }
}

Yet when I executed insert TITLE values (1, 1, 'Mr.') in SSMS, no event fired. Does the change that triggered the event have to be made on a SqlConnection object or something?

first of all you need schema name. Select .. From Schema.TableName. — Jānis, Nov 27 '13 at 07:59
Yes, @Janis, I have since added that and am receiving notifications, but all is not well: [I have another problem] [1]: http://stackoverflow.com/questions/20234287/why-am-i-getting-an-open-data-reader-exception-with-my-sqldependency-subscript — ProfK, Nov 27 '13 at 12:19

score 1 · Answer 1 · answered Nov 24 '13 at 14:22

1

I think you need to execute the command. Read the official docs, they have a sample. SqlDependency is very fragile. There are many usage rules and race conditions involved.

answered Nov 24 '13 at 14:22

usr

168,620
35
240
369

Righ, unless something like `cmd.ExecuteQuery` is issued, no notificaiton is actually set up. – Remus Rusanu Nov 26 '13 at 14:42

Why doesn't SQL Server Service Broker notify me of an insert to a table?

1 Answers1