public void Delete(Customer deleteThis)
{
_dataEngine.InitialiseParameterList();
_dataEngine.AddParameter("@Id", deleteThis.Id.ToString());
_sqlToExecute = "DELETE FROM [dbo].[Customer] WHERE Id = " + _dataEngine.GetParametersForQuery();
if (!_dataEngine.ExecuteSql(_sqlToExecute))
throw new Exception("Customer - Delete failed");
}
public void CreateCustomer(ref Customer customer, ref Address address)
{
//Save the customer record
var insertedRowId = _da.Value.Customer.Insert(customer);
if (insertedRowId == 0)
{
throw new Exception("Failed to create customer record");
}
else
{
customer.Id = insertedRowId;
}
//Save the address record
if (address.Id == 0)
{
//Create a new address record
insertedRowId = _da.Value.Address.Insert(address);
if (insertedRowId == 0)
{
throw new Exception("Failed to create address record");
}
else
{
address.Id = insertedRowId;
}
}
//Link the customer and address records
var customerAddressLink = new LinkObjectMaster()
{
MasterLinkId = customer.Id,
MasterLinkType = LinkType.Customer,
ChildLinkId = address.Id,
ChildLinkType = LinkType.Address
};
//Save the link record
insertedRowId = _da.Value.Link.Insert(customerAddressLink);
if (insertedRowId == 0)
{
//Roll back the inserts as it's failed
//Delete the customer record
_da.Value.Customer.Delete(customer);
//Delete the address record
_da.Value.Address.Delete(address);
throw new Exception("Failed to create customer address link record, transaction rolled back");
}
}
public int Insert(Customer saveThis)
{
try
{
_db.Customers.InsertOnSubmit(saveThis);
_db.SubmitChanges();
return saveThis.Id;
}
catch (Exception e)
{
throw e;
}
}
public void Delete(Customer deleteThis)
{
try
{
if (deleteThis.Id > 0)
{
_db.Customers.DeleteOnSubmit(deleteThis);
_db.SubmitChanges(ConflictMode.FailOnFirstConflict);
}
}
catch (Exception e)
{
throw e;
}
}
public Address GetCustomerAddress(Customer customer)
{
var customerAddressLink =
_da.Value.Link.GetChildLinkObjectId
(
LinkType.Customer,
customer.Id,
LinkType.Address
);
var linkRecord = customerAddressLink.Single();
if (linkRecord.ChildLinkId == null)
throw new Exception("No address is linked to customer ID : " + customer.Id);
var address = _da.Value.Address.GetById(linkRecord.ChildLinkId.Value);
return address;
}
public void UpdateCustomer(ref Customer customer)
{
_da.Value.Customer.Update(customer);
}
public IEnumerable<Location> GetCustomerLocations(Customer customer)
{
var linkObjs =
_da.Value.Link.GetChildLinkObjectId
(
LinkType.Customer,
customer.Id,
LinkType.Location
).AsEnumerable();
var locationObjs = _da.Value.Location.GetAll().AsEnumerable();
//var filteredLocations =
// from location in _da.Location.GetAll().AsEnumerable()
// join link in linkObjs
// on location.Id equals link.ChildLinkId
// select location;
var filteredLocations =
locationObjs.Where(l => linkObjs.Any(x => x.ChildLinkId == l.Id));
return filteredLocations.AsEnumerable();
}
public int Insert(Customer saveThis)
{
_dataEngine.InitialiseParameterList();
_dataEngine.AddParameter("@CustomerName", saveThis.CustomerName);
_dataEngine.AddParameter("@Active", saveThis.Active == true ? "1" : "0");
_sqlToExecute = "INSERT INTO [dbo].[Customer] ";
_sqlToExecute += "([CustomerName] ";
_sqlToExecute += ",[Active]) ";
_sqlToExecute += "OUTPUT INSERTED.Id ";
_sqlToExecute += "VALUES ";
_sqlToExecute += "(";
_sqlToExecute += _dataEngine.GetParametersForQuery();
_sqlToExecute += ")";
int insertedRowId = 0;
if (!_dataEngine.ExecuteSql(_sqlToExecute, out insertedRowId))
throw new Exception("Customer - Save failed");
return insertedRowId;
}
/// <summary>
/// Creates the object from the data returned from the database
/// </summary>
/// <returns></returns>
private Customer CreateCustomerFromData()
{
var customer = new Customer
{
Id = int.Parse(_dataEngine.Dr["Id"].ToString()),
CustomerName = _dataEngine.Dr["CustomerName"].ToString(),
Active = _dataEngine.Dr["Active"].ToString() == "0" ? false : true
};
return customer;
}
public void Update(Customer saveThis)
{
_dataEngine.InitialiseParameterList();
_dataEngine.AddParameter("@CustomerName", saveThis.CustomerName);
_dataEngine.AddParameter("@Active", saveThis.Active == true ? "1" : "0");
_sqlToExecute = "UPDATE [dbo].[Customer] SET ";
_sqlToExecute += "[CustomerName] = @CustomerName ";
_sqlToExecute += ",[Active] = @Active ";
_sqlToExecute += "WHERE [Id] = " + saveThis.Id;
if (!_dataEngine.ExecuteSql(_sqlToExecute))
throw new Exception("Customer - Update failed");
}
public void Update(Customer updateThis)
{
try
{
_db.Customers.Attach(updateThis);
_db.Refresh(RefreshMode.KeepCurrentValues, updateThis);
_db.SubmitChanges();
}
catch (Exception e)
{
throw e;
}
}
