在数据库管理中，经常会遇到需要将数据从一个数据库同步到另一个数据库的情况。本文将介绍一种方法，通过CLR存储过程和触发器实现SQL Server到MySQL的数据同步。
首先，需要在Windows 7上下载并安装MySQL ODBC Connector。然后设置一个ODBC数据源，并创建一个链接服务器。如果在设置过程中遇到错误，可以尝试重新安装SQL Server。如果问题依旧无法解决，可以联系作者获取帮助。
编写一个CLR存储过程，调用一个Web服务来执行MySQL数据操作。首先，下载并安装MySQL Connector/.NET for Windows。在Visual Studio 2008中创建一个类库项目，命名为MySqlDataManipulation。
using System;
using MySql.Data.MySqlClient;
namespace MySqlDataManipulation
{
public class MySqlData
{
private MySqlConnection _conn;
public MySqlData(string sConnStr)
{
_conn = new MySqlConnection(sConnStr);
}
public int ExecuteNonQuery(string sSqlStmt)
{
_conn.Open();
MySqlCommand cmd = new MySqlCommand(sSqlStmt, _conn);
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = sSqlStmt;
int returnCode = cmd.ExecuteNonQuery();
return returnCode;
}
}
}
编译并复制DLL文件到Web服务的bin目录下。
创建一个ASP.NET Web服务，用于同步MySQL数据。
using System;
using System.Web.Services;
using MySqlDataManipulation;
using System.Web.Configuration;
[WebService]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
public class WebSyncService : System.Web.Services.WebService {
public WebSyncService () {
}
[WebMethod]
public void SyncMySQLData(string sSqlStmt)
{
String sMySqlConnStr = WebConfigurationManager.ConnectionStrings["SyncMySQL"].ToString();
MySqlData d = new MySqlData(sMySqlConnStr);
d.ExecuteNonQuery(sSqlStmt);
return;
}
}
使用Visual Studio 2008构建CLR存储过程程序集。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using MySQLRealTimeSync.com.xxxxx.www;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SaveData(String sSqlStmt)
{
WebSyncService sync = new WebSyncService();
sync.SyncMySQLData(sSqlStmt);
}
}
编译后，会得到两个文件：MySQLRealTimeSyncStoredProcedure.dll和MySQLRealTimeSyncStoredProcedure.XmlSerializers.dll。然后在SQL ServerManagement Studio中注册这两个程序集。
编写一个常规存储过程，用于调用CLR存储过程。
CREATE PROCEDURE MySqlDataSync @sSqlStmt nvarchar(255) AS EXTERNAL NAME MySQLRealTimeSyncStoredProcedure.StoredProcedures.SaveData;
GO
USE MyDatabase
IF OBJECT_ID('MySchema.TR_SyncToMySQL', 'TR') IS NOT NULL DROP TRIGGER MySchema.TR_SyncToMySQL
GO
SET NOCOUNT ON
GO
CREATE TRIGGER MySchema.TR_SyncToMySQL ON MySchema.MyTable FOR INSERT, DELETE, UPDATE AS
BEGIN
DECLARE @InsertCount integer, @DeleteCount integer, @TableName varchar(255), @MySQLTableName varchar(255), @FieldName nvarchar(255), @IdentityColName nvarchar(255), @IdentityColVal nvarchar(255), @OldValue nvarchar(max), @NewValue nvarchar(max), @ColCount bigint, @ColTotal bigint, @SqlString varchar(max), @SqlStringTemp varchar(max), @HasModifiedCol bit
SET @MySQLTableName = 'MySQLTableName'
SET @InsertCount = (SELECT Count(*) FROM INSERTED)
SET @DeleteCount = (SELECT Count(*) FROM DELETED)
SELECT @TableName = OBJECT_NAME(PARENT_OBJ) FROM SYSOBJECTS WHERE id = @@PROCID
SELECT @ColTotal = COUNT(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
SET @ColCount = 0
SELECT @IdentityColName = name FROM syscolumns WHERE OBJECT_NAME(id) = @TableName AND COLUMNPROPERTY(id, name, 'IsIdentity') = 1
IF OBJECT_ID('tempdb..#myTemp') IS NOT NULL DROP TABLE #myTemp;
CREATE TABLE #myTemp (FieldValue nvarchar(max));
IF @InsertCount > @DeleteCount
BEGIN
DECLARE @ValueString nvarchar(max)
WHILE ((@ColCount) < @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name) FROM syscolumns WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName = @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myInserted1') IS NOT NULL DROP TABLE #myInserted1;
SELECT * INTO #myInserted1 FROM INSERTED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @FieldName + ' AS nvarchar(max)) FROM #myInserted1';
EXEC (@SqlStringTemp);
SET @NewValue = (SELECT FieldValue FROM #myTemp);
IF @NewValue IS NULL SET @NewValue = ''
IF @SqlString IS NULL SET @SqlString = 'INSERT ' + @MySQLTableName + ' (';
IF @ValueString IS NULL SET @ValueString = ') VALUES ('
SET @SqlString = @SqlString + @FieldName + ', '
SET @ValueString = @ValueString + ''''
+ @NewValue + '''
'', '
END
EXEC dbo.MySqlDataSync REPLACE(@SqlString + @ValueString + ')', ',', ), ''');
END
ELSE IF @InsertCount < @DeleteCount
BEGIN
WHILE ((@ColCount) <= @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name) FROM syscolumns WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName <> @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myDeleted1') IS NOT NULL DROP TABLE #myDeleted1;
SELECT * INTO #myDeleted1 FROM DELETED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @IdentityColName + ' AS nvarchar(max)) FROM #myDeleted1';
EXEC (@SqlStringTemp);
SET @IdentityColVal = (SELECT FieldValue FROM #myTemp);
END
EXEC dbo.MySqlDataSync 'DELETE FROM ' + @MySQLTableName + ' WHERE ' + @IdentityColName + ' = ''' + @IdentityColVal + '''';
END
ELSE IF @InsertCount = @DeleteCount
BEGIN
SET @HasModifiedCol = 0
WHILE ((@ColCount) < @ColTotal)
BEGIN
SET @ColCount = 1 + @ColCount
SELECT @FieldName = rtrim(name) FROM syscolumns WHERE OBJECT_NAME(id) = @TableName AND colid = @ColCount
IF @FieldName = @IdentityColName CONTINUE
IF OBJECT_ID('tempdb..#myInserted') IS NOT NULL DROP TABLE #myInserted;
SELECT * INTO #myInserted FROM INSERTED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @IdentityColName + ' AS nvarchar(max)) FROM #myInserted';
EXEC (@SqlStringTemp);
SET @IdentityColVal = (SELECT FieldValue FROM #myTemp);
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @FieldName + ' AS nvarchar(max)) FROM #myInserted';
EXEC (@SqlStringTemp);
SET @NewValue = (SELECT FieldValue FROM #myTemp);
IF @NewValue IS NULL SET @NewValue = ''
IF OBJECT_ID('tempdb..#myDeleted') IS NOT NULL DROP TABLE #myDeleted;
SELECT * INTO #myDeleted FROM DELETED;
DELETE FROM #myTemp;
SET @SqlStringTemp = 'INSERT INTO #myTemp (FieldValue) SELECT TOP 1 CAST(' + @FieldName + ' AS nvarchar(max)) FROM #myDeleted';
EXEC (@SqlStringTemp);
SET @OldValue = (SELECT FieldValue FROM #myTemp);
IF @OldValue IS NULL SET @OldValue = ''
IF @SqlString IS NULL SELECT @SqlString = 'UPDATE ' + @MySQLTableName + ' SET '
IF @NewValue <> @OldValue
BEGIN
SET @HasModifiedCol = 1
SET @SqlString = @SqlString + @FieldName + ' = '''
+ @NewValue + '''
'', '
END
END
-- remove the last comma
SELECT @SqlString = LTRIM(REVERSE(@SqlString))
SELECT @SqlString = REVERSE(SUBSTRING(@SqlString, 2, LEN(@SqlString)))
SELECT @SqlString = @SqlString + 'WHERE ' + @IdentityColName + ' = ''' + @IdentityColVal + ''''
IF @HasModifiedCol = 1
BEGIN
PRINT @SqlString
EXEC dbo.MySqlDataSync @SqlString
END
END
END
GO