时间戳

首页 > 解决方案 > 为什么在更改数据库表中的单元格值时不会触发 OnChange 事件？

c# - 为什么在更改数据库表中的单元格值时不会触发 OnChange 事件？

问题描述

在过去的 24 小时里，我一直在处理此代码，但它不起作用。

我希望OnChange事件受到打击如果我修改服务表中的一个单元格但它没有，无论我做什么。

我已经在数据库上启用了服务代理，但它仍然不起作用。

代码：

class Program
    {
        static void Main(string[] args)
        {
            var cs = Utility.getConnectionString();
            using (SqlConnection connection = new SqlConnection(cs))
            {
                connection.Open();
                SqlCommand command = new SqlCommand(@"Select [Services].[ServiceName], [Services].[ServicePrice] from dbo.Services", connection);
                SqlDependency dependency = new SqlDependency(command);
                dependency.OnChange += new OnChangeEventHandler(OnChange);
                SqlDependency.Start(cs);
                command.ExecuteReader().Dispose();
            }
            Console.ReadKey();
        }

        private static void OnChange(object sender, SqlNotificationEventArgs e)
        {
            Console.WriteLine(e.Info);
        }
    }

使用编辑更多或更新查询对数据库进行更改。

在此处输入图像描述

标签： c#.netsql-serverconsole-applicationsqldependency

解决方案

下面是一个基于您的代码的工作示例，该代码SqlNotificationEventArgs在事件触发时显示值并仅在有效时重新订阅。

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

class Program
{

    static void Main(string[] args)
    {

        SqlDependency.Start(Utility.getConnectionString());

        GetDataWithSqlDependency();
        Console.WriteLine("Waiting for data changes");
        Console.WriteLine("Press any key to quit");
        Console.ReadKey();

        SqlDependency.Stop(Utility.getConnectionString());

    }

    static void GetDataWithSqlDependency()
    {

        using (var connection = new SqlConnection(Utility.getConnectionString()))
        using (var cmd = new SqlCommand(@"SELECT [Services].[ServiceName], [Services].[ServicePrice] from dbo.Services;", connection))
        {
            var dependency = new SqlDependency(cmd);
            dependency.OnChange += new OnChangeEventHandler(OnDependencyChange);
            connection.Open();
            cmd.ExecuteReader().Dispose();
        }

    }
    static void OnDependencyChange(object sender, SqlNotificationEventArgs e)
    {

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

        if ((e.Info != SqlNotificationInfo.Invalid)
            && (e.Type != SqlNotificationType.Subscribe))
        {
            SqlDependency.Start(Utility.getConnectionString());
            GetDataWithSqlDependency();
            Console.WriteLine($"Data changed.");
        }
        else
        {
            Console.WriteLine("SqlDependency not restarted");
        }

    }

}

static class Utility
{
    public static string getConnectionString()
    {
        return @"Data Source=.;Initial Catalog=YourDatabase;Application Name=SqlDependencyExample;Integrated Security=SSPI";
    }

}

这是测试数据库的 DDL：

CREATE DATABASE YourDatabase;
GO
ALTER DATABASE YourDatabase SET ENABLE_BROKER;
GO
USE YourDatabase;
GO
CREATE TABLE dbo.Services(
      ServiceName varchar(100) NOT NULL 
        CONSTRAINT PK_Services PRIMARY KEY
    , ServicePrice decimal(10,2) NOT NULL
);
GO

这些查询触发 OnChanged 事件：

INSERT INTO dbo.Services VALUES('SomeService', 1.00);
GO
UPDATE dbo.Services SET ServicePrice = 2.00 WHERE ServiceName = 'SomeService';
GO
DELETE FROM dbo.Services WHERE ServiceName = 'SomeService';
GO

编辑：

如果您使用最低权限帐户运行您的应用程序（最佳实践），下面是一个示例脚本，用于授予SqlDependency.

--create user for schema ownership
CREATE USER SqlDependencySchemaOwner WITHOUT LOGIN;
GO
--create schema for SqlDependency objects
CREATE SCHEMA SqlDependency AUTHORIZATION SqlDependencySchemaOwner;
GO

--add existing login as a minimally privileged database user with default schema SqlDependency
CREATE USER YourLogin WITH DEFAULT_SCHEMA = SqlDependency;

--grant user control permissions on SqlDependency schema
GRANT CONTROL ON SCHEMA::SqlDependency TO YourLogin;

--grant user impersonate permissions on SqlDependency schema owner
GRANT IMPERSONATE ON USER::SqlDependencySchemaOwner TO YourLogin;
GO

--grant database permissions needed to create and use SqlDependency objects
GRANT CREATE PROCEDURE TO YourLogin;
GRANT CREATE QUEUE TO YourLogin;
GRANT CREATE SERVICE TO YourLogin;
GRANT REFERENCES ON
    CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] TO YourLogin;
GRANT VIEW DEFINITION TO YourLogin;
GRANT SELECT to YourLogin;
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO YourLogin;
GRANT RECEIVE ON QueryNotificationErrorsQueue TO YourLogin;
GO

--grant permissions on user objects used by application
GRANT SELECT ON dbo.Services TO YourLogin;
GO

推荐阅读