SQLDependency with SignalR and MVC. OnChange only firing once

Question

I am able to display real time row updates on my websites dashboard. However, when trying to do almost exactly the same thing with notifications my onchange event only fires once. The SignalR connection is being used by the dashboard updates and it works 100% of the time. All of the code is practically identical, except I'm getting every row, everytime on the dashboard and not making any updates to the database after getting them.

Here's what I am trying to do.

Insert a row into dbo.SystemNotifications with a flag of Sent = 0
Subscribe to changes and show new inserts using jQuery.noty
Update the record to sent = 1

This works the first time or anytime I refresh the browser but the SQLDependency onchange either fires once more after that or doesn't fire again at all.

IN MY CUSTOM.JS FILE

$(document).ready(function ( $ ) {
    var notificationHub = $.connection.notyHub;

    notificationHub.client.showNotification = function () {
        getNotifications();
        markSet();
    };

    $.connection.hub.start();
    getNotifications();

});

function getNotifications() {
    $.ajax({
        url: '../Notification/GetNotifications',
        type: 'GET',
        global: false,
        datatype: 'json',
        success: function (data) {
            if (data.length > 0) {
                for (var i = 0; i < data.length; i++) {
                    noty({
                        text: data[i].NotificationText,
                        type: 'information',
                        timeout: 0,
                        closeWith: ['hover'],
                        maxVisible: 1
                    });

                    markSent(data[i].ID);
                }
            }
        }
    });
}

function markSent(id) {
    $.ajax({                  
        url: '../Notification/MarkNotificationSent',
        type: 'POST',
        data: JSON.stringify({ notyID: id }),
        dataType: 'json',
        contentType: 'application/json',
        global: false,        
        error: function (req, status, error) {
            alert("R: " + req + " S: " + status + " E: " + error);
        }
    });
}

MY CONTROLLER

 public class NotificationController : CaseEnhancedController
    {
        private readonly NotificationRepository notyRepo = new NotificationRepository();
        private ReaderWriterLockSlim methodLock = new ReaderWriterLockSlim();

        [OutputCache(Duration = 0)]
        public JsonResult GetNotifications()
        {
            try
            {
                methodLock.EnterWriteLock();

                IEnumerable<Notification> notifications = notyRepo.GetData();
                JsonResult notysJSon = new JsonResult();
                notysJSon.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
                notysJSon.Data = notifications;
                return notysJSon;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (methodLock.IsWriteLockHeld)
                    methodLock.ExitWriteLock();
            }
        }

        [HttpPost]
        public JsonResult MarkNotificationSent(string notyID)
        {
            string sConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SirenCMNew;Data Source=localhost";

            try
            {
                methodLock.EnterWriteLock();

                using (SqlConnection connection = new SqlConnection(sConn))
                {
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (SqlCommand cmd = new SqlCommand("[dbo].[spMarkNotificationSent]", connection))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;

                        SqlParameter idParm = new SqlParameter
                        {
                            Value = notyID,
                            SqlDbType = SqlDbType.BigInt,
                            ParameterName = "ID"
                        };

                        cmd.Parameters.Add(idParm);
                        cmd.ExecuteNonQuery();
                    }
                }

                return Json(new { result = notyID }, JsonRequestBehavior.AllowGet);
            }
            catch (Exception ex)
            {
                return Json(new { result = 0 }, JsonRequestBehavior.AllowGet);
            }
            finally
            {
                if (methodLock.IsWriteLockHeld)
                    methodLock.ExitWriteLock();
            }
        }

MY REPOSITORY

public class NotificationRepository
{
    string sConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SirenCMNew;Data Source=localhost";

    public IEnumerable<Notification> GetData()
    {
        try
        {
            using (var connection = new SqlConnection(sConn))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(@"EXEC [dbo].[spGetUnsentNotifications]", connection))
                {
                    command.Notification = null;

                    SqlDependency dependency = new SqlDependency(command);
                    dependency.OnChange += new OnChangeEventHandler(OnChange);

                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (var reader = command.ExecuteReader())
                        return reader.Cast<IDataRecord>()
                            .Select(x => new Notification()
                            {    
                                ID = x.GetInt64(0),
                                NotificationText = x.GetString(1)                                   
                            }).ToList();
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private void OnChange(object sender, SqlNotificationEventArgs e)
    {
        SqlDependency dependency = sender as SqlDependency;
        dependency.OnChange -= OnChange;

        if (e.Info == SqlNotificationInfo.Insert)
        {
            NotificationHub.Show();
        }
    }

MY HUB

 [HubName("notyHub")]
    public class NotificationHub : Hub
    {
        public static void Show()
        {
            IHubContext context = GlobalHost.ConnectionManager.GetHubContext<JobInfoHub>();            
            context.Clients.All.showNotification();
        }     
    }

THE SPROCS

CREATE PROCEDURE [dbo].[spGetUnsentNotifications]
    AS

    SELECT
        ID, NotificationText
    FROM
        dbo.SystemNotifications
    WHERE
        Sent = 0

GO

CREATE PROCEDURE dbo.spMarkNotificationSent

@ID bigint

AS

UPDATE 
    dbo.SystemNotifications
SET
    Sent = 1
WHERE
    ID = @ID

score 0 · Answer 1 · answered Jan 21 '14 at 00:37

I finally figured this out. I had to take a different approach. Here's how I did it

THE REPOSITORY

if (e.Info == SqlNotificationInfo.Insert)
        {
            id = GetLastRecord();
        }

        if (id != 0)
            DeleteNotification(id);

        GetData();
    }

    private long GetLastRecord()
    {
        Notification notification = null;
        long id = 0;
        try
        {
            using (var connection = new SqlConnection(sConn))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(@"SELECT TOP 1 ID, NotificationText FROM dbo.SystemNotifications with(nolock)", connection))
                {
                    if (connection.State == ConnectionState.Closed)
                        connection.Open();

                    using (var reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            notification = new Notification
                            {
                                ID = (long)reader.GetSqlInt64(0)
                                , NotificationText = reader.GetString(1)
                            };
                        }
                    }

                    if (null != notification)
                    {
                        NotificationHub.Show(notification);
                        id = notification.ID;
                    }
                    return id;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    private static void DeleteNotification(long notyID)
    {
        string sConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SirenCMNew;Data Source=localhost";

        try
        {

            using (SqlConnection connection = new SqlConnection(sConn))
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (SqlCommand cmd = new SqlCommand("[dbo].[spDeleteNotification]", connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    SqlParameter idParm = new SqlParameter
                    {
                        Value = notyID,
                        SqlDbType = SqlDbType.BigInt,
                        ParameterName = "ID"
                    };

                    cmd.Parameters.Add(idParm);
                    cmd.ExecuteNonQuery();
                }
            }

        }
        catch (Exception ex)
        {
            //TODO
        }
    }

THE HUB

[HubName("notyHub")]
public class NotificationHub : Hub
{
    public static void Show(Notification notification)
    {
        IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationHub>();
        context.Clients.All.showNoty(notification.ID, notification.NotificationText);

    }     
}

THE CONTROLLER

 public class NotificationController : CaseEnhancedController
{
    private readonly NotificationRepository notyRepo = new NotificationRepository();
    private ReaderWriterLockSlim methodLock = new ReaderWriterLockSlim();

    [OutputCache(Duration = 0)]
    public JsonResult GetNotifications()
    {
        int count = 0;

        try
        {
            methodLock.EnterWriteLock();

            IEnumerable<Notification> notifications = notyRepo.GetData();
            count = notifications.Count();
            JsonResult notysJSon = new JsonResult();
            notysJSon.JsonRequestBehavior = JsonRequestBehavior.AllowGet;
            notysJSon.Data = notifications;
            return notysJSon;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            if (methodLock.IsWriteLockHeld)
                methodLock.ExitWriteLock();

            if (count > 0)
                DeleteNotifications();
        }
    }

    private static void DeleteNotifications()
    {
        string sConn = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SirenCMNew;Data Source=localhost";

        try
        {
            using (SqlConnection connection = new SqlConnection(sConn))
            {
                if (connection.State == ConnectionState.Closed)
                    connection.Open();

                using (SqlCommand cmd = new SqlCommand("[dbo].[spDeleteAllNotifications]", connection))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        catch (Exception ex)
        {
            //TODO
        }
    }

THE JQUERY

$(function () {
 var notificationHub = $.connection.notyHub;

notificationHub.client.showNoty = function (id, message) {
    showNotification(id, message);
};

$.connection.hub.start();
getNotifications();
}

function showNotification(id, message)
{
    noty({
        text: message,
        type: 'information',
        timeout: 0,
        closeWith: ['hover'],
        maxVisible: 1
    });
}

function getNotifications() {
$.ajax({
    url: '../Notification/GetNotifications',
    type: 'GET',
    global: false,
    datatype: 'json',
    success: function (data) {
        if (data.length > 0) {
            for (var i = 0; i < data.length; i++) {
                showNotification(data[i].ID, data[i].NotificationText);
            }
        }
    }
});
}

have you found out the reason why it was not firing second time. I have same issue. — coder771, Dec 02 '15 at 12:48

score 0 · Answer 2 · answered Jun 08 '15 at 07:12

You should write your'e sp to following method :

CREATE PROCEDURE [dbo].[spGetUnsentNotifications]
AS

SELECT
    [ID], [NotificationText]
FROM
    [dbo].[SystemNotifications]
WHERE
    [Sent] = 0

GO

CREATE PROCEDURE [dbo].[spMarkNotificationSent]

@ID bigint

AS

UPDATE 
[dbo].[SystemNotifications]
SET [Sent] = 1 
WHERE [ID] = @ID