从 SQL Server 到MySQL同步数据的另一种方法

分享于 

10分钟阅读

数据库

  繁體

介绍

首先,我认为,在将链接服务器设置为MySQL表之后,可以通过table triggers来完成,那么这不会是一个大问题。

我下载安装了MySQL ODBC连接器,设置了ODBC数据源,然后链接服务器,但得到了这个错误消息:

sync_sql_mysql/error.jpg

解决方案

步骤1:下载并安装Windows版MySQL Connector/.NET。在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目录中,以便我们的Web服务使用它。

步骤2:创建ASP.NET Web服务。



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;


 }


}




步骤3:使用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);


 }


}




编译之前,将项目生成输出设置为将序列化程序集生成为"On"。编译后,得到两个文件:MySQLRealTimeSyncStoredProcedure.dll和MySQLRealTimeSyncStoredProcedure.XmlSerializers.dll。

然后,在SQL Server Management Studio中,分别对这两个DLL注册新的程序集MySQLRealTimeSyncStoredProcedure,MySQLRealTimeSyncStoredProcedure.XmlSerializers,记住将"权限设置"设置为"外部访问权限"。根据数据库设置,可能还需要运行以下代码。



EXEC sp_configure 'clr enabled', 1


reconfigure;


GO


EXEC ('ALTER DATABASE dbname SET TRUSTWORTHY ON')


GO




步骤4:编写常规存储过程。



CREATE PROCEDURE MySqlDataSync


 @sSqlStmt nvarchar(255)


AS EXTERNAL NAME MySQLRealTimeSyncStoredProcedure.StoredProcedures.SaveData;


GO




步骤5:编写表触发器。



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


 BEGIN


 DECLARE @ValueString nvarchar(max)


 WHILE ((SELECT @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 -- delete action


 BEGIN


 WHILE ((SELECT @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 -- update action


 BEGIN


 SET @HasModifiedCol = 0


 WHILE ((SELECT @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




这里假定MySQL表具有与SQL Server表相同的结构和表字段。


数据  Server  syn  ALT  Alter  
相关文章