数据集更改时，SqlDependency不会触发OnChange事件 [英] SqlDependency doesn't fire OnChange event when dataset is changed

问题描述

我对使用SQL Server的查询通知的概念并不陌生，我需要花一些时间来解决这个问题.

I'm new to the concept of query notifications with SQL Server and it's going to take some time for me to wrap my head around it.

我的目标是创建一个Windows服务应用程序，当对SQL Server表进行更改时会收到通知.我遵循了此指南，该指南对我的入门很有帮助.

My objective is to create a Windows service application that is notified when a change has been made to a SQL Server table. I followed this guide which was helpful in getting me started.

但是我无法获得预期的结果.Windows服务应用程序中的 OnStart()方法如下所示:

However I'm not able to get the expected result. The OnStart() method in my windows service app looks like so:

protected override void OnStart(string[] args)
{
        eventLog1.WriteEntry("Service Started");

        serviceRun = false;

        SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);

        try
        {
            perm.Demand();
            eventLog1.WriteEntry("permission granted");
        }
        catch (System.Exception)
        {
            eventLog1.WriteEntry("permission denied");
        }

        try
        {
            connstr = "Data Source=THSSERVER-LOCAL;Initial Catalog=ET;User ID=mujtaba;Password=ths123";

            connection = new SqlConnection(connstr);

            SqlCommand command = new SqlCommand("select * from dbo.Customer_FileUploads", 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 += Dependency_OnChange;

            SqlDependency.Start(connstr);

            connection.Open();

            // Execute the command.
            using (SqlDataReader reader = command.ExecuteReader())
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        //eventLog1.WriteEntry("reading data");
                    }
                }
                else
                {
                    eventLog1.WriteEntry("No rows found.");
                }
                reader.Close();
            }
        }
        catch (Exception e)
        {
            eventLog1.WriteEntry("Error Message: " + e.Message);
        }
}

预订了 SqlDependency 事件，如下所示:

The event SqlDependency is subscribed to, looks like so:

private void Dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    // Handle the event.
    eventLog1.WriteEntry("data changed");
}

OnStop()方法如下所示:

protected override void OnStop()
{
        SqlDependency.Stop(connstr);
        connection.Close();
        eventLog1.WriteEntry("In onStop.");
}

我在数据库中将 ENABLE_BROKER 设置为true.最终结果是，该服务运行并创建了以下日志:

I have ENABLE_BROKER set to true in my database. The end result is, The service runs and the followings logs are created:

"Service Started"
"permission granted"
"data changed"

但是，当我向表中插入新数据时，不会触发 OnChange()事件，也不会创建新日志.同样，当我停止并再次启动该服务时，即使没有插入新数据，也会触发 OnChange().

However when I insert new data into the table, the OnChange() event doesn't fire and no new log is created. Also when I stop and start the service again, the OnChange() is triggered even though there was no new data inserted.

有人可以帮助我了解这一过程吗?

Can anyone help me understand the process?

推荐答案

事件触发后，将删除SqlDependency，因此您需要再次使用依赖项执行命令.下面是一个控制台应用程序示例，除非由于错误通知，否则该示例将再次订阅.

The SqlDependency is removed after the event fires so you need to execute the command again with the dependency. Below is a console app example that will subscribe again unless the notification was due to an error.

using System;
using System.Data;
using System.Data.SqlClient;

namespace SqlDependencyExample
{
    class Program
    {

        static string connectionString = @"Data Source=.;Initial Catalog=YourDatabase;Application Name=SqlDependencyExample;Integrated Security=SSPI";

        static void Main(string[] args)
        {

            SqlDependency.Start(connectionString);

            getDataWithSqlDependency();

            Console.WriteLine("Waiting for data changes");
            Console.WriteLine("Press enter to quit");
            Console.ReadLine();

            SqlDependency.Stop(connectionString);

        }

        static DataTable getDataWithSqlDependency()
        {

            using (var connection = new SqlConnection(connectionString))
            using (var cmd = new SqlCommand("SELECT Col1, Col2, Col3 FROM dbo.MyTable;", connection))
            {

                var dt = new DataTable();

                // Create dependency for this command and add event handler
                var dependency = new SqlDependency(cmd);
                dependency.OnChange += new OnChangeEventHandler(onDependencyChange);

                // execute command to get data
                connection.Open();
                dt.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));

                return dt;

            }

        }

        // Handler method
        static void onDependencyChange(object sender,
           SqlNotificationEventArgs e)
        {

            Console.WriteLine($"OnChange Event fired. SqlNotificationEventArgs: Info={e.Info}, Source={e.Source}, Type={e.Type}.");

            if ((e.Info != SqlNotificationInfo.Invalid)
                && (e.Type != SqlNotificationType.Subscribe))
            {
                //resubscribe
                var dt = getDataWithSqlDependency();

                Console.WriteLine($"Data changed. {dt.Rows.Count} rows returned.");
            }
            else
            {
                Console.WriteLine("SqlDependency not restarted");
            }

        }


    }
}

这篇关于数据集更改时，SqlDependency不会触发OnChange事件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！