【SQLServer】SQLServer数据全同步及价值分析

MEI · 发表于 2014-10-28 11:14:44

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

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

3. 最重要的一点：同步数据库的价值所在：当主数据库服务器不可用时，程序可以使用其他从数据库或者备用数据库，这对于未来公有云和私有云应用具有重大价值！

代码：

/// <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();
}

复制代码

代码的基础类其他部分请看下列文章：

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

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

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

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

收藏本站

快速投稿

联系我们

广告服务

基石导航

峰会活动

基石数据

社区

【SQLServer】SQLServer数据全同步及价值分析