SQL Server数据全同步[终结版]

版权所有，转载请注明出处，谢谢！

经过两天的同步编写和测试，出了第一个Release版本：

1. 本函数仅支持单向同步，即从一个主数据库想多个从数据库同步

2.主数据库的任何增删改都会同步到所有从数据库上

代码：

/// 

        /// Note: for columns, the first string must be primary key name!
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        /// 
        public void BulkUpdateTo(string server, string database, string uid, string password, string tableName, List columns, List ignoreUpdateColumns, List ignoreInsertColumns)
        {
            string primaryKeyName = columns[0];
            string connectionString = "Server=" + server + ";Database=" + database + ";User Id=" + uid + ";Password=" + password;
            // Create destination connection
            SqlConnection destinationConnector = new SqlConnection(connectionString);

            SqlCommand cmd = new SqlCommand("SELECT * FROM " + tableName, destinationConnector);
            // Open source and destination connections.
            this.EnsureConnectionIsOpen();
            destinationConnector.Open();

            Dictionary Index_PrimaryKeyValue = new Dictionary();

            SqlDataReader readerSource = cmd.ExecuteReader();
            Dictionary> recordsDest = new Dictionary>();
            int i = 0;
            while (readerSource.Read())
            {
                Index_PrimaryKeyValue.Add(i, readerSource[primaryKeyName].ToString());
                string recordIndex = Index_PrimaryKeyValue[i];
                recordsDest[recordIndex] = new Dictionary();
                foreach (string keyName in columns)
                {
                    recordsDest[recordIndex].Add(keyName, readerSource[keyName].ToString());
                }
                i++;
            }

            // Select data from Products table
            cmd = new SqlCommand("SELECT * FROM " + tableName, mySqlConn);
            // Execute reader
            SqlDataReader reader = cmd.ExecuteReader();
            Dictionary





> recordsSource = new Dictionary>();

            Dictionary Index_PrimaryKeyValue2 = new Dictionary();

            int j = 0;
            while (reader.Read())
            {
                Index_PrimaryKeyValue2.Add(j, reader[primaryKeyName].ToString());
                string recordIndex = Index_PrimaryKeyValue2[j];
                recordsSource[recordIndex] = new Dictionary();
                foreach (string keyName in columns)
                {
                    recordsSource[recordIndex].Add(keyName, reader[keyName].ToString());
                }
                j++;
            }
            reader.Close();
            readerSource.Close();

            foreach (var record in recordsSource)
            {
                string setScripts = string.Empty;
                string insertKeysScripts = string.Empty;
                string insertValuesScripts = string.Empty;
                int setScriptsIndex = 0;
                int insertScriptsIndex = 0;
                string primaryKeyValue = record.Key;
                if (recordsDest.ContainsKey(primaryKeyValue))
                {
                    foreach (string keyName in columns)
                    {
                        if (!ignoreUpdateColumns.Contains(keyName))
                        {
                            if (recordsDest[primaryKeyValue][keyName] == record.Value[keyName])
                            {
                                //do nothing
                            }
                            else
                            {
                                if (setScriptsIndex == 0)
                                {
                                    setScripts += keyName + "='" + recordsSource[primaryKeyValue][keyName] + "' ";
                                }
                                else
                                {
                                    setScripts += "," + keyName + "='" + recordsSource[primaryKeyValue][keyName] + "' ";
                                }
                                setScriptsIndex++;
                            }
                        }
                    }
                }
                else
                {
                    foreach (string keyName in columns)
                    {
                        if (!ignoreInsertColumns.Contains(keyName))
                        {
                            if (insertScriptsIndex == 0)
                            {
                                insertKeysScripts += keyName;
                                insertValuesScripts += "'" + recordsSource[primaryKeyValue][keyName] + "' ";
                            }
                            else
                            {
                                insertKeysScripts += "," + keyName;
                                insertValuesScripts += ",'" + recordsSource[primaryKeyValue][keyName] + "' ";
                            }
                            insertScriptsIndex++;
                        }
                    }
                }

                /