C # synchronize data in the SQL Server database-Database Synchronization tool [synchronize new data],

C # synchronize data in the SQL Server database-Database Synchronization tool [synchronize new data],
C # synchronize data in the SQL Server database 1. Write an 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 = new 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 + "; Database =" + 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;
        }

        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 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 () + "and";
            }
            whereClause = whereClause.Remove (whereClause.Length-"and" .Length, "and" .Length);
            readerSource.Close ();

            whereClause = isSourceContainsData? whereClause: string.Empty;

            // Select data from 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 another database type class:
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 ("34", "image");
            systemTypeDict.Add ("35", "text");
            systemTypeDict.Add ("36", "uniqueidentifier");
            systemTypeDict.Add ("40", "date");
            systemTypeDict.Add ("41", "time");
            systemTypeDict.Add ("42", "datetime2");
            systemTypeDict.Add ("43", "datetimeoffset");
            systemTypeDict.Add ("48", "tinyint");
            systemTypeDict.Add ("52 "," smallint ");
            systemTypeDict.Add ("56", "int");
            systemTypeDict.Add ("58", "smalldatetime");
            systemTypeDict.Add ("59", "real");
            systemTypeDict.Add ("60", "money");
            systemTypeDict.Add ("61", "datetime");
            systemTypeDict.Add ("62", "float");
            systemTypeDict.Add ("98", "sql_variant");
            systemTypeDict.Add ("99", "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 a synchronous database data:
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 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 () + "and";
            }
            whereClause = whereClause.Remove (whereClause.Length-"and" .Length, "and" .Length);
            readerSource.Close ();

            whereClause = isSourceContainsData? whereClause: string.Empty;

            // Select data from 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 synchronization 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 + "Done sync data for table:" + tableName + Environment.NewLine;
                }
                MessageBox.Show ("Done sync db data successfully!");
            }
            catch (Exception exc)
            {
                MessageBox.Show (exc.ToString ());
            }
        }
Note: Only the data that is no longer inserted into the existing data is written here. It can be further improved to update if there is data update, then a database synchronization tool can be completed!


A simple program of bubble sorting method in C language
main ()
{
int i, j, temp;
int a [10];
for (i = 0; i <10; i ++)
scanf ("% d,", & a [i]);
for (j = 0; j <= 9; j ++)
{for (i = 0; i <10-j; i ++)
if (a [i]> a [i + 1])
{temp = a [i];
a [i] = a [i + 1];
a [i + 1] = temp;}
}
for (i = 1; i <11; i ++)
printf ("% 5d,", a [i]);
printf ("\ n");
}

--------------
Bubbling algorithm
Algorithm Analysis and Improvement of Bubble Sort
The basic idea of exchange sorting is to compare the keywords of the records to be sorted pair by pair, and when two records are found in reverse order, they will be exchanged until there are no records in reverse order.
The main sorting methods that apply the basic idea of exchange sorting are: bubble sorting and quick sorting.

Bubble Sort

1. Sorting method
The sorted record array R [1..n] is arranged vertically, and each record R is regarded as a bubble with a weight of R.key. According to the principle that light bubbles cannot be under heavy bubbles, scan the array R from bottom to top: when the light bubbles that violate this principle are scanned, they are made to "float" upward. This is repeated until the last two bubbles are all lighter and more important.
(1) Initial
R [1..n] is a disordered area.

(2) The first scan
Compare the weights of the two adjacent bubbles in order from the bottom of the disordered area upwards. If the lighter one is found to be lower and the heavier one to the top, the positions of the two are exchanged. That is, compare (R [n], R [n-1]), (R [n-1], R [n-2]), ..., (R [2], R [1]); for each pair Bubbles (R [j + 1], R [j]). If R [j + 1] .key <R [j] .key, exchange the contents of R [j + 1] and R [j].
When the first scan is completed, the "lightest" bubble floats to the top of the interval, that is, the record with the smallest keyword is placed on the highest position R [1].

(3) The second scan
Scan R [2..n]. When the scan is complete, the "second light" bubble floats to the position of R [2] ...
Finally, after n-1 scans, the ordered area R [1..n] can be obtained
note:
During the ith scan, R [1..i-1] and R [i..n] are the current ordered and unordered areas, respectively. The scan is still from the bottom of the disordered area up to the top of the area. When the scan is complete, the lightest bubble in the area floats to the top position R, and the result is that R [1..i] becomes a new ordered area.

2. Example of bubble sorting process
The process of bubble sorting for files with a keyword sequence of 49 38 65 97 76 13 27 49

3. Sorting algorithm
(1) Analysis
Because each sorting order adds a bubble to the ordered area, after n-1 sorting, there are n-1 bubbles in the ordered area, and the weight of the bubbles in the disordered area is always greater than or equal to the order The weight of the bubbles in the zone, so the entire bubble sorting process needs at most n-1 sorting.
If the exchange of bubble positions is not found in a certain sorting, it means that all bubbles in the disordered area to be sorted satisfy the principle that the light is the first and the heavy is the bottom. termination. To this end, in the algorithm given below, a boolean exchange is introduced, which is set to FALSE before each sorting starts. If an exchange occurs during the sorting process, it is set to TRUE. Check the exchange at the end of each sorting, if no exchange has occurred, the algorithm is terminated, and the next sorting is not performed.

(2) Specific algorithm
void BubbleSort (SeqList R)
{//R(l..n) is the file to be sorted, using bottom-up scanning to do bubble sorting on R
int i, j;
Boolean exchange; // Exchange flag
for (i = 1; i & ... remaining full text >>
 
A simple program of bubble sorting method in C language

main ()
{
int i, j, temp;
int a [10];
for (i = 0; i <10; i ++)
scanf ("% d,", & a [i]);
for (j = 0; j <= 9; j ++)
{for (i = 0; i <10-j; i ++)
if (a [i]> a [i + 1])
{temp = a [i];
a [i] = a [i + 1];
a [i + 1] = temp;}
}
for (i = 1; i <11; i ++)
printf ("% 5d,", a [i]);
printf ("\ n");
}

--------------
Bubbling algorithm
Algorithm Analysis and Improvement of Bubble Sort
The basic idea of exchange sorting is to compare the keywords of the records to be sorted pair by pair, and when two records are found in reverse order, they will be exchanged until there are no records in reverse order.
The main sorting methods that apply the basic idea of exchange sorting are: bubble sorting and quick sorting.

Bubble Sort

1. Sorting method
The sorted record array R [1..n] is arranged vertically, and each record R is regarded as a bubble with a weight of R.key. According to the principle that light bubbles cannot be under heavy bubbles, scan the array R from bottom to top: when the light bubbles that violate this principle are scanned, they are made to "float" upward. This is repeated until the last two bubbles are all lighter and more important.
(1) Initial
R [1..n] is a disordered area.

(2) The first scan
Compare the weights of the two adjacent bubbles in order from the bottom of the disordered area upwards. If the lighter one is found to be lower and the heavier one to the top, exchange the positions of the two bubbles. That is, compare (R [n], R [n-1]), (R [n-1], R [n-2]), ..., (R [2], R [1]); for each pair Bubbles (R [j + 1], R [j]). If R [j + 1] .key <R [j] .key, exchange the contents of R [j + 1] and R [j].
When the first scan is completed, the "lightest" bubble floats to the top of the interval, that is, the record with the smallest keyword is placed on the highest position R [1].

(3) The second scan
Scan R [2..n]. When the scan is complete, the "second light" bubble floats to the position of R [2] ...
Finally, after n-1 scans, the ordered area R [1..n] can be obtained
note:
During the ith scan, R [1..i-1] and R [i..n] are the current ordered and unordered areas, respectively. The scan is still from the bottom of the disordered area up to the top of the area. When the scan is complete, the lightest bubble in the area floats to the top position R, and the result is that R [1..i] becomes a new ordered area.

2. Example of bubble sorting process
The process of bubble sorting for files with a keyword sequence of 49 38 65 97 76 13 27 49

3. Sorting algorithm
(1) Analysis
Because each sorting order adds a bubble to the ordered area, after n-1 sorting, there are n-1 bubbles in the ordered area, and the weight of the bubbles in the disordered area is always greater than or equal to the order The weight of the bubbles in the area, so the entire bubble sorting process needs at most n-1 sorting.
If the exchange of bubble positions is not found in a certain sorting, it means that all bubbles in the disordered area to be sorted meet the principle that the light is above and the top is below. Therefore, the bubble sorting process can be performed after this sorting termination. To this end, in the algorithm given below, a boolean exchange is introduced, which is set to FALSE before each sorting starts. If an exchange occurs during the sorting process, it is set to TRUE. Check the exchange at the end of each sorting, if no exchange has occurred, the algorithm is terminated, and the next sorting is not performed.

(2) Specific algorithm
void BubbleSort (SeqList R)
{//R(l..n) is the file to be sorted, using bottom-up scanning to do bubble sorting on R
int i, j;
Boolean exchange; // Exchange sign
for (i = 1; i & ... remaining full text >>