private void m_bwUpdateMasterDataContact_DoWork(object sender, DoWorkEventArgs e) { this.Invoke(new MethodInvoker(delegate { BrightPlatformEntities _efDbModel = new BrightPlatformEntities(UserSession.EntityConnection); WaitDialog.Show("Processing import file..."); this.SetEditableControlsContact(false); string[] _sRowValues = null; DataTable _dtContacts = m_dtImportFileMatchedContacts.Copy(); /** * clean records to be updated first. get all the new values from all cells and re-update each cell values * with the new values from the import data as from the format (New Value/Old Value) * _dtAccounts will now have the cleaned records */ #region Code Logic for (int i = 0; i < _dtContacts.Rows.Count; i++) { foreach (DataColumn _dcItem in _dtContacts.Columns) { // get off inverted p on column values _dtContacts.Rows[i][_dcItem.ColumnName] = _dtContacts.Rows[i][_dcItem.ColumnName].ToString().Replace("¶", ""); // we bypass non needed columns //if (_dcItem.ColumnName.Equals("org_no") || _dcItem.ColumnName.Equals("action_type") || _dcItem.ColumnName.Equals("list_id") || _dcItem.ColumnName.Equals("account_idid")) if (_dcItem.ColumnName.Equals("action_type") || _dcItem.ColumnName.Equals("list_id") || _dcItem.ColumnName.Equals("id")) continue; // we bypass "No Changes" rows else if (_dtContacts.Rows[i]["action_type"].Equals("No Changes")) continue; // we bypass null/empty cell values else if (string.IsNullOrEmpty(_dtContacts.Rows[i][_dcItem.ColumnName].ToString())) continue; // we bypass rows with no new values // respectively, we dont update cell values that include separator "/" at index 0 else if (_dtContacts.Rows[i][_dcItem.ColumnName].ToString().IndexOf("[«]") < 1) continue; try { _sRowValues = null; _sRowValues = _dtContacts.Rows[i][_dcItem.ColumnName].ToString().Split(new string[] { "[«]" }, StringSplitOptions.None); if (_sRowValues.Length > 1) _dtContacts.Rows[i][_dcItem.ColumnName] = _sRowValues[0].ToString(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Bright Manager", MessageBoxButtons.OK, MessageBoxIcon.Information); this.SetEditableControlsContact(true); e.Cancel = true; return; } } } #endregion /** * gather to be updated account records. */ #region Code Logic int _ContactId = 0; contact _efeContact= new contact(); List<contact> _lstUpdatedContacts = new List<contact>(); foreach (DataRow _drRow in _dtContacts.Select("action_type = 'For Update'")) { _efeContact = null; _ContactId = Convert.ToInt32(_drRow["id"]); _efeContact = _efDbModel.contacts.FirstOrDefault(i => i.id == _ContactId); if (_dtContacts.Columns.Contains("first_name")) _efeContact.first_name = string.IsNullOrEmpty(_drRow["first_name"].ToString())? string.Empty : _drRow["first_name"].ToString(); if (_dtContacts.Columns.Contains("middle_name")) _efeContact.middle_name = string.IsNullOrEmpty(_drRow["middle_name"].ToString()) ? string.Empty : _drRow["middle_name"].ToString(); if (_dtContacts.Columns.Contains("last_name")) _efeContact.last_name = string.IsNullOrEmpty(_drRow["last_name"].ToString()) ? string.Empty : _drRow["last_name"].ToString(); if (_dtContacts.Columns.Contains("direct_phone")) _efeContact.direct_phone = string.IsNullOrEmpty(_drRow["direct_phone"].ToString()) ? string.Empty : _drRow["direct_phone"].ToString(); if (_dtContacts.Columns.Contains("mobile")) _efeContact.mobile = string.IsNullOrEmpty(_drRow["mobile"].ToString()) ? string.Empty : _drRow["mobile"].ToString(); if (_dtContacts.Columns.Contains("email")) _efeContact.email = string.IsNullOrEmpty(_drRow["email"].ToString()) ? string.Empty : _drRow["email"].ToString(); if (_dtContacts.Columns.Contains("title_id")) _efeContact.title_id = string.IsNullOrEmpty(_drRow["title_id"].ToString()) ? null : this.TryParseInt(_drRow["title_id"].ToString()); if (_dtContacts.Columns.Contains("title")) _efeContact.title = string.IsNullOrEmpty(_drRow["title"].ToString()) ? string.Empty : _drRow["title"].ToString(); if (_dtContacts.Columns.Contains("role_tag_ids")) _efeContact.role_tag_ids = string.IsNullOrEmpty(_drRow["role_tag_ids"].ToString()) ? null : _drRow["role_tag_ids"].ToString(); if (_dtContacts.Columns.Contains("address_1")) _efeContact.address_1 = string.IsNullOrEmpty(_drRow["address_1"].ToString()) ? string.Empty : _drRow["address_1"].ToString(); if (_dtContacts.Columns.Contains("address_2")) _efeContact.address_2 = string.IsNullOrEmpty(_drRow["address_2"].ToString()) ? string.Empty : _drRow["address_2"].ToString(); if (_dtContacts.Columns.Contains("city")) _efeContact.city = string.IsNullOrEmpty(_drRow["city"].ToString()) ? string.Empty : _drRow["city"].ToString(); if (_dtContacts.Columns.Contains("zipcode")) _efeContact.zipcode = string.IsNullOrEmpty(_drRow["zipcode"].ToString()) ? string.Empty : _drRow["zipcode"].ToString(); if (_dtContacts.Columns.Contains("country")) _efeContact.country = string.IsNullOrEmpty(_drRow["country"].ToString()) ? string.Empty : _drRow["country"].ToString(); if (_dtContacts.Columns.Contains("remarks")) _efeContact.remarks = string.IsNullOrEmpty(_drRow["remarks"].ToString()) ? string.Empty : _drRow["first_remarksname"].ToString(); if (_dtContacts.Columns.Contains("role")) _efeContact.role = string.IsNullOrEmpty(_drRow["role"].ToString()) ? string.Empty : _drRow["role"].ToString(); if (_dtContacts.Columns.Contains("active")) _efeContact.active = string.IsNullOrEmpty(_drRow["active"].ToString()) ? false : Convert.ToBoolean(this.TryParseByte(_drRow["active"].ToString())); _efeContact.modified_by = UserSession.CurrentUser.UserId; _efeContact.modified_date = DateTime.Now; _efeContact.last_modified_machine = UserSession.CurrentUser.ComputerName; _efeContact.last_modified_source = BrightVision.EventLog.Business.FacadeEventLog.Source_Bright_Manager_Master_Data_Import; _lstUpdatedContacts.Add(_efeContact); } #endregion /** * gather to be added account records. */ #region Code Logic DataTable _dtNewContacts = DataImportUtility.CreateContactTable(); foreach (DataRow _drRow in _dtContacts.Select("action_type = 'New Record'")) { DataRow _drNewContact = _dtContacts.NewRow(); foreach (DataColumn _dcContact in _dtContacts.Columns) { if (!_dtContacts.Columns.Contains(_dcContact.ColumnName)) continue; if (_dcContact.DataType.FullName.Equals("System.String")) _drNewContact[_dcContact.ColumnName] = _drRow[_dcContact.ColumnName].ToString(); else if (_dcContact.DataType.FullName.Equals("System.Decimal")) _drNewContact[_dcContact.ColumnName] = this.TryParseDecimal(_drRow[_dcContact.ColumnName].ToString()); else if (_dcContact.DataType.FullName.Equals("System.Int32")) _drNewContact[_dcContact.ColumnName] = this.TryParseInt(_drRow[_dcContact.ColumnName].ToString()); else if (_dcContact.DataType.FullName.Equals("System.Byte")) _drNewContact[_dcContact.ColumnName] = this.TryParseByte(_drRow[_dcContact.ColumnName].ToString()); } _drNewContact["created_by"] = UserSession.CurrentUser.UserId; _drNewContact["created_date"] = DateTime.Now; _drNewContact["modified_by"] = UserSession.CurrentUser.UserId; _drNewContact["modified_date"] = DateTime.Now; _drNewContact["last_modified_machine"] = UserSession.CurrentUser.ComputerName; _drNewContact["last_modified_source"] = BrightVision.EventLog.Business.FacadeEventLog.Source_Bright_Manager_Master_Data_Import; if (_drNewContact != null) _dtNewContacts.Rows.Add(_drNewContact); } #endregion /** * start the insert/update transaction. * updates will be first processed over new data. */ #region Code Logic SqlConnection _sqlConnection = new SqlConnection(UserSession.ProviderConnection); _sqlConnection.Open(); SqlTransaction _sqlTransaction = _sqlConnection.BeginTransaction(); using (TransactionScope _efDbTransaction = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions() { Timeout = TimeSpan.FromMinutes(120), IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted })) { try { /** * start updating records. * transaction will not be saved until _efDbModel.AcceptAllChanges() is called. */ if (_lstUpdatedContacts.Count > 0) { contact _item = null; int _id = 0; for (int x = 0; x < _lstUpdatedContacts.Count; x++) { _id = _lstUpdatedContacts[x].id; _item = _efDbModel.contacts.FirstOrDefault(i => i.id == _id); _efDbModel.contacts.ApplyCurrentValues(_lstUpdatedContacts[x]); } _efDbModel.SaveChanges(); } //ObjectContact.SaveContacts(_lstUpdatedContacts, _efDbModel); /** * process new records using bulk insert. * commit and accept all changes to the database. * to be updated records will be committed on _efDbModel.AcceptAllChanges() call. */ if (_dtNewContacts.Rows.Count > 0) DatabaseUtility.ExecuteBulkProcessing("vw_contacts", _dtNewContacts, _sqlConnection, _sqlTransaction); /** * commit only if has records to insert/update. */ if (_dtNewContacts.Rows.Count > 0) _sqlTransaction.Commit(); if (_lstUpdatedContacts.Count > 0) { _efDbModel.AcceptAllChanges(); _efDbTransaction.Complete(); } tbxImportFileContact.Text = ""; cboSheetNameContact.Properties.Items.Clear(); cboSheetNameContact.Text = ""; gcMatchedColumnContact.DataSource = null; gcImportFileDataContact.DataSource = null; lblRecordStatisticContact.Text = " Number of Records: 0"; WaitDialog.Close(); MessageBox.Show("Successfully saved items to contacts master data.", "Bright Manager", MessageBoxButtons.OK, MessageBoxIcon.Information); WaitDialog.Show("Initializing ..."); } catch (Exception Ex) { WaitDialog.Close(); MessageBox.Show(string.Format("Transaction rolled backed due to the ff:{0}{0}{1}", Environment.NewLine, Ex.Message), "Bright Manager", MessageBoxButtons.OK, MessageBoxIcon.Information); WaitDialog.Show("Initializing ..."); _efDbTransaction.Dispose(); _sqlTransaction.Rollback(); _efDbModel = null; _sqlConnection = null; this.SetEditableControlsContact(true); e.Cancel = true; return; } finally { _efDbTransaction.Dispose(); _efDbModel = null; _sqlConnection = null; } WaitDialog.Close(); } #endregion /** * delete the temporary excel file created during the matching */ if (File.Exists(m_sFileNameContact)) File.Delete(m_sFileNameContact); this.SetEditableControlsContact(true); btnUpdateToGridContact.Enabled = false; btnUpdateToMasterDataTableContact.Enabled = false; WaitDialog.Close(); })); e.Cancel = true; }