SignalR SQLTableDependency database changes tracking problems

Question

My app is an ASP NET MVC 5 app with a databse. There is a table called Logs in my database. I want my app to notify all clients about new inserts to that table, so i decided to use SignalR + SQLTableDependency. I read a lot of examples, combined all of them (but iam quite new to all this stuff), and that's what i ended up with:

My Log Entity class:

public class Log
{
    public Log(int visitRequestId, string requestNumber)
    {
        Created = DateTime.Now;
        VisitRequestId = visitRequestId;
        RequestNumber = requestNumber;
    }

    public Log()
    {

    }
    [DatabaseGenerated(DatabaseGeneratedOption.Identity), Key]
    public int Id { get; set; }

    [Required]
    public int VisitRequestId { get; set; }

    public string RequestNumber { get; set; }

    public DateTime Created { get; set; }


    public string Message { get; set; }




}

My singletone service class:

public class JournalMessangesService
    {
        private static readonly Lazy<JournalMessangesService> _instance = new Lazy<JournalMessangesService>(()=>new JournalMessangesService(GlobalHost.ConnectionManager.GetHubContext<JournalMessageHub>().Clients));

    private static SqlTableDependency<Log> _tableDependency;
    private IHubConnectionContext<dynamic> Clients { get; }

    public static JournalMessangesService Instance => _instance.Value;

    private JournalMessangesService(IHubConnectionContext<dynamic> clients)
    {
        Clients = clients;


        _tableDependency = new SqlTableDependency<Log>(
            ConfigurationManager.ConnectionStrings["EFDbContext"].ConnectionString,
            "Logs"
            );
        _tableDependency.OnChanged += SqlTableDependency_Changed;
        _tableDependency.OnError += SqlTableDependency_OnError;
        _tableDependency.Start();
    }

    private void SqlTableDependency_OnError(object sender, ErrorEventArgs e)
    {
        throw e.Error;
    }

    private void SqlTableDependency_Changed(object sender, RecordChangedEventArgs<Log> e)
    {
        if (e.ChangeType != ChangeType.None)
        {
            BroadcastJournalMessage(e.Entity);
        }
    }

    private void BroadcastJournalMessage(Log log)
    {
        Clients.All.updateMessage(log);
    }

    public IEnumerable<Log> GetAllLogs()
    {
        var logEntries = new List<Log>();
        var connString = ConfigurationManager.ConnectionStrings["EFDbcontext"].ConnectionString;
        using (var connection = new SqlConnection(connString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "SELECT * FROM [Logs]";
                using (var dataReader = command.ExecuteReader() )
                {
                    while (dataReader.Read())
                    {
                        var id = dataReader.GetInt32(dataReader.GetOrdinal("VisitRequestId"));
                        var reqNum = dataReader.GetString(dataReader.GetOrdinal("RequestNumber"));
                        var created = dataReader.GetDateTime(dataReader.GetOrdinal("Created"));
                        var message = dataReader.GetString(dataReader.GetOrdinal("Message"));
                        logEntries.Add(new Log(id, reqNum) {Created = created, Message = message});
                    }
                }
            }
        }

        return logEntries;
    }
    #region IDisposable Support
    private bool disposedValue = false; // To detect redundant calls

    protected virtual void Dispose(bool disposing)
    {
        if (!disposedValue)
        {
            if (disposing)
            {
                _tableDependency.Stop();
            }

            disposedValue = true;
        }
    }

    ~JournalMessangesService()
    {
        Dispose(false);
    }


    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    #endregion

}

My Hub class:

[HubName("journalMessageHub")]
public class JournalMessageHub : Hub
{


    private readonly JournalMessangesService _journalMessangesService;
    public JournalMessageHub() : this(JournalMessangesService.Instance) { }

    public JournalMessageHub(JournalMessangesService journalMessangesService)
    {
        _journalMessangesService = journalMessangesService;
    }

    public IEnumerable<Log> GetAllLogs()
    {
        return _journalMessangesService.GetAllLogs();
    }




}

I also added app.MapSignalR(); to my Startup.cs.

Here is my html view:

<script src="~/Scripts/jquery.signalR-2.2.2.js"></script>
<script src="/signalr/hubs" type="text/javascript"></script>
<script src="~/Scripts/noty.js"></script>

<script type="text/javascript">
    $(document).ready(function() {

    var journalHub = $.connection.journalMessageHub;
    journalHub.client.updateMessage = function(log) {
        new Noty({
                            text: log.Message,
                            type: 'warning',
                            theme: 'metroui',
                            timeout: '2000'
                        }).show();
    };
    $.connection.hub.start().done(function () {

        journalHub.server.getAllLogs().done(function(data) {
            console.log('data length: ' + data.length);
        });
        journalHub.client.updateMessage();
    });
});

All i want is to get a notification (via Noty.js, but it doesnt' matter) when there is an insert int my Logs database table. But with my code i don't get that result - notifications are fired, but they don't contain any Log data inside, only some 'undefined' stuff.

Another problem is that i don't want to fire notifications on page refresh - inly when database changes.

I think i have some misunderstanding of the javascript part of my code, but it can be anything else - i am completely new to all of this web stuff.

Please help me, i just can't read all those StockTicker examples anymore, they don't work for me.

Thanks a lot for any help.

Answer 1

Maybe some random poor guy like me will fish out this post and it will help him. As it turned out, i just missed Microsoft.AspNet.SignalR.Client package. I didn't think i had to add it manually. I added it and everything started to work like a charm.