SQLDependency OnChange event producing an infinite loop

Question

I have a problem when using SQLDependency and SignalR Hub. When a connection with the hub is started, the SQLDependency's OnChange event is always firing even though there are no changes in the database.

Here is my code that contains SQLDependency

public List<NotifCenterModel> countNewTransaksi()
{
    List<NotifCenterModel> ncms = new List<NotifCenterModel>();
    command = new SqlCommand(@"SELECT Edolpuz_DB.dbo.TABEL_NOTIF_CENTER.NAMA_TABEL,Edolpuz_DB.dbo.TABEL_NOTIF_CENTER.JUMLAH_NOTIF FROM Edolpuz_DB.dbo.TABEL_NOTIF_CENTER",connect);
    try 
    {
        command.Notification = null;
        SqlDependency dependency = new SqlDependency(command);
        dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
        if(connect.State == ConnectionState.Open)
            connect.Close();
        connect.Open();
        reader = command.ExecuteReader();
        while (reader.Read())
        {
            ncms.Add(new NotifCenterModel(reader[0].ToString(), int.Parse(reader[1].ToString())));
        }
        return ncms;
    }
    catch { return null; }
    finally { connect.Close(); }
}

private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    TransHub.Show();   
}

and in my Hub the code is like this

public class TransHub : Hub
{
    public static void Show()
    {
        IHubContext context = GlobalHost.ConnectionManager.GetHubContext<TransHub>();
        context.Clients.All.displayStatus();
    }
}

and here is my javascript

 $(function () {
            // Proxy created on the fly
            var job = $.connection.transHub;
            // Declare a function on the job hub so the server can invoke it
            job.client.displayStatus = function () {
                //     alert("test");
                getData();
            };

            // Start the connection
            $.connection.hub.start().done(function () {
                getData();
            }).fail(function (e) {
                alert(e);
            });

        });

function getData() {
    $.ajax({
        url: server + '/Admin/GetNotifikasi/',
        type: 'GET',
        dataType: 'json',
        success: function (data) {
            for (var i = 0; i < data.length ; i++)
            {
                if (data[i].nama_tabel == "TABEL_TRANSAKSI")
                {    
                    $('#notifTrans').text(data[i].jumlah_notif);
                }
                else if (data[i].nama_tabel == "TABEL_KONF_BAYAR")
                {
                    $('#notifBayar').text(data[i].jumlah_notif);
                }
                else if (data[i].nama_tabel == "TABEL_TESTI")
                {
                    $('#notifTesti').text(data[i].jumlah_notif);
                }
                else if (data[i].nama_tabel == "TABEL_KUSTOM_ORDER")
                {
                    $('#notifKustom').text(data[i].jumlah_notif);
                }
            }
        }
    });
}

When in connection.hub.start().done I call getData(), it will constantly fire and produce an infinite loop, but when I don't call getData() it doesn't fire the event when data in table changes. How can this be fixed?

score 0 · Answer 1 · answered May 07 '15 at 00:39

0

In dependency_OnChange, you need to check e.Type. If it's != SqlNotificationType.Change, then the handler was called for some reason other than a data change. The subscription itself likely failed.

answered May 07 '15 at 00:39

jjj

4,822
1
16
39

But when i check e.Type first, it don't give any result even though database is change. What's wrong with my code ? @jjj – Handaru Eri Pramudiya Apr 01 '16 at 09:00
1

@HandaruEriPramudiya; You were saying that the "`OnChange` event is always firing even though there are no changes" -- for a single subscription, you will only notified at most once, so whenever `dependency_OnChange` is called, the subscription is no longer active. But if it's called with `e.Type == SqlNotifcationType.Subscribe`, then the subscription failed. – jjj Apr 01 '16 at 16:49

score 0 · Answer 2 · answered Apr 25 '19 at 17:41

According to this site (https://learn.microsoft.com/en-us/previous-versions/aewzkxxh(v=vs.90)), the table name in query must be two-part name, like [dbo].[TABEL_NOTIF_CENTER].

The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.

[]'s

SQLDependency OnChange event producing an infinite loop

2 Answers2