C#实现数据库数据变化监测（sqlserver&mysql）

监测数据库表数据变化，可实现数据库同步（一主一从（双机备份），一主多从（总部数据库，工厂1，工厂2，工厂数据合并到总部数据））

sqlserver

思路1（update和delete ）：

-- 创建表
CREATE TABLE [dbo].[LOGR](
[USERID] [varchar](50) NULL,
[STRSQL] [varchar](max) NULL,
[DATE] [varchar](8) NULL,
[TIME] [varchar](8) NULL,
[TABLNM] [nvarchar](50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

-- 创建触发器
CREATE TRIGGER [dbo].[KNMT_LOG] ON [dbo].[student8]
FOR UPDATE, DELETE
AS

DECLARE @CLIENT AS VARCHAR(50)
DECLARE @DATE AS VARCHAR(8)
DECLARE @TIME AS VARCHAR(8)
DECLARE @USERNAME AS VARCHAR(50)
DECLARE @STATMT AS VARCHAR(max)
DECLARE @strSQL AS VARCHAR(100)

SET @CLIENT = HOST_NAME()
SET @DATE = CONVERT(VARCHAR(8),GETDATE(),112)
SET @TIME =REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')
SET @strSQL='DBCC INPUTBUFFER('+CAST(@@SPID AS VARCHAR(50))+')'

CREATE TABLE #STATEMENT (C1 VARCHAR(50),C2 VARCHAR(50),C3 VARCHAR(5000))
INSERT INTO #STATEMENT EXEC(@strSQL)

SELECT @STATMT=C3 FROM #STATEMENT

INSERT INTO LOGR(USERID,STRSQL,DATE,TIME,TABLNM) VALUES(@CLIENT,@STATMT,@DATE,@TIME,'PATIENT')

--测试
update student8 set Name='66' where Id=6
delete from student8 where id=8

思路2：启用数据库监听服务
ALTER DATABASE test SET NEW_BROKER WITH ROLLBACK IMMEDIATE; ALTER DATABASE test SET ENABLE_BROKER;
SELECT is_broker_enabled FROM sys.databases WHERE name='test';

Nuget：SqlTableDependency

mysql监听思路

1. 启用二进制日志

[mysqld]

...
log-bin=mysql_bin
binlog-format=statement

2. 查看是否开启日志
SHOW VARIABLES like '%log_bin%'