sql server之觸發器調用C#CLR預存程序實現兩個表的資料同步

第一步：開啟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')