第一步：开启CLR
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '1';
go
二、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;
GO
USE master
GO
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
GO
四、部署ASSEMBLY
USE pdxzsp;
GO
IF OBJECT_ID('dbo.Sync') IS NOT NULL
DROP PROC Sync;
GO
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))
AS
EXTERNAL NAME SqlServerProject1.SyncDll.Sync
Go
六、触发器里调用存储过程，然后在存储过程里做同步，存储过程里开了一个线程去做这些事情，原理：
往本地数据库的表SA_AFFAIR_THEME里插入数据，然后触发器里获取这条数据，传入到CLR存储过程里，
然后开个线程，写入到远程数据库的表SA_AFFAIR_THEME里
DROP TRIGGER [tgr_Theme_insert]
CREATE TRIGGER [tgr_Theme_insert]
ON [dbo].[SA_Affair_Theme]
after INSERT --插入触发
AS
BEGIN
DECLARE @atID INT
DECLARE @atName NVARCHAR(500)
DECLARE @atMemo NVARCHAR(3000)
DECLARE @atUpperID NVARCHAR(200)
DECLARE @atIcon NVARCHAR(200)
DECLARE @atFlow NVARCHAR(3000)
DECLARE @atFlowImage NVARCHAR(200)
SELECT @atID=atID,@atName=atName,@atMemo=atMemo,@atUpperID=atUpperID,@atIcon=atIcon,@atFlow=atFlow,@atFlowImage=atFlowImage from inserted
EXEC dbo.Sync @atID,@atName,@atMemo,@atUpperID,@atIcon,@atFlow,@atFlowImage
END
最后插入数据看看：INSERT INTO dbo.sa_affair_theme VALUES(2, 'aa','bb','cc','dd','ee','ff')
