SQL Server data full synchronization and value analysis [final version]

SQL Server data full synchronization [Finalize]Copyright, reproduced Please specify the source, thank you! after two days of simultaneous writing and testing, the first release version was made:

1. This function only supports one-way synchronization, that is, from a primary database want to multiple from the database synchronization

2. Any additions or deletions to the primary database will be synchronized to all the databases

3. The most important point: the value of synchronizing a database: When the primary database server is unavailable, the program can use other from the database or the standby database, which is of great value for future public and private cloud applications!

Code:

<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" ></par  am>//<param name= "UID" ></param>///<param name= "password" ></param>// <param name= "TableName" ></param>//<param name= "columns" ></param>//<param N Ame= "Ignoreupdatecolumns" ></param>//<param name= "Ignoreinsertcolumns" ></param> publi c void Bulkupdateto (string server, String database, string uid, string password, string tableName, List<string> Colu MNS, List<string> Ignoreupdatecolumns, list<string> ignoreinsertcolumns) {string PrimaryKey            Name = Columns[0]; String connectionString = "server=" + Server + ";D atabase=" + 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 the 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 {I F (Setscriptsindex = = 0) {setscripts + = keyName + "=")                                + Recordssource[primarykeyvalue][keyname] + "'"; } else {setscripts + = ",                                "+ keyName +" = ' "+ recordssource[primarykeyvalue][keyname] +" ' ";                            } setscriptsindex++; }}}} else {F                         Oreach (string keyName in columns) {if (!ignoreinsertcolumns.contains (keyName))                    {if (Insertscriptsindex = = 0) {            Insertkeysscripts + = KeyName;                            Insertvaluesscripts + = "'" + recordssource[primarykeyvalue][keyname] + "'"; } else {insertkeysscripts + = "," + K                                Eyname;                            Insertvaluesscripts + = ", '" + recordssource[primarykeyvalue][keyname] + "'";                        } insertscriptsindex++;                }}}//update source to Dest if (Setscriptsindex > 0) {cmd = new SqlCommand ("Update" + TableName + "Set" + Setscripts + "where" + Primar                    Ykeyname + "= '" + 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>

See the following articles for other parts of the underlying class of code:

1. C # Synchronizing data in a SQL Server database--Database synchronization tool [synchronizing existing changed data]       2. analyze the performance and flaws of the SQL Server Synchronization tool you wrote      3.C # Synchronizing data in a SQL Server database--Database synchronization tool [synchronizing new data]      4.C # Synchronous SQL Server database schema

SQL Server data full synchronization and value analysis [final version]