// 2.Create a DAO class with static methods which provide functionality
// for inserting, modifying and deleting customers.
public static void InsertCustomer(Customer customer)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
northwindEntities.Customers.Add(customer);
northwindEntities.SaveChanges();
Console.WriteLine("Customer inserted!");
}
public static void UpdateCustomerContactName(string customerId, string contactName)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
var customerToModify = GetCustomerById(northwindEntities, customerId);
customerToModify.ContactName = contactName;
northwindEntities.SaveChanges();
Console.WriteLine("Customer updated!");
}
public static void DeleteCustomer(string customerId)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
var customer = GetCustomerById(northwindEntities, customerId);
northwindEntities.Customers.Remove(customer);
northwindEntities.SaveChanges();
Console.WriteLine("Customer deleted!");
}
static void Main()
{
NorthwindEntities northwind = new NorthwindEntities();
var nancy = northwind.Employees.FirstOrDefault();
var nancyExtended = new EmployeeExtended();
nancyExtended.EmployeeID = nancy.EmployeeID;
nancyExtended.GetEmployeeTerritories();
foreach (var ter in nancyExtended.Territories)
{
Console.WriteLine("{0} {1} {2}", ter.TerritoryID, ter.TerritoryDescription.Trim(), ter.RegionID);
}
}
// 3.Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
public static void GetAllCustomersWhoOrderedInYearXAndShippedToCountry(DateTime date, string country)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
var customers = from customer in northwindEntities.Customers
join order in northwindEntities.Orders
on customer.CustomerID equals order.CustomerID
where order.ShipCountry == country &&
order.OrderDate.Value.Year == date.Year
select new
{
CustomerId = customer.CustomerID,
ContactName = customer.ContactName,
OrderDate = order.OrderDate,
ShipCountry = order.ShipCountry
};
foreach (var customer in customers)
{
Console.WriteLine("{0} {1} {2} {3}",customer.CustomerId, customer.ContactName,
customer.OrderDate, customer.ShipCountry);
}
}
// UNFINISHED
// 5.Write a method that finds all the sales by specified region and period (start / end dates).
public static void GetSalesByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
NorthwindEntities northwind = new NorthwindEntities();
var salesSatisfiengDate = from order in northwind.Orders
join orderDetail in northwind.Order_Details
on order.OrderID equals orderDetail.OrderID
join product in northwind.Products
on orderDetail.ProductID equals product.ProductID
where order.OrderDate >= startDate && order.OrderDate <= endDate
orderby order.OrderDate
select new
{
EmployeeID = order.EmployeeID,
ProductName = product.ProductName,
OrderDate = order.OrderDate,
//Region = from employee in northwind.Employees
// join territory in northwind.Territories
// on employee.
};
/* Left to join this condition by EmployeeID with salesSatisfiengDate
SELECT e.EmployeeID, e.FirstName, e.LastName,
t.TerritoryDescription, r.RegionDescription
FROM Employees e
INNER JOIN EmployeeTerritories et
ON e.EmployeeID = et.EmployeeID
INNER JOIN Territories t
ON et.TerritoryID = t.TerritoryID
INNER JOIN Region r
ON t.RegionID = r.RegionID
*/
//foreach (var sale in salesSatisfiengDate)
//{
// Console.WriteLine("{0} {1} {2}", sale.EmployeeID, sale.ProductName, sale.OrderDate);
//}
}
static void Main()
{
NorthwindEntities northwind = new NorthwindEntities();
//Customer alf = new Customer
//{
// CustomerID = "ALFAK",
// CompanyName = "Melmac Industries"
//};
//CustomerDao.InsertCustomer(alf);
//CustomerDao.UpdateCustomerContactName(alf.CustomerID, "Alf Tanner");
//CustomerDao.DeleteCustomer(alf.CustomerID);
//// 3.Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
//var year = DateTime.Parse("1997-01-01");
//var country = "Canada";
//CustomerDao.GetAllCustomersWhoOrderedInYearXAndShippedToCountry(year, country);
//// 4.Implement previous by using native SQL query and executing it through the DbContext
//var customerSearchQuery = "SELECT c.CustomerID, c.ContactName, o.OrderDate, o.ShipCountry " +
// "FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.ShipCountry = 'Canada' " +
// "AND(o.OrderDate >= '1997' AND o.OrderDate <= '1997-12-31')";
//var customersWhoOrderedIn1997AndShippedToCanada =
// northwind.Database.SqlQuery<CustomerAndOrder>(customerSearchQuery);
//foreach (var customer in customersWhoOrderedIn1997AndShippedToCanada)
//{
// Console.WriteLine("{0} {1} {2} {3}", customer.CustomerID, customer.ContactName,
// customer.OrderDate, customer.ShipCountry);
//}
//Console.WriteLine();
// 5.Write a method that finds all the sales by specified region and period (start / end dates).
//GetSalesByRegionAndPeriod("SP", DateTime.Parse("1997-05-08"), DateTime.Parse("1998-11-08"));
// 7.Try to open two different data contexts and perform concurrent changes
// on the same records.
// Q: What will happen at SaveChanges() ?
// A: It`s using optimistic locking, so no change made in the first DBContext
// will reflect in the second DBContext, until the application is restarted or
// a new DBContext(pointing to the same DB) is created after the change is made
// Q: How to deal with it ?
// A: With "using" statement. This way we have one and after we are done with it
// or open new one it will have the changes.
NorthwindEntities northwindSecond = new NorthwindEntities();
var firstEmployeeInNorthwind = northwind.Employees.FirstOrDefault();
var firstEmployeeInNorthwindSecond = northwindSecond.Employees.FirstOrDefault();
Console.WriteLine("Before Change: ");
PrintEmployeeInfo(firstEmployeeInNorthwind);
PrintEmployeeInfo(firstEmployeeInNorthwindSecond);
firstEmployeeInNorthwind.FirstName = "Bob Ross";
firstEmployeeInNorthwind.LastName = "Take over the wheel";
northwind.SaveChanges();
Console.WriteLine("After Change: ");
PrintEmployeeInfo(firstEmployeeInNorthwind);
PrintEmployeeInfo(firstEmployeeInNorthwindSecond);
}
public static Customer GetCustomerById(NorthwindEntities entities, string customerId)
{
var customer = entities.Customers.FirstOrDefault(
p => p.CustomerID == customerId);
return customer;
}