SQL dependency messages doesn't stop

45 Views Asked by At

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.

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/detecting-changes-with-sqldependency

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.
       ...
    }
0

There are 0 best solutions below