We're developing a WPF application using SqlDependencies to update the Application according to the database. We've recognized a few times, that the query notifications seem to be irregular - the application does not show the latest state of the database. As soon as somebody else changes something on the table, the application quickly updates and shows the latest state.
I've now written a tool, that just changes the database as fast as possible for a specific amount of times and another tool that registers the SqlDependencies and just counts how many SqlDependencies have been triggered.
private void AddNewQuery(string query)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var dependency = new SqlDependency(command);
dependency.OnChange += OnQueryChanged;
}
}
private void OnQueryChanged(object sender, SqlNotificationEventArgs e)
{
AddNewQuery('SELECT Foo,Bar FROM dbo.Foobar');
OnDatabaseChanged();
var dependency = sender as SqlDependency;
dependency.OnChange -= OnQueryChanged;
}
private void OnDatabaseChanged()
{
lock(_databaseChangedLocker)
_counter++;
}
Apparently, the faster the main application runs, the less notifications are recognized by the SqlDependency-Reader. For example: I've run 1000 Queries a few times:
9.2 seconds total run time results in 520 notifications received (52%) 14 seconds total run time results in 822 notifications (82.2%) 19.3 seconds total run time results in 957 notifications (95.7%).
Is there a way to prevent this kind of dataloss and ensure, that every change results in a QueryNotification? We're running a SQL Server 2008 R2.
