C#同步SQL Server数据库中的数据--数据库同步工具[同步已有的有变化的数据]

1. C#同步SQL Server数据库Schema

2. C#同步SQL Server数据库中的数据--数据库同步工具[同步新数据]

3. 分析下自己写的SQL Server同步工具的性能和缺陷

接着写数据同步，这次可以把有变化的数据进行更新了：

1.SQL批量更新函数：

/// <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>
        public void BulkUpdateTo(string server, string database, string uid, string password, string tableName, List<string> columns, List<string> ignoreUpdateColumns)
        {
            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;
                int setScriptsIndex = 0;
                string primaryKeyValue = record.Key;
                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++;
                        }
                    }
                }
                //update source to dest
                if (setScriptsIndex > 0)
                {
                    cmd = new SqlCommand("Update " + tableName + " set " + setScripts + " where " + primaryKeyName + "='" + recordsSource[primaryKeyValue][primaryKeyName] + "'", destinationConnector);
                    cmd.ExecuteNonQuery();
                }
            }

            // Close objects
            destinationConnector.Close();
            mySqlConn.Close();
        }

2.调用批量更新函数：

public void syncClick3()
        {
            string server = "server-01";
            string dbname = "dbname1";
            string uid = "sa";
            string password = "password";
            string server2 = "server-02";
            string dbname2 = "dbname2";
            string uid2 = "sa";
            string password2 = "password2";
            try
            {
                LogView.Text = "DB data is syncing!";
                DBUtility db = new DBUtility(server, dbname, uid, password);
                DataSet ds = db.ExecuteDS("SELECT sobjects.name FROM sysobjects sobjects WHERE sobjects.xtype = 'U'");
                DataRowCollection drc = ds.Tables[0].Rows;
                DateTime start = DateTime.Now;
                foreach (DataRow dr in drc)
                {
                    string tableName = dr[0].ToString();
                    LogView.Text = LogView.Text + Environment.NewLine + " syncing table:" + tableName + Environment.NewLine;
                    DataSet ds2 = db.ExecuteDS("SELECT * FROM sys.columns WHERE object_id = OBJECT_ID('dbo." + tableName + "')");
                    DataRowCollection drc2 = ds2.Tables[0].Rows;
                    string primaryKeyName = drc2[0]["name"].ToString();

                    List<string> columns = new List<string>();
                    if (tableName == "Customers")
                    {
                        columns.Add("CustomerName");
                        columns.Add("CustomerId");
                        columns.Add("IsNewData");
                    }
                    else
                    {
                        foreach (DataRow dr2 in drc2)
                        {
                            columns.Add(dr2["name"].ToString());
                        }
                    }
                    List<string> ignoreUpdateColumns = new List<string>();
                    ignoreUpdateColumns.Add("CustomerId");
                    db.BulkUpdateTo(server2, dbname2, uid2, password2, tableName, columns, ignoreUpdateColumns);

                    LogView.Text = LogView.Text + "Done sync data for table:" + tableName + Environment.NewLine;
                }
                DateTime end = DateTime.Now;
                LogView.Text = LogView.Text + "cost total seconds:" + (end - start).TotalSeconds.ToString() + Environment.NewLine;
                MessageBox.Show("Done sync db data successfully!");
            }
            catch (Exception exc)
            {
                MessageBox.Show(exc.ToString());
            }
        }

至此，一个数据库同步工具的核心代码基本完成！