如何使用signalR通知数据库更改 [英] How to notify database changes with signalR

问题描述

我刚开始使用SignalR，想尝试实时通知.目的是继续在网页上显示更新的消息.有一个数据库表- DummyData ，带有列 Message .该表只有一条记录- Hello 页面加载后，将显示"Hello".

I've just started with SignalR and would like to try out the real time notifications. The objective is to keep displaying the updated message on web page. There is a database table - DummyData with a column Message. This table has only one record - Hello When the page loads, "Hello" is displayed.

然后我在sql server 2012中手动运行命令

I then manually run the command in sql server 2012

update DummyData set Message ='hello world'，但该消息未在网页中更新.

update DummyData set Message='hello world', but the message isn't updated in the webpage.

aspx:

<script>
    $(function () {
        var notify = $.connection.notificationsHub;

        $.connection.hub.start().done(function () {
            notify.server.notifyAllClients();
        });

        notify.client.displayNotification = function (msg) {               
            $("#newData").html(msg);
        };

        notify.client.stopClient = function () {
            $.connection.hub.stop();
        };
    });
</script>
 <span id="newData"></span>

aspx.cs:

public string SendNotifications()
    {
      string message = string.Empty;
      using (SqlConnection connection = new SqlConnection(conStr))
       {
        string query = "SELECT [Message] FROM [dbo].[DummyData]";

        SqlCommand command = new SqlCommand(query, connection)
        command.Notification = null;
        SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
         reader.Read();
         message = reader[0].ToString();
        }
       }            
        return message;
    }

    private void dependency_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
        {
            SendNotifications();
        }            
    }

NotificationsHub.cs

public class NotificationsHub : Hub
{
 Messages obj = new Messages();
 public void NotifyAllClients(string msg)
  {
   IHubContext context = GlobalHost.ConnectionManager.GetHubContext<NotificationsHub>();
   context.Clients.All.displayNotification(msg);
  }

 public override System.Threading.Tasks.Task OnConnected()
  {
   NotifyAllClients();
   return base.OnConnected();
  }

 public override System.Threading.Tasks.Task OnDisconnected(bool stopCalled)
 {
  NotifyAllClients();
  return base.OnDisconnected(stopCalled);
 }
}

global.asax:

protected void Application_Start(object sender, EventArgs e)
        {
            SqlDependency.Start(Constr);
        }

当我运行tsql update命令时，首先在 dependency_OnChange 处命中了断点，并且可以看到从 SendNotification 返回的新更新文本.但是页面上看不到它.感觉就像我快到了，但是缺少了一些东西.

When I run the tsql update command, the break point is first hit at dependency_OnChange and I can see the new updated text being returned from SendNotification. But it isn't seen reflected on page. Feels like I'm almost there but something is missing.

推荐答案

Signalr不在监视数据库中的更改.因此，当您仅在数据库中将用户设置为非活动状态时，这对Signalr毫无意义.您的3个客户端仍处于连接状态.

Signalr is not watching your database for changes. So when you just set the user to inactive in the database, it means nothing to Signalr. Your 3 clients are still connected.

要获得理想的结果，请在集线器中添加类似的内容

To get the desired result add something like this to your Hub

public override OnConnected()
{
  // Increase the active user count in the db 
  IHubContext context = GlobalHost.ConnectionManager.GetHubContext<ServerHub>();
  Clients.All.broadcastCount(DB.GetCount());
  return base.OnConnected();
}

public override OnDisconnected() 
{
    //Decrease the connected user count in the db
  IHubContext context = GlobalHost.ConnectionManager.GetHubContext<ServerHub>();
  Clients.All.broadcastCount(DB.GetCount());
  return base.OnDisconnected();
}

然后，当您连接和断开客户端连接时，集线器将通知连接的客户端.

Then when you connect and disconnect your clients, the hub will notify connected clients.

您将需要以SignalR捕获的方式断开连接，因此您不仅可以更改数据库中的标志.尝试从客户端调用 $.connection.hub.stop(); .

You will need to disconnect in a way that SignalR will catch, so you can't just change a flag in the database. Try calling $.connection.hub.stop(); from your client.

此链接对此有更详细的说明.

This link goes into more detail on it.

如果您说在数据库中更新后触发了 dependency_OnChange 事件，则不要调用 SendNotifications(); ，而是调用中心方法，特别是NotifyAllClients(...)

If you say the dependency_OnChange event is fired after you update in the database, then Instead of calling SendNotifications();, call a hub method, specifically NotifyAllClients(...)

这篇关于如何使用signalR通知数据库更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！