public async Task CreatePayHistory(EmployeePayHistory payHistory)
{
if (await IsValidEmployeeID(payHistory.BusinessEntityID))
{
if (await IsExistingPayHistory(payHistory) == false)
{
var history = new EmployeePayHistory {
};
history.Map(payHistory);
Create(history);
await Save();
}
else
{
var msg = "Error: This operation would result in a duplicate employee pay history record.";
RepoLogger.LogError(CLASSNAME + ".CreatePayHistory " + msg);
throw new AdventureWorksUniqueIndexException(msg);
}
}
else
{
var msg = $"Error: '{payHistory.BusinessEntityID}' is not a valid employee ID.";
RepoLogger.LogError(CLASSNAME + ".CreatePayHistory " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
}
public async Task DeleteAddress(AddressDomainObj addressDomainObj)
{
if (await DbContext.Address.FindAsync(addressDomainObj.AddressID) == null)
{
string msg = $"Error: Update failed; unable to locate an address in the database with ID '{addressDomainObj.AddressID}'.";
RepoLogger.LogError(CLASSNAME + ".DeleteAddress " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
var strategy = DbContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async() =>
{
using (var transaction = DbContext.Database.BeginTransaction())
{
var bea = await DbContext.BusinessEntityAddress
.FindAsync(addressDomainObj.ParentEntityID, addressDomainObj.AddressID, addressDomainObj.AddressTypeID);
DbContext.BusinessEntityAddress.Remove(bea);
await Save();
var address = await DbContext.Address.FindAsync(addressDomainObj.AddressID);
DbContext.Address.Remove(address);
await Save();
transaction.Commit();
}
});
}
public async Task UpdateEmployee(EmployeeDomainObj employeeDomainObj)
{
var person = await DbContext.Person
.Where(p => p.BusinessEntityID == employeeDomainObj.BusinessEntityID)
.Include(p => p.EmployeeObj)
.Include(p => p.EmailAddressObj)
.Include(p => p.PasswordObj)
.FirstOrDefaultAsync();
if (person == null)
{
string msg = $"Error: Update failed; unable to locate an employee in the database with ID '{employeeDomainObj.BusinessEntityID}'.";
RepoLogger.LogError(CLASSNAME + ".UpdateEmployee " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
person.Map(employeeDomainObj);
person.EmployeeObj.Map(employeeDomainObj);
person.EmailAddressObj.PersonEmailAddress = employeeDomainObj.EmailAddress;
person.PasswordObj.PasswordHash = employeeDomainObj.PasswordHash;
person.PasswordObj.PasswordSalt = employeeDomainObj.PasswordSalt;
DbContext.Person.Update(person);
await Save();
}
public async Task UpdateContact(ContactDomainObj contactDomainObj)
{
await DoDatabaseValidation(contactDomainObj);
var contact = await DbContext.Person
.Where(c => c.BusinessEntityID == contactDomainObj.BusinessEntityID)
.Include(c => c.EmailAddressObj)
.Include(c => c.PasswordObj)
.FirstOrDefaultAsync();
if (contact != null)
{
contact.Map(contactDomainObj);
contact.EmailAddressObj.PersonEmailAddress = contactDomainObj.EmailAddress;
contact.PasswordObj.PasswordHash = contactDomainObj.EmailPasswordHash;
contact.PasswordObj.PasswordSalt = contactDomainObj.EmailPasswordSalt;
DbContext.Person.Update(contact);
await Save();
}
else
{
string msg = $"Error: Update failed; unable to locate a contact in the database with ID '{contactDomainObj.BusinessEntityID}'.";
RepoLogger.LogError(CLASSNAME + ".UpdateContact " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
}
public async Task CreateDepartmentHistory(EmployeeDepartmentHistory departmentHistory)
{
if (await IsValidEmployeeID(departmentHistory.BusinessEntityID))
{
var existingRecord = await DbContext.EmployeeDepartmentHistory.Where(hist =>
hist.BusinessEntityID == departmentHistory.BusinessEntityID &&
hist.DepartmentID == departmentHistory.DepartmentID &&
hist.ShiftID == departmentHistory.ShiftID &&
hist.StartDate == departmentHistory.StartDate)
.AnyAsync();
if (!existingRecord)
{
var deptHistory = new EmployeeDepartmentHistory {
};
deptHistory.Map(departmentHistory);
Create(deptHistory);
await Save();
}
else
{
var msg = "Error: This operation would result in a duplicate employee department history record.";
RepoLogger.LogError(CLASSNAME + ".CreateDepartmentHistory " + msg);
throw new AdventureWorksUniqueIndexException(msg);
}
}
else
{
var msg = $"Error: '{departmentHistory.BusinessEntityID}' is not a valid employee ID.";
RepoLogger.LogError(CLASSNAME + ".CreateDepartmentHistory " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
}
public async Task UpdateDepartmentHistory(EmployeeDepartmentHistory departmentHistory)
{
var deptHistory = await DbContext.EmployeeDepartmentHistory.Where(hist =>
hist.BusinessEntityID == departmentHistory.BusinessEntityID &&
hist.DepartmentID == departmentHistory.DepartmentID &&
hist.ShiftID == departmentHistory.ShiftID &&
hist.StartDate == departmentHistory.StartDate)
.FirstOrDefaultAsync();
if (deptHistory != null)
{
// The first four fields are part of the primary key,
// therefore, only the EndDate field can be edited.
deptHistory.EndDate = departmentHistory.EndDate;
Update(deptHistory);
await Save();
}
else
{
string msg = $"Error: Update failed; no record found with employee ID '{departmentHistory.BusinessEntityID}', ";
msg += $"department ID '{departmentHistory.DepartmentID}', shift ID '{departmentHistory.ShiftID}' ";
msg += $"and start date '{departmentHistory.StartDate.ToString()}'.";
RepoLogger.LogError(CLASSNAME + ".UpdateDepartmentHistory " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
}
public async Task DeleteContact(ContactDomainObj contactDomainObj)
{
if (await DbContext.Person.Where(p => p.BusinessEntityID == contactDomainObj.BusinessEntityID).AnyAsync() == false)
{
string msg = $"Error: Delete failed; unable to locate a contact in the database with ID '{contactDomainObj.BusinessEntityID}'.";
RepoLogger.LogError(CLASSNAME + ".DeleteContact " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
var strategy = DbContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async() =>
{
using (var transaction = DbContext.Database.BeginTransaction())
{
var pword = DbContext.Password.Where(p => p.BusinessEntityID == contactDomainObj.BusinessEntityID).FirstOrDefault();
if (pword != null)
{
DbContext.Password.Remove(pword);
await Save();
}
var email = DbContext.EmailAddress.Where(e => e.BusinessEntityID == contactDomainObj.BusinessEntityID).FirstOrDefault();
if (email != null)
{
DbContext.EmailAddress.Remove(email);
await Save();
}
var phones = DbContext.PersonPhone.Where(p => p.BusinessEntityID == contactDomainObj.BusinessEntityID).ToList();
if (phones != null)
{
DbContext.PersonPhone.RemoveRange(phones);
await Save();
}
var bec = DbContext.BusinessEntityContact
.Find(contactDomainObj.ParentEntityID, contactDomainObj.BusinessEntityID, contactDomainObj.ContactTypeID);
if (bec != null)
{
DbContext.BusinessEntityContact.Remove(bec);
await Save();
}
var contact = await DbContext.Person.FindAsync(contactDomainObj.BusinessEntityID);
DbContext.Person.Remove(contact);
await Save();
var bizEntity = DbContext.BusinessEntity.Find(contactDomainObj.BusinessEntityID);
DbContext.BusinessEntity.Remove(bizEntity);
await Save();
transaction.Commit();
}
});
}
public async Task UpdateVendor(VendorDomainObj vendorDomainObj)
{
var vendor = await DbContext.Vendor.FindAsync(vendorDomainObj.BusinessEntityID);
if (vendor == null)
{
string msg = $"Error: Update failed; unable to locate a vendor in the database with ID '{vendorDomainObj.BusinessEntityID}'.";
RepoLogger.LogError(CLASSNAME + ".UpdateVendor " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
vendor.Map(vendorDomainObj);
DbContext.Vendor.Update(vendor);
await Save();
}
public async Task DeleteDepartment(Department department)
{
var dept = await DbContext.Department
.Where(dept => dept.DepartmentID == department.DepartmentID)
.FirstOrDefaultAsync();
if (dept == null)
{
string msg = $"Error: Delete failed; no record found with department ID '{department.DepartmentID}'.";
RepoLogger.LogError(CLASSNAME + ".DeleteDepartment " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
Delete(dept);
await Save();
}
public async Task DeleteVendor(VendorDomainObj vendorDomainObj)
{
// There is a tsql trigger that prevents deletion of vendors!
vendorDomainObj.IsActive = false;
var vendor = await DbContext.Vendor.FindAsync(vendorDomainObj.BusinessEntityID);
if (vendor == null)
{
string msg = $"Error: Failed to change vendor status to in-active. Unable to locate a vendor in the database with ID '{vendorDomainObj.BusinessEntityID}'.";
RepoLogger.LogError(CLASSNAME + ".DeleteVendor " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
vendor.Map(vendorDomainObj);
DbContext.Vendor.Update(vendor);
await Save();
}
public async Task DeletePayHistory(EmployeePayHistory payHistory)
{
var history = await DbContext.EmployeePayHistory.Where(hist =>
hist.BusinessEntityID == payHistory.BusinessEntityID &&
hist.RateChangeDate == payHistory.RateChangeDate)
.FirstOrDefaultAsync();
if (history == null)
{
string msg = $"Error: Delete failed; unable to locate a pay history record in the database with employee ID '{payHistory.BusinessEntityID}' and rate change date '{payHistory.RateChangeDate.ToString()}'.";
RepoLogger.LogError(CLASSNAME + ".DeletePayHistory " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
Delete(history);
await Save();
}
public async Task UpdateAddress(AddressDomainObj addressDomainObj)
{
if (await DbContext.Address.FindAsync(addressDomainObj.AddressID) == null)
{
string msg = $"Error: Update failed; unable to locate an address in the database with ID '{addressDomainObj.AddressID}'.";
RepoLogger.LogError(CLASSNAME + ".UpdateAddress " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
await DoDatabaseValidation(addressDomainObj);
var strategy = DbContext.Database.CreateExecutionStrategy();
await strategy.ExecuteAsync(async() =>
{
using (var transaction = DbContext.Database.BeginTransaction())
{
var address = await DbContext.Address
.Where(a => a.AddressID == addressDomainObj.AddressID)
.Include(a => a.BusinessEntityAddressObj)
.FirstOrDefaultAsync();
if (addressDomainObj.AddressTypeID != address.BusinessEntityAddressObj.AddressTypeID)
{
// AddressTypeID is part of the primary key; it can't be edited. Delete
// BusinessEntity record and insert new one with the updated AddressTypeID
DbContext.BusinessEntityAddress.Remove(address.BusinessEntityAddressObj);
DbContext.BusinessEntityAddress.Add(
new BusinessEntityAddress
{
BusinessEntityID = addressDomainObj.ParentEntityID,
AddressID = addressDomainObj.AddressID,
AddressTypeID = addressDomainObj.AddressTypeID
}
);
}
address.Map(addressDomainObj);
DbContext.Address.Update(address);
await Save();
transaction.Commit();
}
});
}
public async Task CreateDepartment(Department department)
{
if (await IsDuplicateDeptRecord(department))
{
var msg = $"Error: Create department failed; there is already a department named '{department.Name}'.";
RepoLogger.LogError(CLASSNAME + ".CreateDepartment " + msg);
throw new AdventureWorksUniqueIndexException(msg);
}
var dept = new Department {
};
dept.Map(department);
Create(dept);
await Save();
department.DepartmentID = dept.DepartmentID;
}
public async Task <ContactDomainObj> GetContactByIDWithPhones(int contactID)
{
if (await DbContext.ContactDomainObj.Where(p => p.BusinessEntityID == contactID).AnyAsync())
{
var contact = await DbContext.ContactDomainObj
.Where(contact => contact.BusinessEntityID == contactID)
.FirstOrDefaultAsync();
contact.Phones.AddRange(await DbContext.PersonPhone.Where(p => p.BusinessEntityID == contactID).ToListAsync());
return(contact);
}
else
{
var msg = "Error: Invalid ContactID detected.";
RepoLogger.LogError(CLASSNAME + ".GetContactByIDWithPhones " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
}
public async Task <EmployeePayHistory> GetPayHistoryByID(int employeeID, DateTime rateChangeDate)
{
if (await IsValidEmployeeID(employeeID))
{
return(await DbContext.EmployeePayHistory
.Where(hist =>
hist.BusinessEntityID == employeeID &&
hist.RateChangeDate == rateChangeDate)
.AsNoTracking()
.FirstOrDefaultAsync());
}
else
{
var msg = $"Error: '{employeeID}' is not a valid employee ID.";
RepoLogger.LogError(CLASSNAME + ".GetPayHistoryByID " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
}
public async Task DeleteEmployee(EmployeeDomainObj employeeDomainObj)
{
var person = await DbContext.Person
.Where(p => p.BusinessEntityID == employeeDomainObj.BusinessEntityID)
.Include(p => p.EmployeeObj)
.FirstOrDefaultAsync();
if (person == null)
{
string msg = $"Error: Update failed; unable to locate an employee in the database with ID '{employeeDomainObj.BusinessEntityID}'.";
RepoLogger.LogError(CLASSNAME + ".DeleteEmployee " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
person.EmployeeObj.IsActive = false;
DbContext.Person.Update(person);
await Save();
}
public async Task <PagedList <EmployeePayHistory> > GetPayHistories(int employeeID, PayHistoryParameters payHistoryParameters)
{
if (await IsValidEmployeeID(employeeID))
{
var pageList = await PagedList <EmployeePayHistory> .ToPagedList(
FindByCondition(hist => hist.BusinessEntityID == employeeID)
.OrderBy(ph => ph.RateChangeDate),
payHistoryParameters.PageNumber,
payHistoryParameters.PageSize);
return(pageList);
}
else
{
var msg = $"Error: '{employeeID}' is not a valid employee ID.";
RepoLogger.LogError(CLASSNAME + ".GetPayHistories " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
}
public async Task DeletePhone(PersonPhone telephone)
{
if (await IsExistingPhoneRecord(telephone))
{
var phone = await DbContext.PersonPhone
.Where(p => p.BusinessEntityID == telephone.BusinessEntityID &&
p.PhoneNumber == telephone.PhoneNumber &&
p.PhoneNumberTypeID == telephone.PhoneNumberTypeID)
.FirstOrDefaultAsync();
Delete(phone);
await Save();
}
else
{
var msg = $"The phone record with ID: {telephone.BusinessEntityID}, number: {telephone.PhoneNumber}, and type: {telephone.PhoneNumberTypeID} could not be located in the database.";
RepoLogger.LogError(msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
}
private async Task DoDatabaseValidation(ContactDomainObj contactDomainObj)
{
if (await IsValidContactTypeID(contactDomainObj.ContactTypeID) == false)
{
var msg = "Error: Invalid contact type detected.";
RepoLogger.LogError(CLASSNAME + ".DoDatabaseValidation " + msg);
throw new AdventureWorksInvalidContactTypeException(msg);
}
if (!IsValidParentEntityID(contactDomainObj.ParentEntityID, contactDomainObj.PersonType))
{
var msg = string.Empty;
if (contactDomainObj.PersonType == "VC")
{
msg = "Error: Unable to determine the vendor that this contact is to be assigned to.";
}
RepoLogger.LogError(CLASSNAME + ".DoDatabaseValidation " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
}
public async Task <PagedList <EmployeeDepartmentHistory> > GetDepartmentHistories(int employeeID, DepartmentHistoryParameters deptHistoryParameters)
{
if (await IsValidEmployeeID(employeeID))
{
var pagedList = await PagedList <EmployeeDepartmentHistory> .ToPagedList(
DbContext.EmployeeDepartmentHistory
.AsNoTracking()
.Where(hist => hist.BusinessEntityID == employeeID)
.OrderBy(hist => hist.StartDate)
.AsQueryable(),
deptHistoryParameters.PageNumber,
deptHistoryParameters.PageSize);
return(pagedList);
}
else
{
var msg = $"Error: '{employeeID}' is not a valid employee ID.";
RepoLogger.LogError(CLASSNAME + ".GetDepartmentHistories " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
}
public async Task DeleteDepartmentHistory(EmployeeDepartmentHistory departmentHistory)
{
var deptHistory = await DbContext.EmployeeDepartmentHistory.Where(hist =>
hist.BusinessEntityID == departmentHistory.BusinessEntityID &&
hist.DepartmentID == departmentHistory.DepartmentID &&
hist.ShiftID == departmentHistory.ShiftID &&
hist.StartDate == departmentHistory.StartDate)
.FirstOrDefaultAsync();
if (deptHistory == null)
{
string msg = $"Error: Delete failed; no record found with employee ID '{departmentHistory.BusinessEntityID}', ";
msg += $"department ID '{departmentHistory.DepartmentID}', shift ID '{departmentHistory.ShiftID}' ";
msg += $"and start date '{departmentHistory.StartDate.ToString()}'.";
RepoLogger.LogError(CLASSNAME + ".DeleteDepartmentHistory " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
Delete(deptHistory);
await Save();
}
public async Task <PagedList <ContactDomainObj> > GetContacts(int entityID, ContactParameters contactParameters)
{
if (DbContext.BusinessEntity.Where(v => v.BusinessEntityID == entityID).Any())
{
var pagedList = await PagedList <ContactDomainObj> .ToPagedList(
DbContext.ContactDomainObj
.Where(contact => contact.ParentEntityID == entityID)
.AsQueryable()
.OrderBy(p => p.LastName)
.ThenBy(p => p.FirstName)
.ThenBy(p => p.MiddleName),
contactParameters.PageNumber,
contactParameters.PageSize);
return(pagedList);
}
else
{
var msg = "Error: Invalid BusinessEntityID detected.";
RepoLogger.LogError(CLASSNAME + ".GetContacts " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
}
private async Task DoDatabaseValidation(AddressDomainObj addressDomainObj)
{
if (await IsValidAddressTypeID(addressDomainObj.AddressTypeID) == false)
{
var msg = "Error: Invalid address type detected.";
RepoLogger.LogError(CLASSNAME + ".DoDatabaseValidation " + msg);
throw new AdventureWorksInvalidAddressTypeException(msg);
}
if (await IsValidParentEntityID(addressDomainObj.ParentEntityID) == false)
{
var msg = "Error: Unable to determine the entity that this address belongs to.";
RepoLogger.LogError(CLASSNAME + ".DoDatabaseValidation " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
if (await IsValidStateProvinceID(addressDomainObj.StateProvinceID) == false)
{
var msg = "Error: Invalid state/province ID detected.";
RepoLogger.LogError(CLASSNAME + ".DoDatabaseValidation " + msg);
throw new AdventureWorksInvalidStateProvinceIdException(msg);
}
}
public async Task UpdateDepartment(Department department)
{
if (await IsDuplicateDeptRecord(department))
{
var msg = "Error: Update department failed; there is another department with this name.";
RepoLogger.LogError(CLASSNAME + ".UpdateDepartment " + msg);
throw new AdventureWorksUniqueIndexException(msg);
}
var dept = await DbContext.Department
.Where(dept => dept.DepartmentID == department.DepartmentID)
.FirstOrDefaultAsync();
if (dept == null)
{
string msg = $"Error: Update failed; no record found with department ID '{department.DepartmentID}'.";
RepoLogger.LogError(CLASSNAME + ".UpdateDepartment " + msg);
throw new AdventureWorksNullEntityObjectException(msg);
}
dept.Map(department);
Update(dept);
await Save();
}
private async Task DoDatabaseValidation(PersonPhone telephone, string operation)
{
if (await IsValidPersonID(telephone.BusinessEntityID) == false)
{
var msg = "Error: Unable to determine what person this phone number should be associated with.";
RepoLogger.LogError($"{CLASSNAME}.{operation} " + msg);
throw new AdventureWorksInvalidEntityIdException(msg);
}
if (await IsValidPhoneNumberTypeID(telephone.PhoneNumberTypeID) == false)
{
var msg = $"Error: The PhoneNumberTypeID '{telephone.PhoneNumberTypeID}' is not valid.";
RepoLogger.LogError($"{CLASSNAME}.{operation} " + msg);
throw new AdventureWorksInvalidPhoneTypeException(msg);
}
if (await IsExistingPhoneRecord(telephone))
{
var msg = "Error: This operation would result in a duplicate phone record.";
RepoLogger.LogError($"{CLASSNAME}.{operation} " + msg);
throw new AdventureWorksUniqueIndexException(msg);
}
}
