Click here to Skip to main content
15,867,308 members
Articles / Web Development / ASP.NET

An Alternative to Sync Data from SQL Server to MySQL

Rate me:
Please Sign up or sign in to vote.
4.68/5 (6 votes)
16 Aug 2012CPOL2 min read 49.3K   18   8
It employs a dynamic trigger, a CLR Stored Procedure, and a Web Service to sync data from a MS SQL Server to MySQL.

Introduction

My company has the need to synchronize data on a real time basis from MS SQL Server 2008 to a MySQL database. At first I thought that it could be done with table triggers after setting up a linked server to a MySQL table, and then it would not be a big problem.

So I downloaded and installed the MySQL ODBC Connector for Windows 7. I set up an ODBC data source and then a linked server, but I got this error message:

sync_sql_mysql/error.jpg

I googled and googled and could not fix this issue, even after I re-installed SQL Server (you would be much appreciated if you let me know how to fix it by sending an email to to_scottleo@yahoo.com).

I had to turn to a work-around.

The solution

My idea is to write a trigger to call a CLR Stored Procedure which calls a Web Service. The Web Service will perform the MySQL data manipulation.

Let’s start.

Step 1: Download and install MySQL Connector/.NET for Windows. Create a Class Library in Visual Studio 2008. Let’s name it MySqlDataManipulation.

C#
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;
        }
    }
}

Build it and copy the DLL file to the web bin directory for our Web Service to use it.

Step 2: Create an ASP.NET Web Service.

C#
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;
    }
}

Step 3: Use Visual Studio 2008 to build a CLR Stored Procedure assembly.

C#
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);
    }
}

Before compiling, set the project build output to generate the serialization assembly to “On”. After compiling, we’ll get two files: MySQLRealTimeSyncStoredProcedure.dll and MySQLRealTimeSyncStoredProcedure.XmlSerializers.dll.

Then in SQL Server Management Studio, register the new assemblies MySQLRealTimeSyncStoredProcedure, MySQLRealTimeSyncStoredProcedure.XmlSerializers against these two DLLs, respectively. Remember to set "Permission set" to "External access". It is also probably needed to run the following codes depending on the database settings.

SQL
EXEC sp_configure 'clr enabled', 1
reconfigure;
GO
EXEC ('ALTER DATABASE dbname SET TRUSTWORTHY ON')
GO

Step 4: Write a regular Stored Procedure.

SQL
CREATE PROCEDURE MySqlDataSync
     @sSqlStmt nvarchar(255)
AS EXTERNAL NAME MySQLRealTimeSyncStoredProcedure.StoredProcedures.SaveData;
GO

Step 5: Write a table trigger.

SQL
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

Here it is assumed that the MySQL table has the same structure and table fields as the SQL Server table. And a catch here about the trigger is that it can be used for any table without any other changes except for the table names.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionCREATE ASSEMBLY - Error Pin
ismailwissam5-Apr-17 2:29
ismailwissam5-Apr-17 2:29 
Questionusing MySQLRealTimeSync.com.xxxxx.www; Pin
Jenarthan6-Aug-15 17:30
Jenarthan6-Aug-15 17:30 
QuestionMySQLRealTimeSync Pin
Jenarthan6-Aug-15 17:28
Jenarthan6-Aug-15 17:28 
QuestionCODE SYNCHRONISATION Pin
seemajoshi23105-Oct-12 1:04
seemajoshi23105-Oct-12 1:04 
I have a database that is working offline .When i connect to the network i want the data from that table to synchronise with the data in the another table on the same database.
Please help me out.
thanks
seema
seemajoshi2310@gmail.com
QuestionBoth on different Server Pin
surajjoshi134-May-12 22:00
surajjoshi134-May-12 22:00 
BugDoesn't work for statements that update several rows Pin
stipus26-Jul-11 23:49
stipus26-Jul-11 23:49 
GeneralRe: Doesn't work for statements that update several rows Pin
scott_liu25-Aug-11 9:43
scott_liu25-Aug-11 9:43 
GeneralMy vote of 5 Pin
Rich Fantozzi5-Jul-11 8:31
Rich Fantozzi5-Jul-11 8:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.