SqlNotificationEvent 通知多次，即使是在数据库中的单个插入

本文关键字：数据库 单个 插入 即使是 通知 SqlNotificationEvent | 更新日期: 2023-09-27 18:31:24

SqlNotificationEvent多次通知，即使是在数据库中的单个插入。

即使对于表中的单个插入/更新/删除，事件也会通知e.Type == SqlNotificationType.Change多次，任何人都可以帮助为什么会发生这种情况。对于表中的单个更改，它应该只通知一次。

static class Program
{
    private static string mStarterConnectionString = "Data Source=localhost;Database=SqlDependencyTest;Persist Security Info=false;Integrated Security=false;User Id=startUser;Password=startUser";
    private static string mSubscriberConnectionString = "Data Source=localhost;Database=SqlDependencyTest;Persist Security Info=false;Integrated Security=false;User Id=subscribeUser;Password=subscribeUser";
    public const string CACHE_KEY = "APPCACHEKEY";
    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
        // Starting the listener infrastructure...
        SqlDependency.Start(mStarterConnectionString);
        // Registering for changes... 
        RegisterForChanges();
        Application.Run(new SqlCache());
        // Quitting...
        SqlDependency.Stop(mStarterConnectionString);
    }
    public static DataTable RegisterForChanges()
    {
        DataTable dataTable = new DataTable();
        // Connecting to the database using our subscriber connection string and
        // waiting for changes...
        SqlConnection oConnection = new SqlConnection(mSubscriberConnectionString);
        oConnection.Open();
        try
        {
            using (SqlCommand oCommand = new SqlCommand("dbo.GetUsers", oConnection))
            //using (SqlCommand oCommand = new SqlCommand("SELECT ID, Name FROM dbo.Users", oConnection))
            {
                oCommand.CommandType = CommandType.StoredProcedure;
                SqlDependency oDependency = new SqlDependency(oCommand);
                oDependency.OnChange += new OnChangeEventHandler(OnNotificationChange);
                using (SqlDataAdapter adapter = new SqlDataAdapter(oCommand))
                    adapter.Fill(dataTable);
            }
            AppMain.Cache.Insert(CACHE_KEY, dataTable, null, Cache.NoAbsoluteExpiration, TimeSpan.FromSeconds(60));
        }
        finally
        {
            oConnection.Close();
        }
        return dataTable;
    }
    public static void OnNotificationChange(object caller, SqlNotificationEventArgs e)
    {
        if (e.Type == SqlNotificationType.Change)
            RegisterForChanges();
    }

SqlNotificationEvent 通知多次，即使是在数据库中的单个插入

您可以使用

SqlNotificationEventArgs 对象的 SqlNotificationInfo 对象，并检查它是 Insert、Update 还是 Delete 并忽略其余部分。

public static void OnNotificationChange(object caller, SqlNotificationEventArgs e)
{
    if (e.Type == SqlNotificationType.Change && (e.Info == SqlNotificationInfo.Insert || e.Info == SqlNotificationInfo.Delete || e.Info == SqlNotificationInfo.Update))
    {
        SqlDependency dependency =(SqlDependency)sender;
        dependency.OnChange -= OnNotificationChange;
        RegisterForChanges();
    }
}

我遇到了问题中描述的相同问题。 我能够查明该问题是由于创建多个SqlCommand实例引起的（类似于您在RegisterForChanges()内部using (SqlCommand oCommand = new SqlCommand("dbo.GetUsers", oConnection))的方式。

我更改了代码以声明对SqlCommand和SqlConnection对象的实例引用，并在 RegisterForChanges() 方法中仅实例化了新的SqlDependency实例。

void RegisterForChanges()
{
  _sqlCommand.Notification = null;
  _sqlConnection.Open();
  SqlDependency dependency = new SqlDependency(_sqlCommand);
  dependency.OnChange += dependency_OnChange;
  _sqlCommand.ExecuteNonQuery(); // or ExecuteReader and parse the results, etc
  _sqlConnection.Close();
}
相关文章：