SqlDependency in ASP.NET

Question

I created the following encapsulation on an SQL Dependency object:

public class DependencyTracker { private SqlDependency _SQLDependency = null; public string ConnectionString { get; private set; } public string CommandNotifier { get; private set; } public delegate void Refresh(); public event Refresh OnRefresh; public DependencyTracker(string connectionString, string commandNotifier) { ConnectionString = connectionString; CommandNotifier = commandNotifier; } public void StartDependency() { SqlDependency.Start(ConnectionString); } public void StopDependency() { SqlDependency.Stop(ConnectionString); } public void TrackForChanges() { using (SqlConnection sqlConn = new SqlConnection(ConnectionString)) { sqlConn.Open(); using (SqlCommand sqlCommand = new SqlCommand(CommandNotifier, sqlConn)) { sqlCommand.CommandType = CommandType.StoredProcedure; _SQLDependency = new SqlDependency(sqlCommand); _SQLDependency.OnChange += new OnChangeEventHandler(dependency_OnChange); sqlCommand.ExecuteReader(); } } } void dependency_OnChange(object sender, SqlNotificationEventArgs e) { SqlDependency sqlDependency = (SqlDependency)sender; sqlDependency.OnChange -= dependency_OnChange; if (OnRefresh != null) { OnRefresh(); } } public bool HasChanges { get { return _SQLDependency.HasChanges; } } } 

This is not an original work based on this text. From my repository, I do the following:

 public bool Updatexxx(Ixxx xsxs) { try { SqlConnection sqlConn = new SqlConnection(_ConnectionString); sqlConn.Open(); ... bool result = sqlComm.ExecuteNonQuery() == 1; _ResetEvent.WaitOne(); return result; } catch ... catch ... } 

Callback

 public void RefreshData() { FindAllxxx(); _ResetEvent.Set(); } 

and

 public HashSet<Iddd> Finddadas() { DependencyTracker tracker = _Container.Resolve<DependencyTracker>("dada"); UnityHashSet<IT24Route> hash = _Container.Resolve<UnityHashSet<dadas>>("Tdsadas"); if (tracker.HasChanges || hash.Count == 0) { hash = new UnityHashSet<dsda>(_Container); hash.ImportHashSet(FindAlldsdsNonCached()); _Container.RegisterInstance<UnityHashSet<dsds>>("dasda", hash); tracker.TrackForChanges(); } return hash; } 

and FindAllXXXNonCached () makes a real choice from the database. As you can see, I cache everything. My question is why this does NOT work. Symptom: from the dependecy tracker, the callback is called twice, then it is blocked. I implemented this because my notifications appeared after the page was refreshed. I tried to put a manual reset event to give a notification, and then set a manual reset event and update the user interface. As I said, after going through OnChange twice, it freezes. What can I do? Dependency Tracker - an instance registered in the Unity container, the repository is registered by type.

Answer 1

 void dependency_OnChange(object sender, SqlNotificationEventArgs e) 

It is expected that you check SqlNotificationEventArgs and find out why you received a notification, data change, or something else. Check Info as Insert / Update / Delete , Check Source as Data . Check Type as Change .

Most likely, your request immediately signals that it does not comply with the restrictions imposed on notification of requests . Yes, I know that the link points to indexed views, and if you want to understand why, read The Mysterious Notification .

You also have a race condition between your update pending _ResetEvent and the _ResetEvent callback. T1 causes an update. Meanwhile, an unrelated update occurs in the data, and callbacks are called. The _ResetEvent parameter is set. T1 completes the update and waits for _ResteEvent, which is signaled, so it continues. The caller assumes that a callback for his own update has occurred and the cache has been updated, but this is not true.

The second serious problem is that the code is incorrect in the presence of transactions. UpdateXXX assumes that a callback for its own update will occur immediately and wait for it. The request notification will be sent by the engine only after the update has been fixed, therefore, when TransactionScope is present, the UpdateXXX method will block waiting for the notification, which cannot appear until UpdateXXX returns (live deadlock).

It is also unclear what the purpose of TrackForChanges is. You read SqlDataReader (sqlCommand.ExecuteReader), but ignore the result. With a notification of requests, you send a request, read the result of itse , and you will be notified when this result has changed.

And finally, it never reads the data in the SqlDependency notification callback .