private void ImportData()
{
ContactsEntities db = new ContactsEntities();
int cnt = 0;
var now = DateTime.Now;
var existingFile = new FileInfo(_filepathString);
using (var package = new ExcelPackage(existingFile))
{
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
ExcelWorksheet currentWorksheet = workBook.Worksheets.First();
SetControlPropertyValue(l_allcnt, "Text", currentWorksheet.Dimension.End.Row.ToString());
for (int i = 2; i <= currentWorksheet.Dimension.End.Row; i++)
{
int col = 0;
SetControlPropertyValue(L_per, "Text",
"%" + ((float) cnt*100/currentWorksheet.Dimension.End.Row).ToString("00.00"));
SetControlPropertyValue(L_cnt, "Text", cnt.ToString());
cnt++;
ContactPerson person = new ContactPerson();
List<ContactPhone> contactPhones = new List<ContactPhone>();
List<ContactEmail> contactEmails = new List<ContactEmail>();
List<ContactAddress> contactAddresses = new List<ContactAddress>();
foreach (
var rowCell in currentWorksheet.Cells[i, 1, i, currentWorksheet.Dimension.End.Column])
{
string value = rowCell.Text;
if (!string.IsNullOrEmpty(value) && col < _dataModels.Count)
{
switch (_dataModels[col])
{
case DataModelKey.FirstName:
person.FirstName = value;
break;
case DataModelKey.LastName:
person.LastName = value;
break;
case DataModelKey.PostName:
person.PostName = value;
break;
case DataModelKey.BirthDate:
long birthdate;
long.TryParse(value, out birthdate);
person.DateOfBirth = birthdate;
break;
case DataModelKey.MobilePhone:
contactPhones.Add(new ContactPhone() {PhoneType = 0, PhoneNumber = value});
break;
case DataModelKey.HomePhone:
contactPhones.Add(new ContactPhone() {PhoneType = 1, PhoneNumber = value});
break;
case DataModelKey.WorkPhone:
contactPhones.Add(new ContactPhone() {PhoneType = 2, PhoneNumber = value});
break;
case DataModelKey.PersonalEmail:
contactEmails.Add(new ContactEmail() {EmailType = 0, Email = value});
break;
case DataModelKey.WorkEmail:
contactEmails.Add(new ContactEmail() {EmailType = 1, Email = value});
break;
case DataModelKey.HomeAddress:
contactAddresses.Add(new ContactAddress() {AddressType = 0, Address = value});
break;
case DataModelKey.WorkAddress:
contactAddresses.Add(new ContactAddress() {AddressType = 1, Address = value});
break;
}
}
col++;
}
if (person.FirstName != "" || person.LastName != "")
{
//db.ContactPerson.Add(person);
//db.SaveChanges();
ContactPerson oldperson=null;
foreach (ContactPhone contactPhone in contactPhones)
{
var oldphone = db.ContactPhone.FirstOrDefault(p => p.PhoneNumber == contactPhone.PhoneNumber);
if (oldphone != null)
{
oldperson = oldphone.ContactPerson;
}
}
if (oldperson != null)
{
if (!string.IsNullOrEmpty(person.PostName))
oldperson.PostName =person.PostName;
if (!string.IsNullOrEmpty(person.FirstName))
oldperson.FirstName = person.FirstName;
if (!string.IsNullOrEmpty(person.LastName))
oldperson.PostName = person.LastName;
if (person.DateOfBirth!=null)
oldperson.DateOfBirth = person.DateOfBirth;
person = oldperson;
db.SaveChanges();
}
foreach (ContactPhone contactPhone in contactPhones)
{
if (person.ContactPhone.All(p => p.PhoneNumber != contactPhone.PhoneNumber))
person.ContactPhone.Add(contactPhone);
else
{
var fphone = person.ContactPhone.FirstOrDefault(
p => p.PhoneNumber == contactPhone.PhoneNumber);
if (fphone != null)
fphone.PhoneType = contactPhone.PhoneType;
}
//contactPhone.ContactPersonID = person.Id;
// db.ContactPhone.Add(contactPhone);
}
foreach (ContactAddress contactAddress in contactAddresses)
{
if (person.ContactAddress.All(p => p.Address != contactAddress.Address))
person.ContactAddress.Add(contactAddress);
else
{
var fphone = person.ContactAddress.FirstOrDefault(
p => p.Address == contactAddress.Address);
if (fphone != null)
fphone.AddressType = contactAddress.AddressType;
}
//contactAddress.ContactPersonID = person.Id;
//db.ContactAddress.Add(contactAddress);
}
foreach (ContactEmail contactEmail in contactEmails)
{
if (person.ContactEmail.All(p => p.Email != contactEmail.Email))
person.ContactEmail.Add(contactEmail);
else
{
var fphone = person.ContactEmail.FirstOrDefault(
p => p.Email == contactEmail.Email);
if (fphone != null)
fphone.EmailType = contactEmail.EmailType;
}
//contactEmail.ContactPersonID = person.Id;
//db.ContactEmail.Add(contactEmail);
}
foreach (ContactGroup contactGroup in _contactGroups)
{
if (person.ContactGroup.All(p => p.GroupId != contactGroup.GroupId))
person.ContactGroup.Add(contactGroup);
//contactGroup.ContactPersonID = person.Id;
//db.ContactGroup.Add(contactGroup);
}
if (oldperson == null)
{
db.ContactPerson.Add(person);
}
try
{
db.SaveChanges();
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
MessageBox.Show("Entity of type " + eve.Entry.Entity.GetType().Name +
" in state " + eve.Entry.State +
" has the following validation errors:");
foreach (var ve in eve.ValidationErrors)
{
MessageBox.Show("- Property: "+ve.PropertyName+", Error: "+ve.ErrorMessage);
}
}
throw;
}
}
else
{
break;
}
}
}
}
}
MessageBox.Show(cnt + " Added in " + DateTime.Now.Subtract(now).TotalSeconds + " Secs");
SetControlPropertyValue(btn_import, "Enabled", true);
}
private void btn_add_Click(object sender, EventArgs e)
{
ContactsEntities db = new ContactsEntities();
if (_contactid > 0)
{
var con = db.ContactPerson.FirstOrDefault(p => p.Id == _contactid);
if (con != null)
{
db.ContactPerson.Remove(con);
db.SaveChanges();
db.Database.ExecuteSqlCommand("DELETE FROM ContactPhone WHERE ContactPersonID=" + _contactid);
db.Database.ExecuteSqlCommand("DELETE FROM ContactAddress WHERE ContactPersonID=" + _contactid);
db.Database.ExecuteSqlCommand("DELETE FROM ContactEmail WHERE ContactPersonID=" + _contactid);
db.Database.ExecuteSqlCommand("DELETE FROM ContactGroup WHERE ContactPersonID=" + _contactid);
}
}
int b;
int.TryParse(tb_Birthdate.Text, out b);
ContactPerson per = new ContactPerson()
{
FirstName = tb_firstname.Text,
LastName = tb_LastName.Text,
PostName = tb_postname.Text,
DateOfBirth = b
};
db.ContactPerson.Add(per);
db.SaveChanges();
for (int i = 0; i < _callnum; i++)
{
if (panelCall.Controls.Find("tb_call" + i, false).Any())
{
var tb = (TextBox) (panelCall.Controls.Find("tb_call" + i, false))[0];
var cb = (ComboBox) (panelCall.Controls.Find("cb_Calltype" + i, false))[0];
if (!string.IsNullOrEmpty(tb.Text))
{
db.ContactPhone.Add(new ContactPhone()
{
ContactPersonID = per.Id,
PhoneNumber = tb.Text,
PhoneType = cb.SelectedIndex
});
}
}
}
for (int i = 0; i < _emailnum; i++)
{
if (panelEmail.Controls.Find("tb_email" + i, false).Any())
{
var tb = (TextBox) (panelEmail.Controls.Find("tb_email" + i, false))[0];
var cb = (ComboBox) (panelEmail.Controls.Find("cb_emailtype" + i, false))[0];
if (!string.IsNullOrEmpty(tb.Text))
{
db.ContactEmail.Add(new ContactEmail()
{
ContactPersonID = per.Id,
Email = tb.Text,
EmailType = cb.SelectedIndex
});
}
}
}
for (int i = 0; i < _addressnum; i++)
{
if (panelAddress.Controls.Find("tb_address" + i, false).Any())
{
var tb = (TextBox) (panelAddress.Controls.Find("tb_address" + i, false))[0];
var cb = (ComboBox) (panelAddress.Controls.Find("cb_addresstype" + i, false))[0];
if (!string.IsNullOrEmpty(tb.Text))
{
db.ContactAddress.Add(new ContactAddress()
{
ContactPersonID = per.Id,
Address = tb.Text,
AddressType = cb.SelectedIndex
});
}
}
}
foreach (var selectedItem in clb_Groups.SelectedItems)
{
var g = (ContactGroupDef) selectedItem;
db.ContactGroup.Add(new ContactGroup()
{
ContactPersonID = per.Id,
GroupId = g.Id
});
}
db.SaveChanges();
MessageBox.Show("تغییرات خواسته شده اعمال شد");
this.Close();
}
