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及更高版本）

下面是同步程序代码：

<span>using</span><span> System;
</span><span>using</span><span> System.Data;
</span><span>using</span><span> System.Data.Sql;
</span><span>using</span><span> Microsoft.SqlServer.Server;
</span><span>using</span><span> System.Data.SqlClient;
</span><span>using</span><span> System.Data.SqlTypes;

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

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

            </span><span>return</span><span> result;
        }
    }
}</span>

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

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

<span>exec</span> sp_configure <span>'</span><span>clr enabled</span><span>'</span>,<span>1</span>  
<span>reconfigure</span>  
<span>go</span>

注册DLL：

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

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

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

<span>DROP</span><span> LOGIN SQLCLRSyncLogin
</span><span>DROP</span> ASYMMETRIC <span>KEY</span> SQLCLRSyncKey

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

<span>USE</span><span> SyncA; 
</span><span>GO</span>  

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

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

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

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

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

SyncB中添加了一条数据：

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

<span>create</span> <span>Trigger</span><span> tr_source
</span><span>on</span> <span>[</span><span>Source</span><span>]</span>
<span>for</span> <span>INSERT</span>

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

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

</span><span>insert</span> <span>into</span> tt(tt) <span>values</span> (<span>@result</span><span>)
</span><span>end</span>

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

<span>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)</span>

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

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

类库代码

<span>using</span><span> System;
</span><span>using</span><span> System.Data;
</span><span>using</span><span> System.Data.Sql;
</span><span>using</span><span> Microsoft.SqlServer.Server;
</span><span>using</span><span> System.Data.SqlClient;
</span><span>using</span><span> System.Data.SqlTypes;
</span><span>using</span><span> System.IO;

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

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

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

            </span><span>return</span><span> result;
        }
    }
}</span>

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

<span>using</span><span> System;
</span><span>using</span><span> System.Data;
</span><span>using</span><span> System.Data.Sql;
</span><span>using</span><span> System.Data.SqlClient;
</span><span>using</span><span> System.Data.SqlTypes;
</span><span>using</span><span> System.Configuration;
</span><span>using</span><span> System.Threading;
</span><span>using</span><span> System.IO;

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

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

参考：

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