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;
}
}