public static void DeleteCustomerRecored(string customerId)
 {
     using (var northwindEntities = new NorthwindEntities())
     {
         var customer = CustomersRepository.GetCustomerById(northwindEntities, customerId);
         northwindEntities.Customers.Remove(customer);
         northwindEntities.SaveChanges();
     }
 }
Beispiel #2
0
 // 8.
 // By inheriting the Employee entity class create a class which allows employees to access their corresponding
 // territories as property of type EntitySet<T>.
 public static void ShowEmployeeTerritories()
 {
     using (var db = new NorthwindEntities())
     {
         var employee = db.Employees.FirstOrDefault();
         foreach (var correspondingTerritory in employee.CorrespondingTerritories)
         {
             Console.WriteLine(correspondingTerritory.TerritoryDescription);
         }
     }
 }
Beispiel #3
0
        // 5. Write a method that finds all the sales by specified region and period (start / end dates).
        public static IEnumerable<Order> FindAllSales(string region, DateTime startDate, DateTime endDate)
        {
            using (var northwindEntities = new NorthwindEntities())
            {
                //return (from order in northwindEntities.Orders
                //        where order.ShipRegion == region && startDate <= order.ShippedDate && order.ShippedDate <= endDate
                //        select order).ToList();

                return
                    northwindEntities.Orders.Where(
                        o => o.ShipRegion == region && startDate <= o.ShippedDate && o.ShippedDate <= endDate).ToList();
            }
        }
        public static void AddCustomerRecord(string customerId, string companyName)
        {
            using (var northwindEntities = new NorthwindEntities())
            {
                northwindEntities.Customers.Add(new Customer
                {
                    CustomerID = customerId,
                    CompanyName = companyName
                });

                northwindEntities.SaveChanges();
            }
        }
Beispiel #5
0
        // 7.
        // Try to open two different data contexts and perform concurrent changes on the same records.
        // What will happen at SaveChanges()?
        // How to deal with it?
        public static void DifferentContexts()
        {
            using (NorthwindEntities db1 = new NorthwindEntities(), db2 = new NorthwindEntities())
            {
                var customer1 = db1.Customers.FirstOrDefault();

                Console.WriteLine(customer1.ContactName);

                customer1.ContactName = "Ivan";

                var customer2 = db2.Customers.FirstOrDefault();

                Console.WriteLine(customer2.ContactName);

                customer2.ContactName = "Pesho";

                db1.SaveChanges();
                db2.SaveChanges();
            }
        }
        //2. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
        // To the examiner: Check the commented code below! I solved this problem in 3 different ways.
        public static IEnumerable<Customer> GetCustomersWithOrdersIn1997ToCanada()
        {
            using (var northwindEntities = new NorthwindEntities())
            {
                //Console.WriteLine(from c in northwindEntities.Customers
                //                  join o in northwindEntities.Orders on c.CustomerID equals o.CustomerID
                //                  where o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997
                //                  orderby c.CompanyName
                //                  select c);

                //return (from c in northwindEntities.Customers
                //       join o in northwindEntities.Orders on c.CustomerID equals o.CustomerID
                //       where o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997
                //       orderby c.CompanyName
                //       select c).ToList();

                //Console.WriteLine(
                //                  northwindEntities.Orders
                //                  .Where(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997)
                //                  .Join(
                //                  northwindEntities.Customers,
                //                  o => o.CustomerID,
                //                  c => c.CustomerID,
                //                  (o, c) => c)
                //                  .OrderBy(c => c.CompanyName));

                //return
                //    northwindEntities.Orders
                //    .Where(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997)
                //    .Join(
                //    northwindEntities.Customers,
                //    o => o.CustomerID,
                //    c => c.CustomerID,
                //    (o, c) => c)
                //    .OrderBy(c => c.CompanyName)
                //    .ToList();

                //Console.WriteLine(
                //    northwindEntities.Customers
                //    .Select(c => c)
                //    .Where(c => c.Orders.Any(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997)));

                //return
                //    northwindEntities.Customers
                //    .Select(c => c)
                //    .Where(c => c.Orders.Any(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997))
                //    .ToList();

                // 4. Implement previous by using native SQL query and executing it through the DbContext.
                string query =
            @"SELECT c.CustomerID,
               c.CompanyName,
               c.ContactName,
               c.ContactTitle,
               c.Address,
               c.City,
               c.Region,
               c.PostalCode,
               c.Country,
               c.Phone,
               c.Fax
            FROM Customers c
            WHERE EXISTS (SELECT * FROM Orders o
              WHERE c.CustomerID = o.CustomerID AND o.ShipCountry = 'Canada' AND YEAR(o.OrderDate) = 1997)";

                return northwindEntities.Database.SqlQuery<Customer>(query).ToList();
            }
        }
 private static Customer GetCustomerById(NorthwindEntities northwindEntities, string customerId)
 {
     return northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerId);
 }
 public static void UpdateCustomerRecord(string customerId, string contactName)
 {
     using (var northwindEntities = new NorthwindEntities())
     {
         var customer = CustomersRepository.GetCustomerById(northwindEntities, customerId);
         customer.ContactName = contactName;
         northwindEntities.SaveChanges();
     }
 }