SqlServer调用外部程序实现数据同步-mysql教程-PHP中文网

首页

数据库

mysql教程

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 07, 2016 pm 03:21 PM

sqlserver同步外部实现数据程序调用首先

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

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

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

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

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

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

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

下面是同步程序代码：

<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>

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

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

启用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>

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

注册DLL：

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

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

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

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

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

SyncB中添加了一条数据：

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

下面使用触发器自动的从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

声明

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn

MySQL：字符串数据类型可用哪些字符集？May 10, 2025 am 12:07 AM

mysqloffersvariouscharactersetsforstringdatatypes：1）latin1 forwesterneuropeanlanguages，2）utf8 formultingualsupport，3）utf8mb4f OREXTEDENDENDENENICODECLUDINGEMOJIS，4）UCS2FORIXED-WIDTHENCODING，5）assiiforbasiclatin.ChoosideStherightStetSetensensersdaintegrity

mysql：斑点流比存储它们更好吗？May 10, 2025 am 12:06 AM

流式传输BLOB确实比直接存储更好，因为它能减少内存使用和提高性能。1）通过逐步读取和处理文件，避免了数据库膨胀和性能下降。2）流式传输需要更复杂的代码逻辑，且可能增加I/O操作次数。

MySQL字符串类型：存储，性能和最佳实践May 10, 2025 am 12:02 AM

mySqlStringTypesimpactStorageAndPerformanCeaseAsfollows：1）长度，始终使用theSamestoragespace，whatcanbefasterbutlessspace-felfficity.2）varCharisvariable varcharisvariable length，morespace-morespace-morespace-effficitybuteftife buteftife butfority butfority textifforlyslower.3）

了解MySQL字符串类型：VARCHAR，文本，char等May 10, 2025 am 12:02 AM

mySqlStringTypesIncludeVarChar，文本，char，enum和set.1）varCharisVersAtileForvariable-lengthStringStringSuptOptoPeptoPepecifientlimit.2）textisidealforlargetStortStorStoverStorextorewiteWithoutAdefinedLengthl.3）charlisfixed-Length

MySQL中的字符串数据类型是什么？May 10, 2025 am 12:01 AM

MySQLoffersvariousstringdatatypes:1)CHARforfixed-lengthstrings,2)VARCHARforvariable-lengthtext,3)BINARYandVARBINARYforbinarydata,4)BLOBandTEXTforlargedata,and5)ENUMandSETforcontrolledinput.Eachtypehasspecificusesandperformancecharacteristics,sochoose

如何向新的MySQL用户授予权限May 09, 2025 am 12:16 AM

TograntpermissionstonewMySQLusers,followthesesteps:1)AccessMySQLasauserwithsufficientprivileges,2)CreateanewuserwiththeCREATEUSERcommand,3)UsetheGRANTcommandtospecifypermissionslikeSELECT,INSERT,UPDATE,orALLPRIVILEGESonspecificdatabasesortables,and4)

如何在MySQL中添加用户：逐步指南May 09, 2025 am 12:14 AM

toadduserInmysqleffectection andsecrely，theTheSepsps：1）USEtheCreateuserStattoDaneWuser，指定thehostandastrongpassword.2）GrantNectalRevileSaryPrivilegesSustate，usiveleanttatement，AdheringTotheTeprinciplelastPrevilegege.3）

mysql：添加具有复杂权限的新用户May 09, 2025 am 12:09 AM

toaddanewuserwithcomplexpermissionsinmysql，loldtheSesteps：1）创建eTheEserWithCreateuser'newuser'newuser'@''localhost'Indedify'pa ssword';。2）GrantreadAccesstoalltablesin'mydatabase'withGrantSelectOnMyDatabase.to'newuser'@'localhost';。3）GrantWriteAccessto'

See all articles