SqlServer调用外部程序实现数据同步

首先创建两个数据库：SyncA是数据源，SyncB是对SyncA进行同步的数据库。在SyncA和SyncB中分别创建Source表和Target表，实际业务中，两张表的结构大多不相同。然后创建一个类库的项目：MySync（注意项目的版本，Sql08不支持的.net 4.0及更高版本）下面是同

首先创建两个数据库：SyncA是数据源，SyncB是对SyncA进行同步的数据库。

在SyncA和SyncB中分别创建Source表和Target表，实际业务中，两张表的结构大多不相同。

然后创建一个类库的项目：MySync（注意项目的版本，Sql08不支持的.net 4.0及更高版本）

下面是同步程序代码：

 using   System;
  using   System.Data;
  using   System.Data.Sql;
  using   Microsoft.SqlServer.Server;
  using   System.Data.SqlClient;
  using   System.Data.SqlTypes;

  namespace   MySync
{
      public   class   SyncDataBase
    {
       [SqlFunction(SystemDataAccess = SystemDataAccessKind.Read, DataAccess = DataAccessKind.Read)]
          public   static   string  Sync( string   strSql)
        {
              string  result =  "  true  "  ;

              string  strConn =  @"  Data Source=localhost;Initial Catalog=SyncB;User ID=sa;Password=123@abc;  "  ;
              try  
            {
                  using  (SqlConnection connection =  new   SqlConnection(strConn))
                {
                    connection.Open();
                    SqlCommand command  =  new   SqlCommand(strSql, connection);
                    command.CommandType  =  CommandType.Text;
                    command.ExecuteNonQuery();
                    connection.Close();
                }
            }
              catch   (Exception ex)
            {
                result  =  "  false:  "  +  ex.ToString();
            }

              return   result;
        }
    }
}

接下来要对类库项目进行签名，签名后编译【项目】：

启用CLR功能：默认情况下，Sql Server中的CLR是关闭的，所以我们要执行如下命令打开SyncA数据库的CLR。

 exec  sp_configure  '  clr enabled  ' , 1   
 reconfigure   
 go

注册DLL：

为了调用我们写的那个方法，需要在SQL Server中注册我们刚刚编译好的那个DLL。在此之前，要知道在这个项目中如果要访问服务器之外的资源是要配置权限的。如果不配置，后面操作中会出现类似下面的错误。我找到的关于授权配置的内容：连接。

创建登录名和密钥，如果程序集有变更，要删除密钥和登录名重新创建：

 USE   master; 
  GO   
 
 CREATE  ASYMMETRIC  KEY  SQLCLRSyncKey  FROM  EXECUTABLE  FILE   =   '  C:\MySync.dll  '   
 CREATE  LOGIN SQLCLRSyncLogin  FROM  ASYMMETRIC  KEY   SQLCLRSyncKey   
  GRANT  EXTERNAL ACCESS ASSEMBLY  TO   SQLCLRSyncLogin; 
  GO

 DROP   LOGIN SQLCLRSyncLogin
  DROP  ASYMMETRIC  KEY  SQLCLRSyncKey

创建程序集，DLL变更后要删除重新创建：

 USE   SyncA; 
  GO   

 create   ASSEMBLY MySync 
  FROM   '  C:\MySync.dll  ' 
 WITH  PERMISSION_SET  =   EXTERNAL_ACCESS;
  GO

然后创建一个函数用于调用这个DLL：

 CREATE   FUNCTION   dbo.fun_sync
(  
      @strSql   nvarchar ( max  )
)
  RETURNS   nvarchar ( max  )  
  AS  EXTERNAL NAME  [  MySync  ] . [  MySync.SyncDataBase  ] . [  Sync  ]

先来测试一下，在SyncA中执行查询：

 SELECT  dbo.fun_sync( '  insert into Target(Id,Name,SyncTime) values (null,null,getdate())  ' )

SyncB中添加了一条数据：

下面使用触发器自动的从SyncA中将数据同步到SyncB中，其中的tt表是我临时创建的，用于保存触发器调用返回的结果：

 create   Trigger   tr_source
  on   [  Source  ] 
 for   INSERT 

 AS 
 begin 
 declare   @strSql   nvarchar ( max  )
  select   @strSql  =  '  insert into Target(Id,Name,SyncTime) values (  '''  +  cast (Id  as   nvarchar ) +  '''  ,  '''  + Title +  '''  ,getdate())  '   from   inserted

  --  执行 
 declare   @result   nvarchar ( max  )
  select   @result  = dbo.fun_sync( @strSql  )

  insert   into  tt(tt)  values  ( @result  )
  end

直接执行函数没有问题，但是触发器去调用函数执行却出现异常：

 false:System.Data.SqlClient.SqlException: 其他会话正在使用事务的上下文。     
在 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)     
在 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)     
在 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)     
在 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)     
在 System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)     
在 System.Data.SqlClient.SqlInternalConnectionTds.PropagateTransactionCookie(Byte[] cookie)     
在 System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)     
在 System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)     
在 System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)    
在 System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)     
在 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)     
在 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)     
在 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)     
在 System.Data.SqlClient.SqlConnection.Open()     
在 MySync.SyncDataBase.Sync(String strSql)

这个错误中包含了一个false值，说明触发器调用时已经可以走到DLL这一步了。考虑到在查询中直接执行函数，走到DLL这一步是没有错误的。那么错误就发生在触发器和DLL调用产生的冲突，冲突在访问数据库上面，再深入的原因，我也没有找到。

下面使用另外一种方式实现同步，因为错误是触发器和DLL的数据库访问冲突，那么我就绕过数据库的访问。将触发器产生的SQL脚本保存到某个目录下面，然后通过其他程序监听这个目录，执行脚本文件，实现同步。

类库代码

 using   System;
  using   System.Data;
  using   System.Data.Sql;
  using   Microsoft.SqlServer.Server;
  using   System.Data.SqlClient;
  using   System.Data.SqlTypes;
  using   System.IO;

  namespace   MySync
{
      public   class   SyncDataBase
    {
        [SqlFunction(SystemDataAccess  = SystemDataAccessKind.Read, DataAccess =  DataAccessKind.Read)]
          public   static   string  Sync( string   strSql)
        {
              string  result =  "  true  "  ;

              try  
            {
                  if  (!Directory.Exists( "  c:\\SyncLog  "  ))
                {
                    Directory.CreateDirectory(  "  c:\\SyncLog  "  );
                }
                  string  fileName =  @"  c:\\SyncLog\\  "  + DateTime.Now.ToString( "  yyyyMMddHHmmss  " ) +  "  .txt  "  ;
                  if  ( File.Exists(fileName))
                    File.Delete(fileName);

                  using  (StreamWriter sw =  File.CreateText(fileName))
                {
                    sw.WriteLine(strSql);
                }
            }
              catch   (Exception ex)
            {
                result  =  "  false:  "  +  ex.ToString();
            }

              return   result;
        }
    }
}

另外创建一个监听程序：MyListen

 using   System;
  using   System.Data;
  using   System.Data.Sql;
  using   System.Data.SqlClient;
  using   System.Data.SqlTypes;
  using   System.Configuration;
  using   System.Threading;
  using   System.IO;

  namespace   MyListen
{
      class   Program
    {
          static   void  Main( string  [] args)
        {
              string  connSync = ConfigurationManager.ConnectionStrings[ "  connSync  "  ].ToString();
              string  filePath = ConfigurationManager.AppSettings[ "  filePath  "  ];
              while  ( true  )
            {
                  //  所有txt文件 
                 string [] fileList = DirFile.GetFileNames(filePath,  "  *.txt  " ,  true  );
                  foreach  ( var  f  in   fileList)
                {
                      string  strSql =  ""  ;
                      using  (StreamReader sr =  new   StreamReader(f))
                    {
                          string   line;
                          while  ((line = sr.ReadLine()) !=  null  )
                        {
                            strSql  += line +  "   "  ;
                        }
                        sr.Close();
                    }
                      try  
                    {
                          using  (SqlConnection connection =  new   SqlConnection(connSync))
                        {
                            connection.Open();
                            SqlCommand command  =  new   SqlCommand(strSql, connection);
                            command.CommandType  =  CommandType.Text;
                            command.ExecuteNonQuery();
                            connection.Close();
                        }
                    }
                      catch   (Exception ex)
                    {
                        Console.WriteLine(ex.ToString());
                    }
                    File.Delete(f);
                }
                  //  每10秒扫描一次 
                Thread.Sleep( 5  *  1000  );
            }
        }
    }
}

只要将监听程序打开，就可以实现对数据的同步。项目和数据库下载。

参考：

http://msdn.microsoft测试数据/zh-cn/library/Microsoft.SqlServer.Server.SqlFunctionAttribute_properties(v=vs.100).aspx

http://blog.sina测试数据.cn/s/blog_59c41d0d0100esjn.html

http://HdhCmsTestcnblogs测试数据/wshcn/archive/2011/12/02/2271630.html

http://HdhCmsTestcnblogs测试数据/edong/archive/2010/03/10/1682172.html

http://HdhCmsTestcnblogs测试数据/hsrzyn/archive/2013/05/28/1976555.html

查看更多关于SqlServer调用外部程序实现数据同步的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did97983

更新时间：2022-10-27 阅读：49次