SqlDependency not working

2
votes

I'm using EF 6 with SQL Server 2012.

I'm trying to use SqlDependency to refresh my cached data, in a class library (DLL).

I have the following, based on whatever guides I found, but seems like it is not working, and I get no error.

  • enabled broker service on the db
  • created the broker queue, and the service

Using sa login.

I'm testing this whole thing through unit testing code, not sure maybe the notification is not instantaneous, my breakpoint in SqlDependency.OnChange never triggered.

But even if I purposely slow down the post-changes, OnChange is still not triggered.

Once I made relevant data changes, how can I know SQL Server is generating a notification in db?

Update:

Initially I found "master key encryption is required" in SQL log. By creating that key, the log no longer happened, but OnChange is still not triggered.

1
sql-serverentity-framework-6sqldependency

1 Answers

1
votes

After many trials, and based on 1 important article: http://www.codeproject.com/Articles/12335/Using-SqlDependency-for-data-change-events

1) After SqlDependency is initialized, must execute the SqlCommand, can simple just invoke sqlCmd.ExecuteNonQuery().

2) After OnChange event triggered, must remove the event handling, recreate a new SqlCommand and SqlDependency, rebind the event handling, and follow rule #1