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

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

首先创建两个数据库: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.com/zh-cn/library/Microsoft.SqlServer.Server.SqlFunctionAttribute_properties(v=vs.100).aspx

http://blog.sina.com.cn/s/blog_59c41d0d0100esjn.html

http://www.cnblogs.com/wshcn/archive/2011/12/02/2271630.html

http://www.cnblogs.com/edong/archive/2010/03/10/1682172.html

http://www.cnblogs.com/hsrzyn/archive/2013/05/28/1976555.html

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。