SQL Server数据全同步[终结版]
版权全部。转载请注明出处。谢谢！
经过两天的同步编写和測试。出了第一个Release版本号：
1. 本函数仅支持单向同步。即从一个主数据库想多个从数据库同步
2.主数据库的不论什么增删改都会同步到全部从数据库上
3. 最重要的一点：同步数据库的价值所在：当主数据库server不可用时，程序能够使用其它从数据库或者备用数据库，这对于未来公有云和私有云应用具有重大价值！
代码：
<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>
代码的基础类其它部分请看下列文章：