2023年7月13日发(作者：)

C#同步SQLServer数据库中的数据–数据库同步⼯具[同步新数据]C#同步SQL Server数据库中的数据1. 先写个sql处理类：using System;using c;using ;using ent;using ;namespace PinkDatabaseSync{class DBUtility : IDisposable{private string Server;private string Database;private string Uid;private string Password;private string connectionStr;private SqlConnection mySqlConn;public void EnsureConnectionIsOpen(){if (mySqlConn == null){mySqlConn = new SqlConnection(tionStr);();}else if ( == ){();}}public DBUtility(string server, string database, string uid, string password){ = server;se = database; = uid;rd = password;tionStr = “Server=” + + “;Database=” + se + “;User Id=” + + “;Password=” + rd;}public int ExecuteNonQueryForMultipleScripts(string sqlStr){ConnectionIsOpen();SqlCommand cmd = Command();dType = ;dText = sqlStr;return eNonQuery();}public int ExecuteNonQuery(string sqlStr){ConnectionIsOpen();SqlCommand cmd = new SqlCommand(sqlStr, mySqlConn);dType = ;return eNonQuery();}public object ExecuteScalar(string sqlStr){ConnectionIsOpen();SqlCommand cmd = new SqlCommand(sqlStr, mySqlConn);dType = ;return eScalar();}public DataSet ExecuteDS(string sqlStr){DataSet ds = new DataSet();ConnectionIsOpen();SqlDataAdapter sda= new SqlDataAdapter(sqlStr,mySqlConn);(ds);return ds;}public void BulkCopyTo(string server, string database, string uid, string password, string tableName, string primaryKeyName){string connectionString = “Server=” + server + “;Database=” + database + “;User Id=” + uid + “;Password=” + password;// Create destination connectionSqlConnection destinationConnector = new SqlConnection(connectionString);SqlCommand cmd = new SqlCommand(“SELECT * FROM ” + tableName, destinationConnector);// Open source and destination ConnectionIsOpen();();SqlDataReader readerSource = eReader();bool isSourceContainsData = false;string whereClause = ” where “;while (()){isSourceContainsData = true;whereClause += ” ” + primaryKeyName + “!=” + readerSource[primaryKeyName].ToString() + ” and “;}whereClause = ( – ” and “.Length, ” and “.Length);();whereClause = isSourceContainsData ? whereClause : ;// Select data from Products tablecmd = new SqlCommand(“SELECT * FROM ” + tableName + whereClause, mySqlConn);// Execute readerSqlDataReader reader = eReader();// Create SqlBulkCopySqlBulkCopy bulkData = new SqlBulkCopy(destinationConnector);// Set destination table ationTableName = tableName;// Write oServer(reader);// Close ();();();}public void Dispose(){if (mySqlConn != null)();}}}2. 再写个数据库类型类：using System;using c;using ;namespace PinkDatabaseSync{public class SQLDBSystemType{public static Dictionary systemTypeDict{get{var systemTypeDict = new Dictionary();(“34”, “image”);(“35”, “text”);(“36”, “uniqueidentifier”);(“40”, “date”);(“41”, “time”);(“42”, “datetime2”);(“43”, “datetimeoffset”);(“48”, “tinyint”);(“52”, “smallint”);(“56”, “int”);(“58”, “smalldatetime”);(“59”, “real”);(“60”, “money”);(“61”, “datetime”);(“62”, “float”);(“98”, “sql_variant”);(“99”, “ntext”);(“104”, “bit”);(“106”, “decimal”);(“108”, “numeric”);(“122”, “smallmoney”);(“127”, “bigint”);(“240-128”, “hierarchyid”);(“240-129”, “geometry”);(“240-130”, “geography”);(“165”, “varbinary”);(“167”, “varchar”);(“173”, “binary”);(“175”, “char”);(“189”, “timestamp”);(“231”, “nvarchar”);(“239”, “nchar”);(“241”, “xml”);(“231-256”, “sysname”);return systemTypeDict;}}}}3. 写个同步数据库中的数据：public void BulkCopyTo(string server, string database, string uid, string password, string tableName, string primaryKeyName){string connectionString = “Server=” + server + “;Database=” + database + “;User Id=” + uid + “;Password=” + password;// Create destination connectionSqlConnection destinationConnector = new SqlConnection(connectionString);SqlCommand cmd = new SqlCommand(“SELECT * FROM ” + tableName, destinationConnector);// Open source and destination ConnectionIsOpen();();SqlDataReader readerSource = eReader();bool isSourceContainsData = false;string whereClause = ” where “;while (()){isSourceContainsData = true;whereClause += ” ” + primaryKeyName + “!=” + readerSource[primaryKeyName].ToString() + ” and “;}whereClause = ( – ” and “.Length, ” and “.Length);();whereClause = isSourceContainsData ? whereClause : ;// Select data from Products tablecmd = new SqlCommand(“SELECT * FROM ” + tableName + whereClause, mySqlConn);// Execute readerSqlDataReader reader = eReader();// Create SqlBulkCopySqlBulkCopy bulkData = new SqlBulkCopy(destinationConnector);// Set destination table ationTableName = tableName;// Write oServer(reader);// Close ();();();}

4. 最后执⾏同步函数：private void SyncDB_Click(object sender, EventArgs e){string server = “localhost”;string dbname = “pinkCRM”;string uid = “sa”;string password = “password”;string server2 = “server2”;string dbname2 = “pinkCRM2”;string uid2 = “sa”;string password2 = “password2”;try{ = “DB data is syncing!”;DBUtility db = new DBUtility(server, dbname, uid, password);DataSet ds = eDS(“SELECT FROM sysobjects sobjects WHERE = ‘U'”);DataRowCollection drc = [0].Rows;foreach (DataRow dr in drc){string tableName = dr[0].ToString(); = + e + ” syncing table:” + tableName + e;DataSet ds2 = eDS(“SELECT * FROM s WHERE object_id = OBJECT_ID(‘dbo.” + tableName + “‘)”);DataRowCollection drc2 = [0].Rows;string primaryKeyName = drc2[0][“name”].ToString();pyTo(server2, dbname2, uid2, password2, tableName, primaryKeyName); = +”Done sync data for table:”+ tableName+ e;}(“Done sync db data successfully!”);}catch (Exception exc){(ng());}}注： 这⾥只写了对已有数据的不再插⼊数据，可以再提⾼为如果有数据更新，可以进⾏更新，那么⼀个数据库同步⼯具就可以完成了！