public static ProductAPIData AddProduct(ProductAPIData nProduct, bool doCommit = true)
{
if (nProduct.ProductName.Length > 40)
{
throw new FormatException("ProductName must be less than or equal to 40 characters.");
}
using (var db = new NorthwindDbContext())
{
Product product = db.Products.Add(new Product
{
ProductName = nProduct.ProductName,
SupplierID = nProduct.SupplierID,
Discontinued = nProduct.Discontinued
});
if (doCommit)
{
db.SaveChanges();
}
return new ProductAPIData
{
ProductID = product.ProductID,
ProductName = product.ProductName,
SupplierID = product.SupplierID
};
}
}
public static void DeleteProductsOrderDetails(ICollection<Product> products, NorthwindDbContext db, bool doCommit = true)
{
IEnumerable<Order_Detail> details = products.SelectMany(x => x.Order_Details);
if (details != null || details.Count() > 0)
{
db.Order_Details.RemoveRange(details);
}
if (doCommit)
{
db.SaveChanges();
}
}
// get all products in the Northwind db
public static IEnumerable<ProductAPIData> GetAll()
{
using (var db = new NorthwindDbContext())
{
return db.Products
.Select(x => new ProductAPIData
{
ProductID = x.ProductID,
ProductName = x.ProductName,
SupplierID = x.SupplierID,
UnitPrice = x.SupplierID,
Discontinued = x.Discontinued
})
.ToList();
}
}
public static SupplierAPIData AddSupplier(SupplierAPIData nSupplier, bool doCommit = true)
{
if (nSupplier.CompanyName.Length > 40)
{
throw new FormatException("CompanyName must be less than or equal to 40 characters.");
}
using (var db = new NorthwindDbContext())
{
Supplier supplier = db.Suppliers.Add(new Supplier
{
CompanyName = nSupplier.CompanyName
});
if (doCommit)
{
db.SaveChanges();
}
return new SupplierAPIData(supplier);
}
}
// get a single product
public static ProductAPIData GetProduct(int productID)
{
using (var db = new NorthwindDbContext())
{
return db.Products
.Where(x => x.ProductID == productID)
.Select(x => new ProductAPIData
{
ProductID = x.ProductID,
ProductName = x.ProductName,
SupplierID = x.SupplierID,
UnitPrice = x.UnitPrice,
Discontinued = x.Discontinued
})
.FirstOrDefault();
}
}
public static void DeleteSupplier(int supplierID, bool doCommit = true)
{
using (var db = new NorthwindDbContext())
{
Supplier supplier = db.Suppliers.Find(supplierID);
if (supplier == null)
{
throw new KeyNotFoundException();
}
// need to delete the supplier's products before deleting the supplier.
DeleteSupplierProducts(supplier, db, false);
db.Suppliers.Remove(supplier);
if (doCommit)
{
db.SaveChanges();
}
}
}
public static SupplierAPIData UpdateSupplier(int supplierID, SupplierAPIData nSupplier, bool doCommit = true)
{
if (nSupplier.CompanyName.Length > 40)
{
throw new FormatException("CompanyName must be less than or equal to 40 characters.");
}
using (var db = new NorthwindDbContext())
{
Supplier supplier = db.Suppliers.Find(supplierID);
// only update the company name
supplier.CompanyName = nSupplier.CompanyName;
if (doCommit)
{
db.SaveChanges();
}
return new SupplierAPIData(supplier);
}
}
// get a supplier's products
public static IEnumerable<ProductAPIData> GetSupplierProducts(int supplierID)
{
using (var db = new NorthwindDbContext())
{
Supplier supplier = db.Suppliers
.Where(x => x.SupplierID == supplierID)
.FirstOrDefault();
if (supplier != null)
{
// found supplier. return his products
return supplier.Products
.Select(x => new ProductAPIData
{
ProductID = x.ProductID,
ProductName = x.ProductName,
SupplierID = x.SupplierID,
UnitPrice = x.UnitPrice,
Discontinued = x.Discontinued
})
.ToList();
}
}
return null;
}
public static IEnumerable<SupplierMonthlySalesAPIData> GetSalesByMonth()
{
using (var db = new NorthwindDbContext())
{
var query = @"select s.SupplierID,s.CompanyName,s.CompanyName,month(o.OrderDate) OrderMonth, year(o.OrderDate) OrderYear, isnull(Sum(CONVERT(money,(od.UnitPrice*od.Quantity*(1-od.Discount)/100))*100),0) MTDOrderDollars
FROM Suppliers s inner join Products p on s.SupplierID = p.SupplierID
inner join ""Order Details"" od on p.ProductID = od.ProductID
inner join Orders o on od.OrderID = o.OrderID
Group By s.SupplierID,s.CompanyName, month(o.OrderDate),year(o.OrderDate)
order by OrderYear,OrderMonth";
var monthlySales = db.Database.SqlQuery<SupplierMonthlySalesAPIData>(query).ToList();
return monthlySales;
}
}
// get all suppliers
public static IEnumerable<SupplierAPIData> GetAll()
{
using (var db = new NorthwindDbContext())
{
return db.Suppliers
.Select(x => new SupplierAPIData
{
SupplierID = x.SupplierID,
CompanyName = x.CompanyName,
City = x.City,
Country = x.Country
})
.ToList();
}
}
public static void DeleteSupplierProducts(Supplier supplier, NorthwindDbContext db, bool doCommit = true)
{
ICollection<Product> products = supplier.Products;
if (products != null || products.Count() > 0 )
{
DeleteProductsOrderDetails(products, db, false);
db.Products.RemoveRange(products);
}
if (doCommit)
{
db.SaveChanges();
}
}