private static void AddAccessDoorRefDataToDb(DataRow row)
{
String acdCode = row.Field <String>("access_door");
if (String.IsNullOrEmpty(acdCode))
{
return;
}
AccessDoor acd = new AccessDoor()
{
Code = acdCode,
DescShort = acdCode,
DescLong = acdCode,
ActiveFlag = true,
CreatedDt = DateTime.Now,
CreatedBy = 0,
UpdatedDt = DateTime.Now,
UpdatedBy = 0
};
try
{
using (SdmDbContext dbc = new SdmDbContext())
{
// if this record already exists in db, dont save it (this is a reference table)
if (dbc.AccessDoors.Any(o => o.Code == acd.Code))
{
//match! todo: fix this if else
}
else
{
dbc.AccessDoors.Add(acd);
dbc.SaveChanges();
}
}
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Value: \"{1}\", Error: \"{2}\"",
ve.PropertyName,
eve.Entry.CurrentValues.GetValue <object>(ve.PropertyName),
ve.ErrorMessage);
}
}
throw;
}
}
private Entity SaveEntityDataToDb(DataColumnCollection columns, DataRow row)
{
try
{
using (SdmDbContext dbc = new SdmDbContext())
{
String rowEmpClassCode = row.Field <String>("empl_classification");
var empClassObject = dbc.EmployeeClassifications.Where(x => x.Code == rowEmpClassCode);
var filteredEmpClass = empClassObject.ToArray().First();
String rowGridLocValue = row.Field <String>("grid_location");
var gridLocObject = dbc.GridLocations.Where(x => x.GridLocationValue == rowGridLocValue);
var filteredGridLoc = gridLocObject.ToArray().First();
String rowAccessDoorCode = row.Field <String>("access_door");
var filteredAccessDoor = new AccessDoor();
if (!String.IsNullOrEmpty(rowAccessDoorCode))
{
var accessDoorObject = dbc.AccessDoors.Where(x => x.Code == rowAccessDoorCode);
filteredAccessDoor = accessDoorObject.ToArray().First();
}
String rowJobTitleCode = row.Field <String>("job_title");
var jobTitleObject = dbc.JobTitles.Where(x => x.Code == rowJobTitleCode);
var filteredJobTitle = jobTitleObject.ToArray().First();
String rowUnitValue = row.Field <String>("unit");
var unitObject = dbc.Units.Where(x => x.UnitValue == rowUnitValue);
var filteredUnit = unitObject.ToArray().First();
String deskPhoneCode = "DESKPHN";
var deskPhoneTypeObject = dbc.PhoneTypes.Where(x => x.Code == deskPhoneCode);
var filteredDeskPhoneType = deskPhoneTypeObject.ToArray().First();
String mobilePhoneCode = "MOBILEPHN";
var mobilePhoneTypeObject = dbc.PhoneTypes.Where(x => x.Code == mobilePhoneCode);
var filteredMobilePhoneType = mobilePhoneTypeObject.ToArray().First();
String completeDeskPhoneNumber = row.Field <String>("desk_phone");
String completeMobilePhoneNumber = row.Field <String>("mobile_phone");
String deskPhoneAreaCode = string.Empty;
String deskPhoneNumber = string.Empty;
String mobilePhoneNumber = string.Empty;
String mobilePhoneAreaCode = string.Empty;
if (completeDeskPhoneNumber.Length == 11)
{
deskPhoneAreaCode = completeDeskPhoneNumber.Substring(1, 3);
deskPhoneNumber = completeDeskPhoneNumber.Substring(4, 7);
}
if (completeMobilePhoneNumber.Length == 11)
{
mobilePhoneAreaCode = completeMobilePhoneNumber.Substring(1, 3);
mobilePhoneNumber = completeMobilePhoneNumber.Substring(4, 7);
}
String entityTypeCode = "INDIVIDUAL";
var entityTypeObject = dbc.EntityTypes.Where(x => x.Code == entityTypeCode);
var filteredEntityType = entityTypeObject.ToArray().First();
String emailTypeColumnName = "email";
var emailTypeObject = dbc.EmailTypes.Where(x => x.Code == emailTypeColumnName);
var filteredEmailType = emailTypeObject.ToArray().First();
String hrmsidCode = "HRMSID";
var hrmsidIdentifierTypeObject = dbc.IdentifierTypes.Where(x => x.Code == hrmsidCode);
var filteredHrmsidIdentifierType = hrmsidIdentifierTypeObject.ToArray().First();
String manageridCode = "MANAGERID";
var manageridIdentifierTypeObject = dbc.IdentifierTypes.Where(x => x.Code == manageridCode);
var filteredManageridIdentifierType = manageridIdentifierTypeObject.ToArray().First();
// Get code for employee subtype
String employeeEntitySubtypeCode = "EMPLOYEE";
var employeeEntitySubtypeObject = dbc.EntitySubtypes.Where(x => x.Code == employeeEntitySubtypeCode);
var filteredEmployeeEntitySubtype = employeeEntitySubtypeObject.ToArray().First();
Entity entity = new Entity()
{
EntityTypeKey = filteredEntityType.EntityTypeKey,
EntitySubtypeKey = filteredEmployeeEntitySubtype.EntitySubtypeKey,
EmployeeClassificationKey = filteredEmpClass.EmployeeClassificationKey,
GridLocationKey = filteredGridLoc.GridLocationKey,
JobTitleKey = filteredJobTitle.JobTitleKey,
UnitKey = filteredUnit.UnitKey,
EffectiveBeginDt = DateTime.Now,
EffectiveEndDt = null,
CreatedBy = 0,
CreatedDt = DateTime.Now,
UpdatedBy = 0,
UpdatedDt = DateTime.Now,
Names = new List <Name>()
{
new Name()
{
FirstName = row.Field <String>("first_name"),
LastName = row.Field <String>("last_name"),
CreatedBy = 0,
CreatedDt = DateTime.Now,
UpdatedBy = 0,
UpdatedDt = DateTime.Now
}
},
Phones = new List <Phone>()
{
new Phone()
{
PhoneTypeKey = filteredDeskPhoneType.PhoneTypeKey,
AreaCode = deskPhoneAreaCode,
PhoneNumber = deskPhoneNumber,
ForeignFlag = false,
CreatedBy = 0,
CreatedDt = DateTime.Now,
UpdatedBy = 0,
UpdatedDt = DateTime.Now
},
new Phone()
{
PhoneTypeKey = filteredMobilePhoneType.PhoneTypeKey,
AreaCode = mobilePhoneAreaCode,
PhoneNumber = mobilePhoneNumber,
ForeignFlag = false,
CreatedBy = 0,
CreatedDt = DateTime.Now,
UpdatedBy = 0,
UpdatedDt = DateTime.Now
}
},
Emails = new List <Email>()
{
new Email()
{
EmailTypeKey = filteredEmailType.EmailTypeKey,
EmailAddress = row.Field <String>("email"),
CreatedBy = 0,
CreatedDt = DateTime.Now,
UpdatedBy = 0,
UpdatedDt = DateTime.Now
}
},
EntityIdentifiers = new List <EntityIdentifier>()
{
new EntityIdentifier()
{
IdentifierTypeKey = filteredHrmsidIdentifierType.IdentifierTypeKey,
IdentifierValue = row.Field <String>("HRMS_ID"),
CreatedBy = 0,
CreatedDt = DateTime.Now,
UpdatedBy = 0,
UpdatedDt = DateTime.Now
},
new EntityIdentifier()
{
IdentifierTypeKey = filteredManageridIdentifierType.IdentifierTypeKey,
IdentifierValue = row.Field <String>("manager_ID"),
CreatedBy = 0,
CreatedDt = DateTime.Now,
UpdatedBy = 0,
UpdatedDt = DateTime.Now
}
}
};
if (!String.IsNullOrEmpty(rowAccessDoorCode))
{
entity.AccessDoor.AccessDoorKey = filteredAccessDoor.AccessDoorKey;
}
return(entity);
};
}
catch (DbEntityValidationException e)
{
foreach (var eve in e.EntityValidationErrors)
{
Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
eve.Entry.Entity.GetType().Name, eve.Entry.State);
foreach (var ve in eve.ValidationErrors)
{
Console.WriteLine("- Property: \"{0}\", Value: \"{1}\", Error: \"{2}\"",
ve.PropertyName,
eve.Entry.CurrentValues.GetValue <object>(ve.PropertyName),
ve.ErrorMessage);
}
}
throw;
}
}
