SqlDependency OnChange事件为Database上的每个事件触发多次 [英] SqlDependency OnChange Event Fires many times for every single event on Database

问题描述

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





< pre lang =cs> [System.Web.Services.WebMethod]

public static IEnumerable< AttendeeList> GetAllUsers（）
{
var AttendeeList = new 列表< AttendeeList>（） ;

尝试
{
使用（ 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 ;

使用（ 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 ）阅读器[ IsAllowToUploadDocuments]，IsCurrentUser = true ？（Guid）读者[ 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（）;
}
}

解决方案

我知道这个问题已经过时了，但我看到它没有回答，只是被引用一个工作的例子。



你在每次调用<$ c $时创建新的 SqlDependency 对象c> GetAllUsers 方法（我认为它不会被调用一次）并且不会停止旧的方法。这会导致多个 SqlDependency 对象在数据库中查看相同的更改，并且每次调用添加的 GetAllUsers 方法时另一个，这将成为遗忘。

更正确的方法是使用一个单独的方法初始化 SqlDependency 对象并且只打电话一次。



希望我帮助过。

干杯。

First即使数据中没有实际变化，也可能发生所有通知。例如，如果服务器负载很重，或者甚至通知问题可能触发事件，则可能发生这种情况。您可以尝试使用 HasChanges [ ^ ]如果它以不同的方式显示情况。



可能提高准确性的一件事是删除参数并使用文字。



每次增加通知的时候，听起来你每次都在创建新的订阅而不会结束旧的订阅。也许每次调用web方法都会创建一个新的监听器。但是，从我无法找到的代码中调用Start或Stop方法...例如，看一下使用SqlDependency检测更改 [ ^ ]。

I'm developing a notification system using SqlDependency and signalR, the problem I can't deal with is when I change the attribute value "IsOnline" in DB to True or False based on attendee status, OnChange event fires many times, first time a new user log in I get two notifications then the second time I get more like 4 then more then more. The number of notifications increase every time a new one sign in or sign out. I'm sure the problem in SqlDependency not in SignalR, I'm going to share with you part of my code.

[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();
        }
    }

解决方案

I know the question is old, but I see it's not answered, just referenced to a working example.

You're creating new SqlDependency objects on each call of the GetAllUsers method (which I presume is not called just once) and not stopping the old-ones. This results in multiple SqlDependency objects watching for identical change in the database, and each time you call the GetAllUsers method you add another one and that multiplies into oblivion.
A more correct way of doing this would be having a separate method for initializing the SqlDependency object(s) and calling it just once.

Hope I've helped.
Cheers.

First of all the notification may occur even if there is no actual change in the data. This may happen for example if the server is under heavy load or even problems with the notification may trigger the event. You can try using HasChanges[^] if it shows the situation differently.

One thing that may possibly enhance the accuracy is removing the parameter and using a literal instead.

What comes to the increase of notification each time, it sounds like you're creating new subscriptions each time without ending the old ones. Perhaps each call on the web method creates a new listener. Howebver, from the code I can't find calls to Start or Stop methods... For example, have a look at the example in Detecting Changes with SqlDependency[^].

这篇关于SqlDependency OnChange事件为Database上的每个事件触发多次的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！