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
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
How to make Google Maps the default map in iPhone
Apr 17, 2024 pm 07:34 PM
The default map on the iPhone is Maps, Apple's proprietary geolocation provider. Although the map is getting better, it doesn't work well outside the United States. It has nothing to offer compared to Google Maps. In this article, we discuss the feasible steps to use Google Maps to become the default map on your iPhone. How to Make Google Maps the Default Map in iPhone Setting Google Maps as the default map app on your phone is easier than you think. Follow the steps below – Prerequisite steps – You must have Gmail installed on your phone. Step 1 – Open the AppStore. Step 2 – Search for “Gmail”. Step 3 – Click next to Gmail app
How to solve the problem that the object named already exists in the sqlserver database
Apr 05, 2024 pm 09:42 PM
For objects with the same name that already exist in the SQL Server database, the following steps need to be taken: Confirm the object type (table, view, stored procedure). IF NOT EXISTS can be used to skip creation if the object is empty. If the object has data, use a different name or modify the structure. Use DROP to delete existing objects (use caution, backup recommended). Check for schema changes to make sure there are no references to deleted or renamed objects.
How to import mdf file into sqlserver
Apr 08, 2024 am 11:41 AM
The import steps are as follows: Copy the MDF file to SQL Server's data directory (usually C:\Program Files\Microsoft SQL Server\MSSQL\DATA). In SQL Server Management Studio (SSMS), open the database and select Attach. Click the Add button and select the MDF file. Confirm the database name and click the OK button.
What to do if the sqlserver service cannot be started
Apr 05, 2024 pm 10:00 PM
When the SQL Server service fails to start, here are some steps to resolve: Check the error log to determine the root cause. Make sure the service account has permission to start the service. Check whether dependency services are running. Disable antivirus software. Repair SQL Server installation. If the repair does not work, reinstall SQL Server.
How to check sqlserver port number
Apr 05, 2024 pm 09:57 PM
To view the SQL Server port number: Open SSMS and connect to the server. Find the server name in Object Explorer, right-click it and select Properties. In the Connection tab, view the TCP Port field.
How to recover accidentally deleted database in sqlserver
Apr 05, 2024 pm 10:39 PM
If you accidentally delete a SQL Server database, you can take the following steps to recover: stop database activity; back up log files; check database logs; recovery options: restore from backup; restore from transaction log; use DBCC CHECKDB; use third-party tools. Please back up your database regularly and enable transaction logging to prevent data loss.
The vitality of super intelligence awakens! But with the arrival of self-updating AI, mothers no longer have to worry about data bottlenecks
Apr 29, 2024 pm 06:55 PM
I cry to death. The world is madly building big models. The data on the Internet is not enough. It is not enough at all. The training model looks like "The Hunger Games", and AI researchers around the world are worrying about how to feed these data voracious eaters. This problem is particularly prominent in multi-modal tasks. At a time when nothing could be done, a start-up team from the Department of Renmin University of China used its own new model to become the first in China to make "model-generated data feed itself" a reality. Moreover, it is a two-pronged approach on the understanding side and the generation side. Both sides can generate high-quality, multi-modal new data and provide data feedback to the model itself. What is a model? Awaker 1.0, a large multi-modal model that just appeared on the Zhongguancun Forum. Who is the team? Sophon engine. Founded by Gao Yizhao, a doctoral student at Renmin University’s Hillhouse School of Artificial Intelligence.
Clock app missing in iPhone: How to fix it
May 03, 2024 pm 09:19 PM
Is the clock app missing from your phone? The date and time will still appear on your iPhone's status bar. However, without the Clock app, you won’t be able to use world clock, stopwatch, alarm clock, and many other features. Therefore, fixing missing clock app should be at the top of your to-do list. These solutions can help you resolve this issue. Fix 1 – Place the Clock App If you mistakenly removed the Clock app from your home screen, you can put the Clock app back in its place. Step 1 – Unlock your iPhone and start swiping to the left until you reach the App Library page. Step 2 – Next, search for “clock” in the search box. Step 3 – When you see “Clock” below in the search results, press and hold it and
