SqlDependency.OnChange 不在 WinForm 中触发? [英] SqlDependency.OnChange not firing in WinForm?

问题描述

我使用 使用 SqlDependency 检测更改 作为我正在编写的代码的示例.我还查看了具有类似代码的其他链接，但它们都不起作用.

I used Detecting Changes with SqlDependency as example for the code that I'm writing. I've also looked at other links with similar code, but none of them work.

本质上，我只是想在表 [ErrorLog] 发生更改时更改 label1.Text.出于某种原因，OnDependencyChange 没有触发.

Essentially, I simply want to change label1.Text when a change has been made to table [ErrorLog]. For some reason, OnDependencyChange is not firing.

我已在数据库中启用 Service Broker:

I've enabled Service Broker in the database:

ALTER DATABASE TestDB 
SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

现在，这是我的完整代码.很短:

Now, here's my complete code. It's very short:

public partial class Form1 : Form
{
    private string GetConnectionString()
    {
        return @"Data Source=USER-PCSQLEXPRESS;Initial Catalog=TestDB;Persist Security Info=True;User ID=TestUser;Password=12345;";
    }

    SqlConnection connection;

    public Form1()
    {
        InitializeComponent();

        connection = new SqlConnection(GetConnectionString());
        connection.Open();

        SqlDependency.Start(GetConnectionString());
        i = 0;
    }

    int i;

    void OnDependencyChange(object sender, SqlNotificationEventArgs e)
    {
        i++;
        label1.Text = "Changed: " + i.ToString();
        // Handle the event (for example, invalidate this cache entry).
    }

    void SomeMethod()
    {
        // Assume connection is an open SqlConnection.
        // Create a new SqlCommand object.
        using (SqlCommand command = 
            new SqlCommand("SELECT [ErrorLog].[ID],[ErrorLog].[Project],[ErrorLog].[Form],[ErrorLog].[Message],[ErrorLog].[Exception],[ErrorLog].[InsertDate] " + 
            "FROM [dbo].[ErrorLog]", connection))
        {
            // Create a dependency and associate it with the SqlCommand.
            SqlDependency dependency = new SqlDependency(command);

            // Maintain the reference in a class member.
            // Subscribe to the SqlDependency event.
            dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);

            // Execute the command.
            using (SqlDataReader reader = command.ExecuteReader())
            {
                // Process the DataReader.
            }
        }
    }
}

我检查了服务代理是否已启用并且已启用；以下返回 1:

I checked if service broker is enabled and it is; the following returns 1:

SELECT is_broker_enabled 
FROM sys.databases 
WHERE name = 'TestDB';

感谢任何帮助.

谢谢.

推荐答案

除了一件事，你做的一切都是正确的.在 Form1 构造函数中调用方法 SomeMethod() 一次.

You are doing everything correctly except one thing. Call the method SomeMethod() once in your Form1 constructor.

对表数据的所有后续更改都会触发依赖项更改.

All subsequent changes to your table data will trigger the dependency change.

这篇关于SqlDependency.OnChange 不在 WinForm 中触发?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！