Transferring, cleaning, synchronizing data

Recently looked at the company's import, cleaning, synchronized data. I want to realize it myself

First use SqlBulkCopy to bulk import, and then use partition by to group the data to be deleted, and then delete the id>1 data. Synchronizing data is querying the source data and then bulk updating the target data.

I implemented the following with MVC, and the code was implemented as follows:

Front Code

@{Layout = null;}  
 Background Code 
 

Using System;
Using System.Collections.Generic;
Using System.Configuration;
Using System.Data;
Using System.Linq;
Using System.Web;
Using SYSTEM.WEB.MVC; Using data synchronization.

Models; Namespace data synchronization. Controllers {public class Homecontroller:controller {///<summary>///declaration: When using this program, pay attention to "the original Table "is consistent with the database table to synchronize" structure to perform successfully. 
        And the primary key for all tables is the same to succeed synchronously.
        </summary> public ActionResult Index () {return View (); ///<summary>///Transfer data///</summary> [HttpPost] public actionre
            Sult CopyData () {string result = ' OK ';
            #region Simply call a table =======//dbutility db = new dbutility (); try {//db.
            Bulkcopyto ("Users", "UserID");
            }//catch (Exception ex)//{//result = "no";
     #endregion #region automatically loops all tables ===== try       {String constr = "DB data is syncing!";
                Dbutility db = new dbutility (); A dataset can be likened to a database in memory, and a DataTable to a data table in memory. The dataset can store multiple datatable DataSet ds = db.
                Executeds ("Select Sobjects.name from sysobjects sobjects WHERE sobjects.xtype = ' U '");/Find all table names, dataset represents a data cache in the database DataRowCollection DRC = ds. Tables[0]. rows;//Fetch table Name collection foreach (DataRow dr in DRC) {string tablename = dr[0].t Ostring ()//get data table Constr = constr + Environment.NewLine + "syncing table:" + tablename + environment.
                    newline; DataSet DS2 = db. Executeds ("select * from sys.columns WHERE object_id = object_id (' dbo." + tablename + "'));/Find the collection of attributes in the table, the dataset represents a storage database The data cache in DataRowCollection Drc2 = ds2. Tables[0]. Rows;//Gets the property collection if (Drc2!= null) {string primarykeyname = DRC2[0][' Name ']. ToString ();//Gets the primary key name DB.

                        Bulkcopyto (tablename, primarykeyname);
                    Constr = constr + "Done sync data for table:" + tablename + Environment.NewLine;
                catch (Exception exc) {result = ' no ';
            Throw exc;
        #endregion return Content (result); ///<summary>///Sync data///</summary> [HttpPost] public Actionresul
            T SyncClick3 () {string result = ' OK ';
            #region simply call a table = = =//dbutility db = new dbutility ();
            try {//list<string> list1 = new list<string> (); List1.
            AddRange (new string[] {"UserID", "UserName", "Userage"});
            list<string> list2 = new list<string> (); List2.
            ADD ("UserID"); Db. Bulkupdateto ("Users", List1, List2);
            }catch (Exception ex)//{//result = "no";  #endregion #region automatically loops all tables = = try {string str = ' DB
                Data is syncing! ";
                Dbutility db = new dbutility (); Query database all table DataSet ds = db.
                Executeds ("Select Sobjects.name from sysobjects sobjects WHERE sobjects.xtype = ' U '"); DataRowCollection DRC = ds. Tables[0].
                rows;//Get table name collection DateTime start = DateTime.Now; foreach (DataRow Dr in DRC) {string tablename = Dr[0]. ToString ()//Get a table name str = str + environment.newline + "syncing table:" + tablename + ENVIRONMENT.NEWL
                    Ine DataSet DS2 = db.
                    Executeds ("select * from sys.columns WHERE object_id = object_id (' dbo." + tablename + ")")//Get table related property information DataRowCollection drc2 = ds2. TableS[0]. Rows;//Gets the property collection string primarykeyname = drc2[0]["Name".
                    ToString ();//Get primary Key name list<string> columns = new list<string> (); if (tablename = = "Customers")//if the table name is Customers, perform the following actions {columns.
                        ADD ("CustomerName"); Columns.
                        ADD ("CustomerId"); Columns.
                    ADD ("Isnewdata");
                        else//add attribute name to collection {foreach (DataRow dr2 in Drc2) {columns. ADD (dr2["name"). ToString ());//write All properties of this table into the list collection}} list<string> Igno
                    Reupdatecolumns = new list<string> (); Ignoreupdatecolumns.add ("ID");//The field db to ignore when adding updates.

                    Bulkupdateto (tablename, columns, ignoreupdatecolumns); str = str + "Done sync data fo"R table: "+ tablename + Environment.NewLine;"
                DateTime end = DateTime.Now; str = str + "Cost total seconds:" + (End-start).

            Totalseconds.tostring () + Environment.NewLine;
            catch (Exception ex) {result = ' no ';

        #endregion return Content (result); ///<summary>///Cleaning data///</summary> public ActionResult Selectdisti
            NCT () {string result = ' OK ';
            Dbutility d = new dbutility ();
            BOOL B=d.cleartdata ();
            if (!b) {result = "no";
        return Content (Result); }
    }
}
 
 Dbutility data Transfer cleaning synchronization help class 
 

public class Dbutility {private string Server;
        private string Database;
        private string Uid;
        private string Password;
        public string ConnectionStr;
        Private SqlConnection Mysqlconn;
        private string TargetServer;
        private string TargetDatabase;
        private string Targetuid;
        private string Targetpassword;
        private string Targetconnectionstr;
        Private SqlConnection Targetconn; public void Ensureconnectionisopen () {if (Mysqlconn = = null) {Mysqlconn
                = new SqlConnection (THIS.CONNECTIONSTR);
            Mysqlconn.open (); else if (mysqlconn.state = = System.Data.ConnectionState.Closed) {Mysqlconn.open
            (); } if (targetconn==null) {targetconn = new SqlConnection (THIS.TARGETCONNECTIONSTR)
                ;
            Targetconn.open ();
     }       else if (targetconn.state = = System.Data.ConnectionState.Closed) {targetconn.open (); {This is public dbutility ()}. Server = configurationmanager.appsettings["SourceServer"].
            ToString (); This. Database = configurationmanager.appsettings["SourceDatabase"].
            ToString (); This. Uid = configurationmanager.appsettings["Sourceuid"].
            ToString (); This. Password = configurationmanager.appsettings["SourcePassword"].
            ToString (); This.connectionstr = "server=" + this. Server + ";D atabase=" + this. Database + "; User id= "+ this. Uid + "; Password= "+ this.
            Password; This.targetserver =