SQL Server資料全同步及價值分析[終結版]

標籤：des   style   blog   http   io   os   ar   使用   for   

SQL Server資料全同步[終結版]著作權，轉載請註明出處，謝謝！經過兩天的同步編寫和測試，出了第一個Release版本：

1. 本函數僅支援單向同步，即從一個主要資料庫想多個從資料庫同步

2.主要資料庫的任何增刪改都會同步到所有從資料庫上

3. 最重要的一點：同步資料庫的價值所在：當主要資料庫伺服器不可用時，程式可以使用其他從資料庫或者備用資料庫，這對於未來公用雲端和私人雲端應用具有重大價值！

代碼：

<span style="font-size:18px;">/// <summary>        /// Note: for columns, the first string must be primary key name!        /// </summary>        /// <param name="server"></param>        /// <param name="database"></param>        /// <param name="uid"></param>        /// <param name="password"></param>        /// <param name="tableName"></param>        /// <param name="columns"></param>        /// <param name="ignoreUpdateColumns"></param>        /// <param name="ignoreInsertColumns"></param>        public void BulkUpdateTo(string server, string database, string uid, string password, string tableName, List<string> columns, List<string> ignoreUpdateColumns, List<string> 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<int, string> Index_PrimaryKeyValue = new Dictionary<int, string>();            SqlDataReader readerSource = cmd.ExecuteReader();            Dictionary<string, Dictionary<string, string>> recordsDest = new Dictionary<string, Dictionary<string, string>>();            int i = 0;            while (readerSource.Read())            {                Index_PrimaryKeyValue.Add(i, readerSource[primaryKeyName].ToString());                string recordIndex = Index_PrimaryKeyValue[i];                recordsDest[recordIndex] = new Dictionary<string, string>();                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<string, Dictionary<string, string>> recordsSource = new Dictionary<string, Dictionary<string, string>>();            Dictionary<int, string> Index_PrimaryKeyValue2 = new Dictionary<int, string>();            int j = 0;            while (reader.Read())            {                Index_PrimaryKeyValue2.Add(j, reader[primaryKeyName].ToString());                string recordIndex = Index_PrimaryKeyValue2[j];                recordsSource[recordIndex] = new Dictionary<string, string>();                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++;                        }                    }                }                //update source to dest                if (setScriptsIndex > 0)                {                    cmd = new SqlCommand("Update " + tableName + " set " + setScripts + " where " + primaryKeyName + "='" + recordsSource[primaryKeyValue][primaryKeyName] + "'", destinationConnector);                    cmd.ExecuteNonQuery();                }                //insert source to dest                if (insertScriptsIndex > 0)                {                    cmd = new SqlCommand("insert into " + tableName + " (" + insertKeysScripts + ") values (" + insertValuesScripts + ")", destinationConnector);                    cmd.ExecuteNonQuery();                }            }            //after update and insert, the count still not match, means we delete some records in source db, then we also need to delete the records in destination db            foreach (var re in recordsDest)            {                //get the delete record primary key value                if (!recordsSource.ContainsKey(re.Key))                {                    cmd = new SqlCommand("delete from " + tableName + " where " + primaryKeyName + "='" + re.Value[primaryKeyName].ToString() + "'", destinationConnector);                    cmd.ExecuteNonQuery();                }            }            // Close objects            destinationConnector.Close();            mySqlConn.Close();        }</span>

 

代碼的基礎類其他部分請看下列文章：

1. C#同步SQL Server資料庫中的資料--資料庫同步工具[同步已有的有變化的資料]       2.分析下自己寫的SQL Server同步工具的效能和缺陷             3.C#同步SQL Server資料庫中的資料--資料庫同步工具[同步新資料]             4.C#同步SQL Server資料庫Schema

 

 

 

 

SQL Server資料全同步及價值分析[終結版]