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;
}