I am a little confused with SQLDependency.
I have a C# WPF application. I need to notify any update in a specific table to other PC's users.
I googled and find SQL Dependency.
What I understand from this topic is the SQLDependency will notify if there is a change.
But when I run the project, it hits permenantly the newMessage event.
Am I missing something or this is the expected behavior?
Is there a way to get notifications only if there is an update in the related table ?
To apply this solution I created a class like:
public class SQLNotifier : IDisposable
{
public string ConnectionString
{
get
{
return Tools.LocalConnection;
}
}
public SqlCommand CurrentCommand { get; set; }
private SqlConnection connection;
public SqlConnection CurrentConnection
{
get
{
this.connection = this.connection ?? new SqlConnection(this.ConnectionString.Replace("App=EntityFramework", ""));
return this.connection;
}
}
public SQLNotifier()
{
SqlDependency.Start(this.ConnectionString);
}
private event EventHandler<SqlNotificationEventArgs> _newMessage;
public event EventHandler<SqlNotificationEventArgs> NewMessage
{
add
{
this._newMessage += value;
}
remove
{
this._newMessage -= value;
}
}
public virtual void OnNewMessage(SqlNotificationEventArgs notification)
{
if (this._newMessage != null)
this._newMessage(this, notification);
}
public DataTable RegisterDependency()
{
this.CurrentCommand = new SqlCommand("SELECT " +
"Select_PractitionerID " +
"FROM " +
"CurrentSelection " +
"WHERE " +
"Select_PractitionerID = @PracID AND " +
"Select_PCName <> @PCName AND Select_Location IS NOT NULL",
this.CurrentConnection);
this.CurrentCommand.Notification = null;
CurrentCommand.Parameters.AddWithValue("@PracID", Tools.CurrentPrat.Prac_ID);
CurrentCommand.Parameters.AddWithValue("@PCName", Tools.PCName);
SqlDependency dependency = new SqlDependency(this.CurrentCommand);
dependency.OnChange += this.dependency_OnChange;
if (this.CurrentConnection.State == ConnectionState.Closed)
this.CurrentConnection.Open();
try
{
DataTable dt = new DataTable();
dt.Load(this.CurrentCommand.ExecuteReader(CommandBehavior.CloseConnection));
return dt;
}
catch (Exception ex)
{
return null;
}
}
void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
SqlDependency dependency = sender as SqlDependency;
dependency.OnChange -= new OnChangeEventHandler(dependency_OnChange);
this.OnNewMessage(e);
}
#region IDisposable Members
public void Dispose()
{
SqlDependency.Stop(this.ConnectionString);
}
#endregion
}
And in my user control :
Notifier = new SQLNotifier();
Notifier.NewMessage += new EventHandler<SqlNotificationEventArgs>(notifier_NewMessage);
DataTable dt = Notifier.RegisterDependency();
Finally :
void notifier_NewMessage(object sender, SqlNotificationEventArgs e)
{
//I have permenantly notifications here.
...
}