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

Source: Internet
Author: User
Tags first string

SQL Server data full synchronization [Finalize]All rights reserved. Reprint please indicate the source. Thank you! after two days of simultaneous writing and testing. Out the first release version number:

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

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

3. The most important point: the value of synchronizing the database: When the primary database server is unavailable, the program can use the 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 the rest of the base class of your 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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.