public static string ModifyCustomer(string customerID, string companyName, string contactName = null, string contactTitle = null, string address = null, string city = null, string region = null, string postalCode = null, string country = null, string phone = null, string fax = null) { using (NorthwindEntities db = new NorthwindEntities()) { Customer customer = db.Customers.First(x => x.CustomerID == customerID); customer.CompanyName = companyName ?? customer.CompanyName; customer.ContactName = contactName ?? customer.ContactName; customer.ContactTitle = contactTitle ?? customer.ContactTitle; customer.Address = address ?? customer.Address; customer.City = city ?? customer.City; customer.Region = region ?? customer.Region; customer.PostalCode = postalCode ?? customer.PostalCode; customer.Country = country ?? customer.Country; customer.Phone = phone ?? customer.Phone; customer.Fax = fax ?? customer.Fax; if (IsThereSameCustomer(customerID)) { db.SaveChanges(); string result = "The customer is modified"; return result; } else { string result = "There isn't cutomer with this id"; return result; } } }
// 5. Write a method that finds all the sales by specified region and period (start / end dates). public static void FindSales(string region, DateTime startDate, DateTime endDate) { using (NorthwindEntities db = new NorthwindEntities()) { var sales = db.Orders .Where(o => o.ShipRegion == region && o.OrderDate.HasValue && (o.OrderDate > startDate || o.OrderDate < endDate)) .GroupBy(o => o.ShipName); db.SaveChanges(); foreach (var s in sales) { Console.WriteLine(s.Key); } } }
// 3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada. public static void FindCustomers() { using (NorthwindEntities db = new NorthwindEntities()) { var customers = db.Orders .Where(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada") .GroupBy(o => o.Customer.CompanyName); db.SaveChanges(); foreach (var c in customers) { Console.WriteLine("Customer name: {0}", c.Key); } } }
// 4. Implement previous by using native SQL query and executing it through the DbContext. public static void FindCustomersDBContext() { using (NorthwindEntities db = new NorthwindEntities()) { var customers = db.Database.SqlQuery<string>(@"SELECT c.CompanyName FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada' GROUP BY c.CompanyName"); db.SaveChanges(); foreach (var c in customers) { Console.WriteLine("Customer name: {0}", c); } } }
public static string DeleteCustomer(string customerID) { using (NorthwindEntities db = new NorthwindEntities()) { Customer customer = db.Customers.First(x => x.CustomerID == customerID); db.Customers.Remove(customer); if (IsThereSameCustomer(customerID)) { db.SaveChanges(); string result = "The customer is deleted"; return result; } else { string result = "There isn't cutomer with this id"; return result; } } }
public static string InsertCustomer(string customerID, string companyName) { using (NorthwindEntities db = new NorthwindEntities()) { Customer customer = new Customer { CustomerID = customerID, CompanyName = companyName }; db.Customers.Add(customer); if (IsThereSameCustomer(customerID)) { string result = "The customer is already in the DB"; return result; } else { db.SaveChanges(); string result = "The customer is inserted"; return result; } } }
private static bool IsThereSameCustomer(string id) { using (NorthwindEntities db = new NorthwindEntities()) { bool result = db.Customers.Any(x => x.CustomerID == id); return result; } }