c# - SqlDependency OnChange 事件为数据库中的每个事件触发多次

Question

我正在使用 SqlDependency 和 signalR 开发一个通知系统，我无法处理的问题是当我根据与会者状态将数据库中的属性值“IsOnline”更改为 True 或 False 时，OnChange 事件多次触发，第一次新用户登录时，我收到两个通知，然后第二次收到更多通知，比如 4 个，然后更多。每次有新用户登录或注销时，通知数量都会增加。我确定 SqlDependency 中的问题不在 SignalR 中，我将与您分享我的部分代码。

提前致谢。

  [System.Web.Services.WebMethod]

    public static IEnumerable<AttendeeList> GetAllUsers()
    {
        var AttendeeList = new List<AttendeeList>();

        try
        {
            using (var connection = new SqlConnection(_connString))
            {
                connection.Open();
                string str = "";
                str += "SELECT [AttendeeID], ";
                str += "       [IsAllowToUploadDocuments],";
                str += "       [IsOnline], ";
                str += "       [AttendeeTypeName],";
                str += "       [UserName] ";
                str += "       FROM [dbo].[Meeting_Attendees]   ";
                str += "       INNER JOIN [dbo].[aspnet_Users]  ON [aspnet_Users].[UserId] = [Meeting_Attendees].[AttendeeID] ";
                str += "       INNER JOIN   [dbo].[AttendeeType] ON [dbo].[AttendeeType].[AttendeeTypeID] = [dbo].[Meeting_Attendees].[AttendeeTypeID] ";
                str += "       WHERE [MeetingID]=@MeetingID ORDER BY [IsOnline] DESC";

                using (var command = new SqlCommand(@str, connection))
                {
                    SqlParameter prm = new SqlParameter("@MeetingID", SqlDbType.Int);
                    prm.Direction = ParameterDirection.Input;
                    prm.DbType = DbType.Int32;
                    prm.Value = Convert.ToInt32(Properties.Settings.Default.MeetingID);
                    command.Parameters.Add(prm);
                    command.Notification = null;

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

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

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        AttendeeList.Add(item: new AttendeeList { UserName = (string)reader["UserName"], UserType = (string)reader["AttendeeTypeName"], IsOnline = (bool)reader["IsOnline"], IsAllowToUploadDocuments = (bool)reader["IsAllowToUploadDocuments"], IsCurrentUser = true ? (Guid)reader["AttendeeID"] == new Guid(Properties.Settings.Default.UserID.ToString()) : false });
                    }
                }
            }
        }
        catch { }
        return AttendeeList;
    }

    private static void dependencyUsers_OnChange(object sender, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change && e.Info == SqlNotificationInfo.Update)
        {
            //Call SignalR  
            MessagesHub.UpdateUsers();
        }
    }

Answer 1

为了确保事件处理程序被注册一次，在“+=”之前执行“-=”:

oDependency.OnChange -= new OnChangeEventHandler(DBUpdateNotificationReeived);
oDependency.OnChange += new OnChangeEventHandler(DBUpdateNotificationReeived);

检查您的 SQL 表是否没有“更新”正在更新的记录的触发器(除了 sqltabledependency 触发器)。