C#同步SQL Server数据库中的数据--数据库同步工具[同步已有的有变化的数据]
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());
}
}
