public static void DeleteCustomer(string customerId)
 {
     NorthwindEntities northwindEntities = new NorthwindEntities();
     Customer customer = GetCustomerById(northwindEntities, customerId);
     northwindEntities.Customers.Remove(customer);
     northwindEntities.SaveChanges();
     Console.WriteLine("Customer deleted");
 }
 public static void ModifyCustomer(string customerId, string name)
 {
     NorthwindEntities northwindEntities = new NorthwindEntities();
     Customer customer = GetCustomerById(northwindEntities, customerId);
     customer.ContactName = name;
     northwindEntities.SaveChanges();
     Console.WriteLine("Customer modified");
 }
Example #3
0
 static void Edit(string id, string newContactName)
 {
     using (NorthwindEntities db = new NorthwindEntities())
     {
         var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
         customer.ContactName = newContactName;
         db.SaveChanges();
     }
 }
Example #4
0
 static void Delete(string id)
 {
     using (NorthwindEntities db = new NorthwindEntities())
     {
         var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
         db.Customers.Remove(customer);
         db.SaveChanges();
     }
 }
Example #5
0
        static void Main()
        {
            //changed App.config to initial catalog=NorthwindTwin

            using (var db = new NorthwindEntities())
            {
                var result = db.Database.CreateIfNotExists();
                Console.WriteLine(result);
            }
        }
 private static void FindOrdersByDateAndRegion(string region, DateTime start, DateTime end)
 {
     using (var db = new NorthwindEntities())
     {
         db.Orders
             .Where(o => o.ShipRegion == region && o.ShippedDate > start && o.ShippedDate < end)
             .OrderBy(o => o.ShippedDate)
             .ToList()
             .ForEach(o => Console.WriteLine("Order {0} shipped to {1} on {2}", o.OrderID, o.ShipAddress, o.ShippedDate));
     }
 }
 private static void FindCustomersByOrder(string country, int year)
 {
     using (var db = new NorthwindEntities())
     {
         db.Orders
             .Where(o => o.ShipCountry == country && o.ShippedDate.Value.Year == year)
             .Select(c => c.Customer.ContactName)
             .Distinct()
             .ToList()
             .ForEach(c => Console.WriteLine(c));
     }
 }
        private static void FindCustomersByOrder(string country, int year)
        {
            using (var db = new NorthwindEntities())
            {
                string sql = "SELECT distinct[ContactName] FROM Customers c, Orders o where c.[CustomerID]=o.CustomerID and o.[ShipCountry]='" + country + "' and year([ShippedDate])=" + year;

                db.Database
                  .SqlQuery<string>(sql)
                  .ToList()
                  .ForEach(c => Console.WriteLine(c));
            }
        }
 public static void InsertCustomer(Customer customer)
 {
     using (var db = new NorthwindEntities())
     {
         if (customer==null)
         {
             throw new ArgumentNullException("Customer cannot be null");
         }
         db.Customers.Add(customer);
         db.SaveChanges();
     }
     Console.WriteLine("Customer added");
 }
Example #10
0
        // TASK 3
        // Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
        static void FindAllCustomers(int orderDate, string shipDestination)
        {
            using (NorthwindEntities db = new NorthwindEntities())
            {
                var orders = from order in db.Orders
                             where order.OrderDate.Value.Year == orderDate && order.ShipCountry == shipDestination
                             select order;

                foreach (var item in orders)
                {
                    Console.WriteLine("Order made by: {0} with CustomerId: {1}", item.Customer.ContactName, item.Customer.CustomerID);
                }
            }
        }
Example #11
0
        // TASK 4
        // Implement previous by using native SQL query and executing it through the DbContext.
        static void FindAllCustomersWithNativeSQLQuery(int orderDate, string country)
        {
            using (NorthwindEntities db = new NorthwindEntities())
            {
                string sqlQuery = @"SELECT c.ContactName from Customers" +
                                  " c INNER JOIN Orders o ON o.CustomerID = c.CustomerID " +
                                  "WHERE (YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1});";

                object[] parameters = { orderDate, country };
                var sqlQueryResult = db.Database.SqlQuery<string>(sqlQuery, parameters);

                foreach (var order in sqlQueryResult)
                {
                    Console.WriteLine(order);
                }
            }
        }
Example #12
0
        // TASK 2
        // Create a DAO class with static methods which provide functionality for inserting, modifying and deleting customers.
        // Write a testing class.
        static void Add(string name, string id)
        {
            Customer newCustomer = new Customer()
            {
                CompanyName = name,
                CustomerID = id
            };

            using (NorthwindEntities db = new NorthwindEntities())
            {
                bool isInDB = IsInDataBase(db, id);

                if (!isInDB)
                {
                    db.Customers.Add(newCustomer);
                    db.SaveChanges();
                    Console.WriteLine("Added Successful.");
                }
                else
                {
                    throw new ArgumentException("Such customer already exists");
                }
            }
        }
 public static Customer GetCustomerById(NorthwindEntities northwindEntities, string customerId)
 {
     Customer customer = northwindEntities.Customers.FirstOrDefault(
         c => c.CustomerID == customerId);
     return customer;
 }
Example #14
0
 static bool IsInDataBase(NorthwindEntities db, string id)
 {
     bool alreadyInDB = db.Customers.Where(a => a.CustomerID == id).Any();
     return alreadyInDB;
 }
Example #15
0
        // TASK 5
        // Write a method that finds all the sales by specified region and period (start / end dates).
        static void FindSales(string region, DateTime from, DateTime to)
        {
            using (NorthwindEntities db = new NorthwindEntities())
            {
                var askedSales = db.Orders.Where(o => o.ShipRegion == region && o.OrderDate > from && o.OrderDate < to)
                                          .Select(o => new { ShipName = o.ShipName, OrderDate = o.OrderDate });

                foreach (var item in askedSales)
                {
                    Console.WriteLine(item.ShipName + " was shiped on: " + item.OrderDate);
                }
            }
        }