C # Synchronizing data in a SQL Server database--Database synchronization tool [synchronizing new data]

C # synchronizes data in a SQL Server database 1. Write a SQL processing class first:

Using system;using system.collections.generic;using system.data;using system.data.sqlclient;using System.Text;        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 = NE                W SqlConnection (THIS.CONNECTIONSTR);            Mysqlconn.open ();            } else if (mysqlconn.state = = connectionstate.closed) {mysqlconn.open (); }} public Dbutility (string server, String database, string uid, string password) {this.            Server = server; This.            Database = database; This.            UID = UID; This.            Password = Password; This.connectionstr = "server=" + this. Server + ";D atabase=" + this. Database + "; User id= "+ this. Uid + "; Password= "+ this.        Password; } public int executenonqueryformultiplescripts (string sqlstr) {this.            Ensureconnectionisopen ();            SqlCommand cmd = Mysqlconn.createcommand ();            Cmd.commandtype = CommandType.Text;            Cmd.commandtext = Sqlstr; return CMD.        ExecuteNonQuery (); } public int ExecuteNonQuery (string sqlstr) {this.            Ensureconnectionisopen ();            SqlCommand cmd = new SqlCommand (sqlstr, mysqlconn);            Cmd.commandtype = CommandType.Text; return CMD.        ExecuteNonQuery (); public Object ExecuteScalar (string sqlstr) {this.            Ensureconnectionisopen ();            SqlCommand cmd = new SqlCommand (sqlstr, mysqlconn);            Cmd.commandtype = CommandType.Text; return CMD.        ExecuteScalar (); } Public DataSet Executeds (string sqlstr) {DataSet Ds = new DataSet (); This.            Ensureconnectionisopen ();            SqlDataAdapter sda= New SqlDataAdapter (Sqlstr,mysqlconn); Sda.            Fill (DS);        return DS; The public void Bulkcopyto (string server, String database, string uid, string password, string tableName, String prim Arykeyname) {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 (); SqlDataReader readersource = cmd.            ExecuteReader ();            BOOL Issourcecontainsdata = false;            String whereclause = "where"; while (READERSOURCE.READ ()) {issourcecontainsdata = true; Whereclause + = "+ primarykeyname +" = "+ Readersource[primarykeyname].            ToString () + "or"; } Whereclause = Whereclause.remove (Whereclause.length-"or". Length, "or".            Length);            Readersource.close (); Whereclause = Issourcecontainsdata? Whereclause:string.            Empty;             Select data from the Products table cmd = new SqlCommand ("SELECT * from" + TableName + whereclause, mysqlconn); Execute reader SqlDataReader reader = cmd.            ExecuteReader ();            Create SqlBulkCopy SqlBulkCopy bulkdata = new SqlBulkCopy (destinationconnector);            Set destination table name bulkdata.destinationtablename = TableName;            Write Data bulkdata.writetoserver (reader);            Close objects bulkdata.close ();            Destinationconnector.close (); Mysqlconn.cloSE ();        public void Dispose () {if (mysqlconn! = null) mysqlconn.close (); }    }}

2. Write a database type class again:

Using system;using system.collections.generic;using system.text;namespace pinkdatabasesync{public class            Sqldbsystemtype {public static dictionary<string, string> systemtypedict {get{            var systemtypedict = new dictionary<string, string> ();            Systemtypedict.add ("The", "image");            Systemtypedict.add ("n", "text");            Systemtypedict.add ("A", "uniqueidentifier");            Systemtypedict.add ("Max", "date");            Systemtypedict.add ("A", "time");            Systemtypedict.add ("A", "datetime2");            Systemtypedict.add ("A", "DateTimeOffset");            Systemtypedict.add ("The", "tinyint");            Systemtypedict.add ("smallint");            Systemtypedict.add ("n", "int");            Systemtypedict.add ("smalldatetime");            Systemtypedict.add ("The", "real");            Systemtypedict.add ("A", "money");            Systemtypedict.add ("A", "datetime"); Systemtypedict.aDD ("+", "float");            Systemtypedict.add ("98", "sql_variant");            Systemtypedict.add ("The", "ntext");            Systemtypedict.add ("104", "bit");            Systemtypedict.add ("106", "decimal");            Systemtypedict.add ("108", "Numeric");            Systemtypedict.add ("122", "smallmoney");            Systemtypedict.add ("127", "bigint");            Systemtypedict.add ("240-128", "HierarchyID");            Systemtypedict.add ("240-129", "geometry");            Systemtypedict.add ("240-130", "Geography");            Systemtypedict.add ("165", "varbinary");            Systemtypedict.add ("167", "varchar");            Systemtypedict.add ("173", "binary");            Systemtypedict.add ("175", "char");            Systemtypedict.add ("189", "timestamp");            Systemtypedict.add ("231", "nvarchar");            Systemtypedict.add ("239", "nchar");            Systemtypedict.add ("241", "xml");            Systemtypedict.add ("231-256", "sysname"); return systemtypedict;           }        }    }} 

3. Write the data in a synchronized database:

public void Bulkcopyto (string server, String database, string uid, string password, string tableName, String Primarykeynam e) {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 (); SqlDataReader readersource = cmd.            ExecuteReader ();            BOOL Issourcecontainsdata = false;            String whereclause = "where";                while (Readersource.read ()) {issourcecontainsdata = true; Whereclause + = "+ primarykeyname +" = "+ Readersource[primarykeyname].            ToString () + "or";        }    Whereclause = Whereclause.remove (Whereclause.length-"or". Length, "or".            Length);            Readersource.close (); Whereclause = Issourcecontainsdata? Whereclause:string.            Empty;             Select data from the Products table cmd = new SqlCommand ("SELECT * from" + TableName + whereclause, mysqlconn); Execute reader SqlDataReader reader = cmd.            ExecuteReader ();            Create SqlBulkCopy SqlBulkCopy bulkdata = new SqlBulkCopy (destinationconnector);            Set destination table name bulkdata.destinationtablename = TableName;            Write Data bulkdata.writetoserver (reader);            Close objects bulkdata.close ();            Destinationconnector.close ();        Mysqlconn.close (); }

4. Finally execute the sync function:

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 {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; 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 (); Db.                                     Bulkcopyto (Server2, Dbname2, Uid2, Password2, TableName, primarykeyname);                Logview.text = Logview.text + "Do sync data for table:" + tablename+ Environment.NewLine;            } MessageBox.Show ("Done Sync db data successfully!"); } catch (Exception exc) {MessageBox.Show (exc.            ToString ()); }        }

Note: Here only write to the existing data is no longer inserted data, can be improved for if there is data update, can be updated, then a database synchronization tool can be completed!

C # Synchronizing data in a SQL Server database--Database synchronization tool [synchronizing new data]