如何使用C#监控SQL Server表的更改？

Question

我有多个应用程序访问同一个数据库，我需要在其中一个应用程序更改某个表格（更新、插入）时得到通知。

数据库和应用程序不在同一台服务器上。

- ToDayIsNow

---

你需要什么样的通知？是即时通知吗？你需要一个应用来通知你，还是需要发送电子邮件通知？你真的需要被通知，还是只是想要追踪这些变化？ - richard

我只需要知道是否有其他应用程序更新或插入任何数据，我不需要数据本身，只需要一个标志表明此表有新的更改。很抱歉回复晚了，我不知道答案会这么快。 - ToDayIsNow

Answer 1

为了完整起见，还有一些其他解决方案（在我看来）比依赖于SqlDependency（和SqlTableDependency）类更正统。SqlDependency最初是为了使刷新分布式Web服务器缓存更容易而设计的，因此它的设计要求与作为事件生产者的设计要求不同。

总体上有四个选项，其中一些在这里还没有涉及：

- 更改跟踪 - CDC（Change Data Capture） - 触发器到队列 - CLR（Common Language Runtime） 更改跟踪 来源：https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/about-change-tracking-sql-server 变更跟踪是SQL Server中的一种轻量级通知机制。基本上，每次对任何数据进行更改时，都会递增一个数据库范围的版本号。然后，版本号将与包含已更改的列名称的位掩码一起写入变更跟踪表中。请注意，实际更改不会持久保存。通知只包含特定数据实体已更改的信息。此外，由于更改表版本是累积的，对个别项的更改通知不会被保留，并且会被较新的通知覆盖。这意味着如果一个实体更改了两次，变更跟踪只会知道最近的更改。

为了在C#中捕获这些更改，必须使用轮询。可以轮询变更跟踪表，并检查每个更改以确定其是否感兴趣。如果感兴趣，则需要直接访问数据以检索当前状态。

变更数据捕获

来源：https://technet.microsoft.com/en-us/library/bb522489(v=sql.105).aspx 变更数据捕获（CDC）比变更跟踪更强大，但成本更高。变更数据捕获将根据监视数据库日志来跟踪和通知变更。因此，CDC可以访问已更改的实际数据，并记录所有单个变更。

与变更跟踪类似，为了在C#中捕获这些变更，必须使用轮询。然而，在CDC的情况下，轮询的信息将包含变更的详细信息，因此不一定需要返回到数据本身。 触发器到队列 编辑这种方法曾经由Microsoft在一篇知识文章中进行了记录，并附带了一个示例应用程序，但现在已经被撤下，所以我不再认为这是一种“支持”的方法。

这种技术依赖于需要通知的表上的触发器。每个变更都会触发一个触发器，触发器将把这些信息写入服务代理队列。可以通过C#使用服务代理消息处理器（链接中的示例）连接到队列。

与变更跟踪或CDC不同，触发器到队列不依赖于轮询，因此提供了实时事件处理。 CLR 这是一种我见过的技术，但我不建议使用。任何依赖CLR与外部通信的解决方案都充其量是一种破解。CLR的设计初衷是通过利用C#来简化编写复杂数据处理代码的过程，而不是用于集成外部依赖，比如消息传递库。此外，CLR绑定的操作在集群环境中可能会以不可预测的方式出现故障。

话虽如此，设置起来相当简单，只需将消息传递程序集注册到CLR中，然后就可以使用触发器或SQL作业进行调用。 总结： 我一直对微软坚决拒绝解决这个问题领域感到惊讶。从数据库到代码的事件传递应该是数据库产品的内置功能。考虑到Oracle Advanced Queuing与ODP.net的MessageAvailable事件在10多年前就为C#提供了可靠的数据库事件传递，这对于微软来说是令人遗憾的。

因此，列出的这些问题的解决方案都不是很好。它们都有技术上的缺陷，并且设置成本相当高。如果微软在听，请解决这个令人遗憾的局面。

- tom redfern

---

为什么将触发器转换为队列已经过时了？ - Kurren

对我来说是同样的问题。 - undefined

@Kurren 我已经更新以反映最新情况。 - undefined

@LucVandenbroucke 我已经更新以反映最新情况。 - undefined

Answer 2

您可以使用SqlDependency Class。它的主要用途是为ASP.NET页面提供服务（客户端通知数量较少）。

ALTER DATABASE UrDb SET ENABLE_BROKER

实现OnChange事件以接收通知：

void OnChange(object sender, SqlNotificationEventArgs e)

而且在代码中：

SqlCommand cmd = ...
cmd.Notification = null;

SqlDependency dependency = new SqlDependency(cmd);

dependency.OnChange += OnChange;

它使用Service Broker（一种基于消息的通信平台）来接收来自数据库引擎的消息。

- Jaroslav Jandek

---

@jaroslav jandek，你好。除了sqldependency之外，你知道其他的方法吗？我在使用sqldependency时遇到了问题，因为它有很多限制，比如OUTER JOIN；而我几乎在所有的SQL查询中都使用了它！ - M_Mogharrabi

@M_Mogharrabi 通知是使用索引完成的，无法与外连接一起使用。您需要手动使用外连接进行连接操作，作为单独的查询。在大多数情况下，我会尽量避免这种情况。 - Jaroslav Jandek

@Kiquenet 这里的问题不在于SB的性能。但是查询通知可能会对数据库性能产生重大影响，特别是如果有很多通知（如我的答案中所提到的）。如果是这种情况，最好使用轮询、SOA等方式来解决。 - Jaroslav Jandek

我曾使用SqlSependency来触发数据库更改以向客户端显示推送通知，但最近我们转移到了SQL Azure，它不支持SqlSependency。那么，在SQL Azure数据更改或插入新数据时，有没有比这个更好的方法来获取通知呢？ - Shaiju T

@stom，据我所知，目前没有替代方案。如果您控制数据输入，可以轻松使用SignalR或类似技术进行通知... - Jaroslav Jandek

是的，你说得对，我已经开始使用SignalR了，但是我有一个问题，就像在这里提到的一样，由于性能原因，Chrome浏览器在不活动时禁用JavaScript。所以一些客户将浏览器最小化，当我使用SignalR向客户发送通知并尝试通过调用JavaScript函数播放声音时，它不起作用。有什么建议吗？ - Shaiju T

Answer 3

通常，你会使用Service Broker

这是触发器 -> 队列 -> 应用程序(s)

另外，在查看其他答案后编辑:

FYI: "Query Notifications" 建立在 Service broker 之上

编辑2：

更多链接

• 事件通知/Service broker
• Service Broker 团队

- gbn

---

我之前使用SqlSependency触发数据库更改以向客户端显示推送通知，但最近我们转移到了SQL Azure，它不支持SqlSependency，所以有没有比这个更好的方法来在SQL Azure数据更改或插入新数据时获取通知？ - Shaiju T

Answer 4

使用SqlTableDependency。它是一个C#组件，当记录更改时会触发事件。 您可以在此处找到其他详细信息：https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency

与.NET SqlDependency相似，但SqlTableDependency会引发包含修改/删除或更新的数据库表值的事件：

string conString = "data source=.;initial catalog=myDB;integrated security=True";

using(var tableDependency = new SqlTableDependency<Customers>(conString))
{
    tableDependency.OnChanged += TableDependency_Changed;
    tableDependency.Start();

    Console.WriteLine("Waiting for receiving notifications...");
    Console.WriteLine("Press a key to stop");
    Console.ReadKey();
}
...
...
void TableDependency_Changed(object sender, RecordChangedEventArgs<Customers> e)
{
    if (e.ChangeType != ChangeType.None)
    {
        var changedEntity = e.Entity;
        Console.WriteLine("DML operation: " + e.ChangeType);
        Console.WriteLine("ID: " + changedEntity.Id);
        Console.WriteLine("Name: " + changedEntity.Name);
        Console.WriteLine("Surname: " + changedEntity.Surname);
    }
}

- Christian Del Bianco

---

使用SqlDependency的性能如何？ - Kiquenet

@Kiquenet， SqlDependency旨在用于ASP.NET或中间层服务，其中有相对较少的服务器针对数据库具有依赖性。它不适用于客户端应用程序，在这些应用程序中，数百或数千台客户端计算机将为单个数据库服务器设置SqlDependency对象。 - Alex

Answer 5

使用SqlDependency类时要小心 - 它存在内存泄漏的问题。

只需使用跨平台、.NET 3.5、.NET Core兼容且开源的解决方案 - SqlDependencyEx。您可以获取通知以及已更改的数据（可以通过通知事件对象中的属性访问它）。您还可以单独或一起跟踪DELETE\UPDATE\INSERT操作。

以下是使用SqlDependencyEx的示例，非常简单：

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

请点击链接查看详细信息。此组件已在许多企业级应用程序中进行了测试，并被证明是可靠的。希望这可以帮到您。

- dyatchenko

---

它与Sql Express兼容吗？ - dmigo

Answer 6

SqlDependency不会监视数据库，它监视您指定的SqlCommand，因此，如果您尝试在一个项目中插入值并在另一个项目中捕获该事件，它将无法工作，因为事件来自于第一个项目的SqlCommand而不是数据库，因为当您创建SqlDependency时，您将其链接到一个SqlCommand，只有在使用该项目的命令时才会创建Change事件。

- The Reptilian Army

---

实际上这并不正确。即使在Management Studio中插入值，SqlDependency也可以工作。但是，这个类存在许多问题，比如内存泄漏。请参见下面我的答案以了解详细信息。@KayLee - dyatchenko

@dyatchenko，谢谢你的意见。我正在使用SqlTableDependency，这是在此帖子的一个答案中提到的。我现在非常忙，但稍后会查看内存问题，当然... - Kay Lee

Answer 7

自从 SQL Server 2005 开始，你可以选择使用查询通知，ADO.NET 可以利用这一特性，详情请见http://msdn.microsoft.com/en-us/library/t9x04ed2.aspx

- Chris Taylor

Answer 8

看起来整个架构都很糟糕。此外，您还没有指定需要通知的应用程序类型（Web应用程序/控制台应用程序/WinForms/服务等）。

尽管如此，为了回答您的问题，有多种解决方法。您可以使用：

1）时间戳，如果您只是希望确保第二个应用程序的下一组更新不会与第一个应用程序的更新冲突；

2）SQL依赖对象-请参阅http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldependency.aspx以获取更多信息；

3）自定义推送通知服务，多个客户端（Web / WinForm / Service）可以订阅并在更改时收到通知。

简而言之，您需要根据通知要求的复杂程度和使用目的选择最简单、最容易和最便宜（就努力而言）的解决方案。如果您的唯一要求是简单的数据并发性，则不要尝试构建过于复杂的通知系统（在这种情况下，请选择基于简单时间戳的解决方案）。

- Raj

---

只是出于好奇，你能否澄清一下这里的“糟糕架构”具体指什么？ - James

Answer 9

1-创建名为TestNotification的新数据库

2-添加名为Customers的新表，字段：Id，Name，Family

3-您应启用ServiceBroker

4-在SQL中运行此代码

ALTER DATABASE [TestNotification] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE

5- 创建新项目 c# consoleApp

6- 在 nuget 中安装 SqlTableDependency 库

7- 创建名为 Customer 的类

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }
}

在 Program.cs 文件中编写以下代码：

        static void Main(string[] args)
        {
            var connectionString = "data source=.;initial catalog=TestNotification;integrated security=true;";
            using (var tableDependecy = new SqlTableDependency<Customer>(connectionString, "Customers"))
            {
                tableDependecy.OnChanged += TableDependency_Changed;
                tableDependecy.OnError += TableDependency_OnError;

                tableDependecy.Start();

                Console.WriteLine("Waiting");

                Console.ReadKey();
                tableDependecy.Stop();
            }
        }

        static void TableDependency_Changed(object sender, RecordChangedEventArgs<Customer> e)
        {
            Console.WriteLine(Environment.NewLine);
            if (e.ChangeType != ChangeType.None)
            {
                var changeEntity = e.Entity;
                Console.WriteLine("ChangeType: " + e.ChangeType);
                Console.WriteLine("Id: " + changeEntity.Id);
                Console.WriteLine("Name: " + changeEntity.Name);
                Console.WriteLine("Id: " + changeEntity.Family);
                Console.WriteLine(Environment.NewLine);
            }
        }

        static void TableDependency_OnError(object sender, ErrorEventArgs e)
        {
            Console.WriteLine(e.Message);
        }

- Diako Hasani

Answer 10

这并不完全是一条通知，但标题中提到了监控，它可以适用于这种情况。

使用SQL Server时间戳列可以轻松查看在查询之间仍然存在的任何更改。

在我看来，SQL Server时间戳列类型的命名非常糟糕，因为它与时间无关，它是一个数据库范围内的值，在任何插入或更新时自动增加。您可以在您想要的表中选择Max(timestamp)，或者从刚刚插入的行中返回时间戳，然后只需选择 where timestamp > storedTimestamp，这将给出所有在这些时间之间已更新或插入的结果。

由于它是一个数据库范围内的值，因此您可以使用存储的时间戳来检查自上次检查/更新存储的时间戳以来是否有数据写入任何表。

- Matt