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