CODEWITHSUNDEEP
c#.netado.netservice-broker
ProfK
ProfK

Reputation: 51064

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

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?

Upvotes: 1

Views: 275

Answers (1)

usr
usr

Reputation: 171178

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.

Upvotes: 1

Related Questions