SQL Server数据全同步[终结版]
版权所有，转载请注明出处，谢谢！
经过两天的同步编写和测试，出了第一个Release版本：
1. 本函数仅支持单向同步，即从一个主数据库想多个从数据库同步
2.主数据库的任何增删改都会同步到所有从数据库上
3. 最重要的一点：同步数据库的价值所在：当主数据库服务器不可用时，程序可以使用其他从数据库或者备用数据库，这对于未来公有云和私有云应用具有重大价值！
代码：
///
/// 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++;
}
}
}
/