public List <BusinessObjects.Order> GetOrdersByDate(DateTime dateFrom, DateTime dateThru)
{
using (var context = DataContextFactory.CreateContext())
{
var orders = context.Orders.Where(o => o.OrderDate >= dateFrom && o.OrderDate <= dateThru).ToList();
return(Mapper.Map <List <Order>, List <BusinessObjects.Order> >(orders));
}
}
public List <BusinessObjects.Order> GetOrdersByMember(int memberId)
{
using (var context = DataContextFactory.CreateContext())
{
var orders = context.Orders.Where(o => o.MemberId == memberId).ToList();
return(Mapper.Map <List <Order>, List <BusinessObjects.Order> >(orders));
}
}
public BusinessObjects.Order GetOrder(int orderId)
{
using (var context = DataContextFactory.CreateContext())
{
var order = context.Orders.SingleOrDefault(o => o.OrderId == orderId);
return(Mapper.Map <Order, BusinessObjects.Order>(order));
}
}
public BusinessObjects.Product GetProduct(int productId)
{
using (var context = DataContextFactory.CreateContext())
{
var product = context.Products.SingleOrDefault(p => p.ProductId == productId);
return(Mapper.Map <Product, BusinessObjects.Product>(product));
}
}
public List <BusinessObjects.Category> GetCategories()
{
using (var context = DataContextFactory.CreateContext())
{
var categories = context.Categories.ToList();
return(Mapper.Map <List <Category>, List <BusinessObjects.Category> >(categories));
}
}
public List <BusinessObjects.OrderDetail> GetOrderDetails(int orderId)
{
using (var context = DataContextFactory.CreateContext())
{
var orderDetails = context.OrderDetails.Where(d => d.OrderId == orderId).ToList();
return(Mapper.Map <List <OrderDetail>, List <BusinessObjects.OrderDetail> >(orderDetails));
}
}
public List <BusinessObjects.Product> GetProductsByCategory(int categoryId, string sortExpression)
{
using (var context = DataContextFactory.CreateContext())
{
var products = context.Products.Where(p => p.CategoryId == categoryId).OrderBy(sortExpression).ToList();
return(Mapper.Map <List <Product>, List <BusinessObjects.Product> >(products));
}
}
public List <BusinessObjects.Member> GetMembers(string sortExpression = "MemberId ASC")
{
using (var context = DataContextFactory.CreateContext())
{
var members = context.Members.OrderBy(sortExpression).ToList();
return(Mapper.Map <List <Member>, List <BusinessObjects.Member> >(members));
}
}
public BusinessObjects.Member GetMemberByEmail(string email)
{
using (var context = DataContextFactory.CreateContext())
{
var member = context.Members.SingleOrDefault(m => m.Email == email);
return(Mapper.Map <Member, BusinessObjects.Member>(member));
}
}
public BusinessObjects.Member GetMember(int memberId)
{
using (var context = DataContextFactory.CreateContext())
{
var member = context.Members.SingleOrDefault(m => m.MemberId == memberId);
return(Mapper.Map <Member, BusinessObjects.Member>(member));
}
}
public BusinessObjects.Member GetMemberByOrder(int orderId)
{
using (var context = DataContextFactory.CreateContext())
{
var member = context.Members.SelectMany(c => context.Orders
.Where(o => c.MemberId == o.MemberId && o.OrderId == orderId),
(c, o) => c).SingleOrDefault(c => true);
return(Mapper.Map <Member, BusinessObjects.Member>(member));
}
}
public void DeleteMember(BusinessObjects.Member member)
{
var entity = Mapper.Map <BusinessObjects.Member, Member>(member);
using (var context = DataContextFactory.CreateContext())
{
context.Members.Attach(entity, false);
context.Members.DeleteOnSubmit(entity);
context.SubmitChanges();
}
}
public BusinessObjects.Category GetCategoryByProduct(int productId)
{
using (var context = DataContextFactory.CreateContext())
{
var category = context.Categories.SelectMany(c => context.Products
.Where(p => c.CategoryId == p.CategoryId)
.Where(p => p.ProductId == productId),
(c, p) => c).SingleOrDefault(c => true);
return(Mapper.Map <Category, BusinessObjects.Category>(category));
}
}
public void InsertMember(BusinessObjects.Member member)
{
var entity = Mapper.Map <BusinessObjects.Member, Member>(member);
using (var context = DataContextFactory.CreateContext())
{
context.Members.InsertOnSubmit(entity);
context.SubmitChanges();
// update business object with new id
member.MemberId = entity.MemberId;
}
}
public void UpdateMember(BusinessObjects.Member member)
{
var entity = Mapper.Map <BusinessObjects.Member, Member>(member);
using (var context = DataContextFactory.CreateContext())
{
var original = context.Members.SingleOrDefault(m => m.MemberId == member.MemberId);
original.Email = member.Email;
original.CompanyName = member.CompanyName;
original.Country = member.Country;
original.City = member.City;
context.SubmitChanges();
}
}
public List <BusinessObjects.Product> SearchProducts(string productName, double priceFrom, double priceThru, string sortExpression)
{
using (var context = DataContextFactory.CreateContext())
{
var query = context.Products.AsQueryable();
if (!string.IsNullOrEmpty(productName))
{
query = query.Where(p => p.ProductName.Contains(productName));
}
if (priceFrom != -1 && priceThru != -1)
{
query = query.Where(p => p.UnitPrice >= (decimal)priceFrom && p.UnitPrice <= (decimal)priceThru);
}
var products = query.OrderBy(sortExpression, null).ToList();
return(Mapper.Map <List <Product>, List <BusinessObjects.Product> >(products));
}
}
public List <BusinessObjects.Member> GetMembersWithOrderStatistics(string sortExpression)
{
using (var context = DataContextFactory.CreateContext())
{
var customers = context.Members.Select(c =>
new BusinessObjects.Member
{
MemberId = c.MemberId,
Email = c.Email,
CompanyName = c.CompanyName,
City = c.City,
Country = c.Country,
NumOrders = context.Orders.Where(o => o.MemberId == c.MemberId).Count(),
LastOrderDate = context.Orders.Where(o => o.MemberId == c.MemberId).Max(o => o.OrderDate)
}).OrderBy(sortExpression);
// Exclude customers without orders
return(customers.Where(c => c.NumOrders > 0).ToList());
}
}