mysql clr_sql server之触发器调用C#CLR存储过程实现两个表的数据同步 | 学步园

ゝ一纸荒年。 2022-10-31 01:54 193阅读 0赞

第一步：开启CLR

sp\_configure 'show advanced options', 1;

RECONFIGURE;

sp\_configure 'Ole Automation Procedures', 1;

RECONFIGURE;

EXEC sp\_configure 'show advanced options' , '1';

reconfigure;

EXEC sp\_configure 'clr enabled' , '1'

reconfigure;

\-- Turn advanced options back off

EXEC sp\_configure 'show advanced options' , '1';

二、c\#编写的存储过程，注意没有要放命名空间

public class SyncDll

\[SqlProcedure\]

public static void Sync(int atID, string atName, string atMemo, string atUpperID, string atIcon, string atFlow, string atFlowImage)

System.Threading.ParameterizedThreadStart start = new System.Threading.ParameterizedThreadStart(SyncDll.Test2);

System.Threading.Thread thread = new System.Threading.Thread(start);

System.Collections.Hashtable par = new System.Collections.Hashtable();

par\["atID"\] = atID;

par\["atName"\] = atName;

par\["atMemo"\] = atMemo;

par\["atUpperID"\] = atUpperID;

par\["atIcon"\] = atIcon;

par\["atFlow"\] = atFlow;

par\["atFlowImage"\] = atFlowImage;

thread.Start(par);

public static void Test2(object obj)

string strConn = "Data Source=192.168.6.68;Initial catalog=pdxzsp;uid=sa;pwd=123456;";

SqlConnection conn = new SqlConnection(strConn);

try

Hashtable par = (Hashtable)obj;

conn.Open();

string sql = String.Format("insert into sa\_affair\_theme values(\{0\},'\{1\}','\{2\}','\{3\}','\{4\}','\{5\}','\{6\}')", (int)par\["atID"\],

par\["atName"\].ToString(), par\["atMemo"\].ToString(), par\["atUpperID"\].ToString(), par\["atIcon"\].ToString(), par\["atFlow"\].ToString(), par\["atFlowImage"\].ToString());

SqlCommand cmd = new SqlCommand(sql, conn);

cmd.ExecuteNonQuery();

catch

finally

conn.Close();

三、开启登陆权限，这一步应该也可以不要

ALTER DATABASE pdxzsp SET TRUSTWORTHY ON;

USE master

CREATE ASYMMETRIC KEY sqldlldemoKey

FROM EXECUTABLE FILE =

'D:\\study\\demo\\SqlServerProject1\\SqlServerProject1\\bin\\Debug\\SqlServerProject1.dll'

\-- Create login and grant it with external access permission

CREATE LOGIN sqldllLogin FROM ASYMMETRIC KEY sqldlldemoKey

GRANT EXTERNAL ACCESS ASSEMBLY TO sqldllLogin

四、部署ASSEMBLY

USE pdxzsp;

IF OBJECT\_ID('dbo.Sync') IS NOT NULL

DROP PROC Sync;

DROP ASSEMBLY SqlServerProject1

create ASSEMBLY SqlServerProject1 FROM 'D:\\study\\demo\\SqlServerProject1\\SqlServerProject1\\bin\\Debug\\SqlServerProject1.dll' WITH PERMISSION\_SET=UNSAFE

五、创建存储过程

CREATE PROCEDURE dbo.Sync(

@atID int,

@atName nvarchar(500),

@atMemo nvarchar(3000),

@atUpperID nvarchar(200),

@atIcon nvarchar(200),

@atFlow nvarchar(3000),

@atFlowImage nvarchar(200))

EXTERNAL NAME SqlServerProject1.SyncDll.Sync

六、触发器里调用存储过程，然后在存储过程里做同步，存储过程里开了一个线程去做这些事情，原理：

往本地数据库的表SA\_AFFAIR\_THEME里插入数据，然后触发器里获取这条数据，传入到CLR存储过程里，