1
\$\begingroup\$

I have code in place that helps me to keep track of our SiteData and make sure its always up to date. Whatever new records are added in the Site must be added to the Online Database.Records that have been modified and updated must be overwritten and replaced with records from the online Database. New records that have been added to the online Database must be added to the site database

The vendors table in both databases has the same structure

My question is how can I improve on this code

private void BtnSynchroniseSiteMaster_Click(object sender, EventArgs e)
    {
        //Connection string to the Online database
        myBridgeMaster_DatabaseConnectionString = @"Server= " + TxtBridgeMasterServer.Text + ";Database=" + TxtBridgeMasterDatabase.Text + ";Uid=" + TxtBridgeMasterUser.Text + ";Pwd=" + TxtBridgeMasterPassword.Text + ";";
        //Connection string to the Local database
        mySiteMaster_DatabaseConnectionString = @"Server= " + TxtSiteMasterServer.Text + ";Database=" + TxtSiteMasterDatabase.Text + ";Uid=" + TxtSiteMasterUser.Text + ";Pwd=" + TxtSiteMasterPassword.Text + ";";
        //The Online Database is the Master and the Local one is the slave

        //Lets get the SiteMaster Data 
        SqlConnection mySiteConnection = new SqlConnection(mySiteMaster_DatabaseConnectionString);
        SqlDataAdapter mySiteDataAdapter = new SqlDataAdapter("SELECT * FROM Vendors ", mySiteConnection);
        SqlCommandBuilder mySiteCommandBuilder = new SqlCommandBuilder(mySiteDataAdapter);
        DataTable mySiteDataTable = new DataTable();
        mySiteDataAdapter.Fill(mySiteDataTable);
        if (mySiteDataTable.Rows.Count > 0)
        {
            //Lets get the BridgeMaster Data 
            DataTable myBridgeDataTable = new System.Data.DataTable();
            SqlConnection myBridgeConnection = new SqlConnection(myBridgeMaster_DatabaseConnectionString);
            myBridgeConnection.Open();
            SqlDataAdapter myBridgeDataAdapter = new SqlDataAdapter();
            myBridgeDataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Vendors ", myBridgeConnection);
            SqlCommandBuilder myMasterCommandBuilder = new SqlCommandBuilder(myBridgeDataAdapter);
            myBridgeDataAdapter.Fill(myBridgeDataTable);
            //Whatever is in the SiteData Datatable but not in the BridgeData Datatable must be added to the datable and to the database
            var EntriesNotInBridgeMaster = mySiteDataTable.AsEnumerable().Select(r => r.Field<string>("VendorID")).Except(myBridgeDataTable.AsEnumerable().Select(r => r.Field<string>("VendorID")));

            if (EntriesNotInBridgeMaster.Count() > 0)
            {
                DataTable dt = (from row in mySiteDataTable.AsEnumerable() join id in EntriesNotInBridgeMaster on row.Field<string>("VendorID") equals id select row).CopyToDataTable();
                foreach (DataRow row in dt.Rows)
                {
                    myBridgeDataTable.Rows.Add(row.ItemArray);
                    myBridgeDataAdapter.UpdateCommand = myMasterCommandBuilder.GetUpdateCommand();
                    myBridgeDataAdapter.Update(myBridgeDataTable);
                }
            }

            //Whatever is in the BridgeData Datatable but not in the SiteData Datatable must be added to the datable and to the database
            var EntriesNotInSiteMaster = myBridgeDataTable.AsEnumerable().Select(r => r.Field<string>("VendorID")).Except(mySiteDataTable.AsEnumerable().Select(r => r.Field<string>("VendorID")));

            if (EntriesNotInSiteMaster.Count() > 0)
            {
                DataTable dt = (from row in myBridgeDataTable.AsEnumerable() join id in EntriesNotInSiteMaster on row.Field<string>("VendorID") equals id select row).CopyToDataTable();
                foreach (DataRow row in dt.Rows)
                {
                    mySiteDataTable.Rows.Add(row.ItemArray);
                    mySiteDataAdapter.UpdateCommand = mySiteCommandBuilder.GetUpdateCommand();
                    mySiteDataAdapter.Update(mySiteDataTable);
                }
            }
            /*Any rows in the SiteData where information in other columns  ( not the VendorID Column )
             * has been changed must be removed */
            var differences = mySiteDataTable.AsEnumerable().Except(myBridgeDataTable.AsEnumerable(), DataRowComparer.Default);
            DataTable mySlaveAddedRows = differences.Any() ? differences.CopyToDataTable() : new DataTable();

            foreach (DataRow rowSlaveDiff in mySlaveAddedRows.Rows)
            {
                for (int i = mySiteDataTable.Rows.Count - 1; i >= 0; i--)
                {
                    DataRow dr = mySiteDataTable.Rows[i];
                    if (Convert.ToString(dr["VendorID"]) == Convert.ToString(rowSlaveDiff["VendorID"]))
                        dr.Delete();
                }
                mySiteDataAdapter.UpdateCommand = mySiteCommandBuilder.GetUpdateCommand();
                mySiteDataAdapter.Update(mySiteDataTable);                
            }
            /*Whatever is in the SiteData Datatable but not in the BridgeData 
             * Datatable must be added to the datable and to the database - This takes care of the rows I deleted in the previous step*/
            var EntriesNotInSiteMaster2 = myBridgeDataTable.AsEnumerable().Select(r => r.Field<string>("VendorID")).Except(mySiteDataTable.AsEnumerable().Select(r => r.Field<string>("VendorID")));

            if (EntriesNotInSiteMaster2.Count() > 0)
            {
                DataTable dt = (from row in myBridgeDataTable.AsEnumerable() join id in EntriesNotInSiteMaster2 on row.Field<string>("VendorID") equals id select row).CopyToDataTable();
                foreach (DataRow row in dt.Rows)
                {
                    mySiteDataTable.Rows.Add(row.ItemArray);
                    mySiteDataAdapter.UpdateCommand = mySiteCommandBuilder.GetUpdateCommand();
                    mySiteDataAdapter.Update(mySiteDataTable);
                }
            }
        }
        MessageBox.Show("Completed");
    }
\$\endgroup\$
  • 1
    \$\begingroup\$ SqlConnection etc are IDisposable and thus should be used inside a using block. Look at the examples here: dotnetperls.com/sqlconnection \$\endgroup\$ – BCdotWEB May 23 at 13:12
  • \$\begingroup\$ EntriesNotInSiteMaster2 is an IEnumerable<T> that is being materialized twice; one for the .Count() and the other for the linq query. You should add something like .ToList() or .ToHashSet() to prevent that. \$\endgroup\$ – Brad M May 23 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.