public Product GetProduct(int productId) { using (var context = new NWContext()) { return context.Products.Find(productId); } }
public int Add(Region region) { // TODO: Add Unit Test if (region == null) throw new ArgumentNullException("region", "region is null."); using (var dbContext = new NWContext()) { /* NOTE: * The TerritoryID column in Territories is a string - nvarchar(20) - rather than an integer. * The existing data in Northwind Traders uses the zip code of the city/town as the TerritoryID. * This sample just "simplifies" and assigns the territory description as the ID, since we're * in Canada and we aren't using a single zip or postal code. */ foreach (var territory in region.Territories) if (string.IsNullOrEmpty(territory.TerritoryID)) territory.TerritoryID = territory.TerritoryDescription; /* NOTE: * The RegionID column in Regions is an integer, but it is not an IDENTITY column. * As such, we're simply going to get the next highest ID available. */ if (region.RegionID <= 0) region.RegionID = dbContext.Regions.Max(item => item.RegionID) + 1; dbContext.Regions.Add(region); dbContext.SaveChanges(); return region.RegionID; } }
public List<CustomerOrderSummary> GetCustomerOrderSummaries() { var dbContext = new NWContext(); var data = (from purchase in dbContext.Orders where purchase.OrderDate.HasValue select new CustomerOrderSummary() { OrderDate = purchase.OrderDate.Value, // see http://blog.dreamlabsolutions.com/post/2008/11/17/LINQ-Method-cannot-be-translated-into-a-store-expression.aspx Freight = purchase.Freight ?? 0m, // purchase.Freight.GetValueOrDefault(), Subtotal = purchase.Order_Details .Sum(x => (decimal?)(x.UnitPrice * x.Quantity) ) ?? 0, // NOTE: See Footnote 1 Discount = purchase.Order_Details .Sum(x => x.UnitPrice * x.Quantity * (((decimal)((int)(x.Discount * 100))) / 100) // NOTE: See Footnote 2 ), Total = purchase.Order_Details.Sum(x => (x.UnitPrice * x.Quantity) - (x.UnitPrice * x.Quantity * (((decimal)((int)(x.Discount * 100))) / 100) // NOTE: See Footnote 2 ) ), ItemCount = purchase.Order_Details.Count(), ItemQuantity = purchase.Order_Details.Sum(x => (short?)x.Quantity) ?? 0, AverageItemUnitPrice = purchase.Order_Details.Average(x => (decimal?)x.UnitPrice) ?? 0, CompanyName = purchase.Customer.CompanyName, ContactName = purchase.Customer.ContactName, ContactTitle = purchase.Customer.ContactTitle, CustomerId = purchase.CustomerID }).ToList(); return data; }
//TODO: Create a method called GetOrders() that will return a list of Order objets from teh database. public List<Order> GetOrders() { using (var context = new NWContext()) { var result = context.Orders; return result.ToList(); } }
public List<Region> GetRegions() { using (var context = new NWContext()) { var result = context.Regions.Include(item => item.Territories).OrderBy(item => item.RegionDescription); return result.ToList(); } }
public List<Employee> GetEmployees() { using (var context = new NWContext()) { var result = context.Employees; return result.ToList(); } }
public int AddShipper(Shipper info) { using (var context = new NWContext()) { context.Shippers.Add(info); context.SaveChanges(); return info.ShipperID; } }
public int AddProduct(Product info) { using (var context = new NWContext()) { context.Products.Add(info); context.SaveChanges(); return info.ProductID; } }
public void DeleteShipper(Shipper info) { using (var context = new NWContext()) { var found = context.Shippers.Find(info.ShipperID); if (found != null) { context.Shippers.Remove(found); context.SaveChanges(); } } }
public void DeleteProduct(Product info) { using (var context = new NWContext()) { var found = context.Products.Find(info.ProductID); if (found != null) { context.Products.Remove(found); context.SaveChanges(); } } }
public List<ProductSaleSummary> GetProductSaleSummaries() { // NOTE: See Footnote 1 // NOTE: See Footnote 2 var dbContext = new NWContext(); var data = (from item in dbContext.Products where !item.Discontinued select new ProductSaleSummary() { TotalSales = item.Order_Details.Sum(x => (decimal?)(x.UnitPrice * x.Quantity)) ?? 0, // NOTE: See Footnote 1 TotalDiscount = item.Order_Details .Sum(x => (decimal?) (x.UnitPrice * x.Quantity * (((decimal)((int)(x.Discount * 100))) / 100) // NOTE: See Footnote 2 )) ?? 0, SaleCount = item.Order_Details.Count(), SaleQuantity = item.Order_Details.Sum(x => (short?)x.Quantity) ?? 0, AverageUnitPrice = item.Order_Details.Average(x => (decimal?)x.UnitPrice) ?? 0, ProductName = item.ProductName, QuantityPerUnit = item.QuantityPerUnit, UnitsInStock = item.UnitsInStock.HasValue ? item.UnitsInStock.Value : (short)0, UnitsOnOrder = item.UnitsOnOrder.HasValue ? item.UnitsOnOrder.Value : (short)0, ReorderLevel = item.ReorderLevel.HasValue ? item.ReorderLevel.Value : (short)0, Discontinued = item.Discontinued, CurrentUnitPrice = item.UnitPrice.HasValue ? item.UnitPrice.Value : 0, CategoryId = item.CategoryID.HasValue ? item.CategoryID.Value : 0, ProductId = item.ProductID }).ToList(); var dbInventoryContext = new NWContext(); foreach (var item in data) if (item.CategoryId > 0) item.CategoryName = dbInventoryContext.Categories.Find(item.CategoryId).CategoryName; return data; }
public void Update(Region region, List<Territory> territories) { // TODO: Add Unit Test if (region == null) throw new ArgumentNullException("region", "region is null."); if (territories == null) throw new ArgumentNullException("territories", "territories is null."); using (var dbContext = new NWContext()) { foreach (var item in territories) { var found = dbContext.Territories.Find(item.TerritoryID); if (found != null) { /* NOTE: * Pre-process the Territory IDs to see if they should be "synced" with the name/description. * This will be the case if, in the original, the ID was the same as the description */ string foundTerritoryID = found.TerritoryID; string foundTerritoryDescription = found.TerritoryDescription.Trim(); // HACK: Turns out, the column is nchar(50), not an nvarchar.... string itemTerritoryID = item.TerritoryID; string itemTerritoryDescription = item.TerritoryDescription.Trim(); if (foundTerritoryID.Equals(foundTerritoryDescription) && !itemTerritoryID.Equals(itemTerritoryDescription)) { item.TerritoryID = itemTerritoryDescription; dbContext.Territories.Remove(found); // Because the PK has changed... dbContext.Territories.Add(item); // Because the PK has changed... } } } dbContext.Entry(region).State = EntityState.Modified; dbContext.SaveChanges(); } }
public Shipper GetShipper(int shipperId) { using (var context = new NWContext()) { return context.Shippers.Find(shipperId); } }
public void UpdateShipper(Shipper info) { //NOTE: See question and commentary on // http://stackoverflow.com/questions/15336248/entity-framework-5-updating-a-record using (var context = new NWContext()) { context.Shippers.Attach(info); context.Entry(info).State = EntityState.Modified; context.SaveChanges(); } }
public IList<Shipper> ListShippers() { using (var context = new NWContext()) { return context.Shippers.ToList(); //var result = context.Regions.Include(item => item.Territories).OrderBy(item => item.RegionDescription); //return result.ToList(); } }
public IList<Product> ListProducts() { using (var context = new NWContext()) { return context.Products.ToList(); } }