在.Net Core（.Net5）中使用开源组件SqlTableDependency来监听ms sqlserver的数据库数据变化

1、本文主要说明在.Net Core（Demo为.Net5）中使用开源组件SqlTableDependency来监听ms sqlserver的数据库数据变化

2、github地址：https://github.com/IsNemoEqualTrue/monitor-table-change-with-sqltabledependency

3、安装nuget包：install-package SqlTableDependency

4、准备数据库脚本

-- 新建表
create table UserInfoForTestSqlTableDependency(
    Id int not null primary key identity(1,1),
    Name varchar(50) not null,
    NickName varchar(50) not null,
    Sex int not null,
    Birthday datetime not null,
    Status int not null,
    CreateTime datetime not null,
    LastModifyTime datetime not null
)

-- 插入数据：测试SqlTableDependency
insert into UserInfoForTestSqlTableDependency(Name,NickName,Sex,Birthday,Status,CreateTime,LastModifyTime)
values('用户-测试SqlTableDependency','昵称-测试SqlTableDependency',1,'1996-02-27',1,getdate(),getdate())

-- 插入数据：测试SqlTableDependency
insert into UserInfoForTestSqlTableDependency(Name,NickName,Sex,Birthday,Status,CreateTime,LastModifyTime)
values('用户1-测试SqlTableDependency','昵称1-测试SqlTableDependency',2,'1995-11-21',1,getdate(),getdate())

-- 更新单条数据：测试SqlTableDependency
update UserInfoForTestSqlTableDependency set
Name = '修改用户-测试SqlTableDependency',
NickName = '修改用户昵称-测试SqlTableDependency',
Status = 1, -- 该字段未变化，更新为原始值，测试是否会通知
LastModifyTime = getdate()
where Id = 1

-- 更新多条数据：测试SqlTableDependency（会收到多条通知）
update UserInfoForTestSqlTableDependency set
LastModifyTime = getdate()

-- 删除数据：测试SqlTableDependency
delete from UserInfoForTestSqlTableDependency where Id = 1

-- 查询数据
select * from UserInfoForTestSqlTableDependency

5、相关代码（所有注释说明在代码中都有）

using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TableDependency.SqlClient;
using TableDependency.SqlClient.Base;
using TableDependency.SqlClient.Base.Enums;
using TableDependency.SqlClient.Base.EventArgs;
using TestMysqlConsole.Logs;
using TestMysqlConsole.TestLiteDB.Entitys;

namespace TestMysqlConsole.TestLiteDB.Services
{
    /// <summary>
    /// 测试SqlTableDependency服务
    /// </summary>
    public class TestSqlTableDependencyService
    {
        /// <summary>
        /// 运行
        /// </summary>
        public static void Run()
        {
            /*
             * 说明：
             * github地址：https://github.com/IsNemoEqualTrue/monitor-table-change-with-sqltabledependency
             * 安装nuget包：install-package SqlTableDependency
             *
             * mssql数据库脚本脚本：
             * create table UserInfoForTestSqlTableDependency(
             *     Id int not null primary key identity(1,1),
             *     Name varchar(50) not null,
             *     NickName varchar(50) not null,
             *     Sex int not null,
             *     Birthday datetime not null,
             *     Status int not null,
             *     CreateTime datetime not null,
             *     LastModifyTime datetime not null
             * )
             *
             * 【增】手动执行sql，插入数据，查看收到的通知事件数据和日志：
             * -- 插入数据：测试SqlTableDependency
             * insert into UserInfoForTestSqlTableDependency(Name,NickName,Sex,Birthday,Status,CreateTime,LastModifyTime)
             * values('用户-测试SqlTableDependency','昵称-测试SqlTableDependency',1,'1996-02-27',1,getdate(),getdate())
             *
             * 【改】手动执行sql，修改数据，查看收到的通知事件数据和日志：
             * -- 更新数据：测试SqlTableDependency
             * update UserInfoForTestSqlTableDependency set
             * Name = '修改用户-测试SqlTableDependency',
             * NickName = '修改用户昵称-测试SqlTableDependency',
             * Status = 1, -- 该字段未变化，更新为原始值，测试是否会通知
             * LastModifyTime = getdate()
             * where Id = 1
             *
             * 【改】手动执行sql，批量修改数据，查看收到的通知事件数据和日志（会受到两条通知）：
             * -- 批量更新数据：测试SqlTableDependency
             * update UserInfoForTestSqlTableDependency set
             * LastModifyTime = getdate()
             *
             * 【删】手动执行sql，删除数据，查看收到的通知事件数据和日志：
             * -- 删除数据：测试SqlTableDependency
             * delete from UserInfoForTestSqlTableDependency where Id = 1
             */

            //初始化
            var mapper = new ModelToTableMapper<UserEntityForTestLiteDB>();
            //映射
            mapper.AddMapping(r => r.Id, nameof(UserEntityForTestLiteDB.Id));
            mapper.AddMapping(r => r.Name, nameof(UserEntityForTestLiteDB.Name));
            mapper.AddMapping(r => r.NickName, nameof(UserEntityForTestLiteDB.NickName));
            mapper.AddMapping(r => r.Sex, nameof(UserEntityForTestLiteDB.Sex));
            mapper.AddMapping(r => r.Birthday, nameof(UserEntityForTestLiteDB.Birthday));
            mapper.AddMapping(r => r.Status, nameof(UserEntityForTestLiteDB.Status));
            mapper.AddMapping(r => r.CreateTime, nameof(UserEntityForTestLiteDB.CreateTime));
            mapper.AddMapping(r => r.LastModifyTime, nameof(UserEntityForTestLiteDB.LastModifyTime));
            //表名
            string tableName = "UserInfoForTestSqlTableDependency";
            using (var sqlTableDependency = new SqlTableDependency<UserEntityForTestLiteDB>(GetMsSqlConnectionString(), tableName, mapper: mapper))
            {
                //赋值
                sqlTableDependency.OnChanged += OnChanged;
                //开始
                sqlTableDependency.Start();
                //打印
                Console.WriteLine("已开始监控，输入任何字符以停止监控");
                //读取
                var line = Console.ReadLine();
                //停止
                sqlTableDependency.Stop();
            }
        }

        /// <summary>
        /// 测试SqlTableDependency - 接收数据库的变化通知
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public static void OnChanged(object sender, RecordChangedEventArgs<UserEntityForTestLiteDB> e)
        {
            //log
            string log = LingbugJsonHelper.ToJson(e, null);
            //记录日志
            LogService.InfoNoWrap(log);
            //打印
            Console.WriteLine($"{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")} 收到数据库通知：{log}");
        }

        /// <summary>
        /// 获取mssql数据库连接
        /// </summary>
        /// <returns></returns>
        public static string GetMsSqlConnectionString()
        {
            //获取mssql数据库连接
            return "server=.;database=xuyulin;uid=sa;pwd=你的数据库密码;";
        }
    }
}

6、运行结果日志

2025-03-17 18: 22: 22.329：{
    "Entity": {
        "Id": 1,
        "Name": "用户-测试SqlTableDependency",
        "NickName": "昵称-测试SqlTableDependency",
        "Sex": 1,
        "Birthday": "1996-02-27T00:00:00",
        "Status": 1,
        "CreateTime": "2025-03-17T18:22:22.127",
        "LastModifyTime": "2025-03-17T18:22:22.127"
    },
    "EntityOldValues": null,
    "ChangeType": 2,
    "CultureInfo": "en-US",
    "Server": ".",
    "Database": "xuyulin",
    "Sender": "dbo_UserInfoForTestSqlTableDependency_d88ab812-40f3-418b-ba2e-70ff692283d5"
}
2025-03-17 18: 25: 30.617：{
    "Entity": {
        "Id": 1,
        "Name": "修改用户-测试SqlTableDependency",
        "NickName": "修改用户昵称-测试SqlTableDependency",
        "Sex": 1,
        "Birthday": "1996-02-27T00:00:00",
        "Status": 1,
        "CreateTime": "2025-03-17T18:22:22.127",
        "LastModifyTime": "2025-03-17T18:25:30.547"
    },
    "EntityOldValues": null,
    "ChangeType": 3,
    "CultureInfo": "en-US",
    "Server": ".",
    "Database": "xuyulin",
    "Sender": "dbo_UserInfoForTestSqlTableDependency_d88ab812-40f3-418b-ba2e-70ff692283d5"
}
2025-03-17 18: 26: 57.932：{
    "Entity": {
        "Id": 1,
        "Name": "修改用户-测试SqlTableDependency",
        "NickName": "修改用户昵称-测试SqlTableDependency",
        "Sex": 1,
        "Birthday": "1996-02-27T00:00:00",
        "Status": 1,
        "CreateTime": "2025-03-17T18:22:22.127",
        "LastModifyTime": "2025-03-17T18:25:30.547"
    },
    "EntityOldValues": null,
    "ChangeType": 1,
    "CultureInfo": "en-US",
    "Server": ".",
    "Database": "xuyulin",
    "Sender": "dbo_UserInfoForTestSqlTableDependency_d88ab812-40f3-418b-ba2e-70ff692283d5"
}
2025-03-17 18: 30: 36.704：{
    "Entity": {
        "Id": 2,
        "Name": "用户1-测试SqlTableDependency",
        "NickName": "昵称1-测试SqlTableDependency",
        "Sex": 2,
        "Birthday": "1995-11-21T00:00:00",
        "Status": 1,
        "CreateTime": "2025-03-17T18:30:36.463",
        "LastModifyTime": "2025-03-17T18:30:36.463"
    },
    "EntityOldValues": null,
    "ChangeType": 2,
    "CultureInfo": "en-US",
    "Server": ".",
    "Database": "xuyulin",
    "Sender": "dbo_UserInfoForTestSqlTableDependency_dd0df3bf-4d65-42b1-affb-ff0eaa75b959"
}
2025-03-17 18: 31: 35.797：{
    "Entity": {
        "Id": 3,
        "Name": "用户-测试SqlTableDependency",
        "NickName": "昵称-测试SqlTableDependency",
        "Sex": 1,
        "Birthday": "1996-02-27T00:00:00",
        "Status": 1,
        "CreateTime": "2025-03-17T18:31:35.747",
        "LastModifyTime": "2025-03-17T18:31:35.747"
    },
    "EntityOldValues": null,
    "ChangeType": 2,
    "CultureInfo": "en-US",
    "Server": ".",
    "Database": "xuyulin",
    "Sender": "dbo_UserInfoForTestSqlTableDependency_dd0df3bf-4d65-42b1-affb-ff0eaa75b959"
}
2025-03-17 18: 32: 33.257：{
    "Entity": {
        "Id": 2,
        "Name": "用户1-测试SqlTableDependency",
        "NickName": "昵称1-测试SqlTableDependency",
        "Sex": 2,
        "Birthday": "1995-11-21T00:00:00",
        "Status": 1,
        "CreateTime": "2025-03-17T18:30:36.463",
        "LastModifyTime": "2025-03-17T18:32:33.25"
    },
    "EntityOldValues": null,
    "ChangeType": 3,
    "CultureInfo": "en-US",
    "Server": ".",
    "Database": "xuyulin",
    "Sender": "dbo_UserInfoForTestSqlTableDependency_dd0df3bf-4d65-42b1-affb-ff0eaa75b959"
}
2025-03-17 18: 32: 33.261：{
    "Entity": {
        "Id": 3,
        "Name": "用户-测试SqlTableDependency",
        "NickName": "昵称-测试SqlTableDependency",
        "Sex": 1,
        "Birthday": "1996-02-27T00:00:00",
        "Status": 1,
        "CreateTime": "2025-03-17T18:31:35.747",
        "LastModifyTime": "2025-03-17T18:32:33.25"
    },
    "EntityOldValues": null,
    "ChangeType": 3,
    "CultureInfo": "en-US",
    "Server": ".",
    "Database": "xuyulin",
    "Sender": "dbo_UserInfoForTestSqlTableDependency_dd0df3bf-4d65-42b1-affb-ff0eaa75b959"
}