示例#1
0
        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
                    };
            }
        }
示例#2
0
        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();
            }
        }
示例#3
0
 // 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();
     }
 }
示例#4
0
        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);
            }
        }
示例#5
0
 // 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();
     }
 }
示例#6
0
        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();
                }
            }
        }
示例#7
0
        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);
            }
        }
示例#8
0
        // 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;
        }
示例#9
0
        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;
            }
        }
示例#10
0
 // 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();
     }
 }
示例#11
0
        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();
            }
        }