public static IEnumerable<Customer> GetAllCustomersWithOrdersIn1997ToCanadaWithQuery(NorthwindEntities db) { // Does not show distinct rows! string query = "select * from Customers c, Orders o " + "where o.CustomerID = c.CustomerID and datediff(year, o.OrderDate, '1997-01-01') = 0 and o.ShipCountry = 'Canada' "; var customers = db.Database.SqlQuery<Customer>(query); return customers.ToList(); }
public static IEnumerable<Customer> GetAllCustomersWithOrdersIn1997ToCanada(NorthwindEntities db) { // Shows only distinct rows! var customers = from c in db.Customers where (from o in c.Orders where o.ShipCountry == "Canada" select o).Count() != 0 select c; return customers.ToList(); }
public static void ModifyCustomer(NorthwindEntities db, string customerId, Action<Customer> operation) { Console.WriteLine("MODIFYING CUSTOMER..."); Customer customer = db.Customers.FirstOrDefault(x => x.CustomerID.ToLower() == customerId.ToLower()); if (customer != null) { operation.Invoke(customer); db.SaveChanges(); } Console.WriteLine("DONE"); }
public static void Main() { using (var db = new NorthwindEntities()) { // MODIFY CUSTOMER //NorthwindDao.ModifyCustomer(db: db, customerId: "alfki", operation: (customer => customer.City = "Sofia")); // INSERT CUSTOMER //NorthwindDao.InsertCustomer(new Customer //{ // CustomerID = "PESHO", // CompanyName = "Pesho Inc.", // ContactName = "Goshko", // ContactTitle = "CEO", // Address = "Studentski grad", // City = "Sofia", // PostalCode = "1000", // Country = "Bulgaria" //}); // DELETE CUSTOMER //NorthwindDao.DeleteCustomer(db: db, customerId: "alfki"); // GET ALL CUSTOMERS WITH ORDERS IN 1997 TO CANADA //var customers = NorthwindDao.GetAllCustomersWithOrdersIn1997ToCanada(db); // GET ALL CUSTOMERS WITH ORDERS IN 1997 TO CANADA USING NATIVE QUERY //var customers = NorthwindDao.GetAllCustomersWithOrdersIn1997ToCanadaWithQuery(db); //foreach (var item in customers) //{ // Console.WriteLine("{0} - {1}", item.CustomerID, item.ContactName); //} //db.SaveChanges(); // GET ALL ORDERS FROM A REGION AND IN A TIME SPAN var orders = NorthwindDao.GetAllSalesBySpecifiedRegionAndPeriod(db, "RJ", new DateTime(1995, 01, 01), new DateTime(2000, 01, 01)); foreach (var item in orders) { Console.WriteLine("{0} - {1}", item.OrderID, item.CustomerID); } // USE THE NEW EMPLOYEE PROPERTY //var territories = db.Employees.Where(e => e.EmployeeID == 1).FirstOrDefault().TerritoriesEntities.ToList(); //foreach (var item in territories) //{ // Console.WriteLine(item.TerritoryDescription); //} } }
public void UpdateCustomerShouldChangeDbRecord() { var northwindDbMock = new NorthwindEntities(); Mock.Arrange(() => northwindDbMock.Customers).ReturnsCollection(this.db); var expected = "Ivan Borimechkata"; NorthwindDao.ModifyCustomer(northwindDbMock, "pen", c => c.ContactName = expected); var actual = this.db.Where(x => x.CustomerID == "PEN") .FirstOrDefault() .ContactName; Assert.AreEqual(expected, actual); }
public static void InsertCustomer(NorthwindEntities db, Customer customer) { Console.WriteLine("INSERTING CUSTOMER..."); var allCustomers = db.Customers; foreach (var item in allCustomers) { if (item.CustomerID.ToLower() == customer.CustomerID.ToLower()) { return; } } db.Customers.Add(customer); db.SaveChanges(); Console.WriteLine("DONE"); }
public void AddCustomerShouldAddDbRecord() { var customer = new Customer() { CustomerID = "ZXC", ContactName = "Asd Qwerty" }; var northwindDbMock = new NorthwindEntities(); Mock.Arrange(() => northwindDbMock.Customers).ReturnsCollection(this.db); Mock.Arrange(() => northwindDbMock.Customers .Add(customer)) .DoInstead((Customer c) => { this.db.Add(c); }); NorthwindDao.InsertCustomer(northwindDbMock, customer); Assert.AreEqual(expected: 3, actual: this.db.Count); }
public static void DeleteCustomer(NorthwindEntities db, string customerId) { Console.WriteLine("DELETING CUSTOMER..."); var customer = db.Customers.FirstOrDefault(x => x.CustomerID.ToLower() == customerId.ToLower()); if (customer != null) { var orders = db.Orders.Where(x => x.CustomerID.ToLower() == customerId.ToLower()).ToList(); foreach (var order in orders) { order.CustomerID = null; } db.SaveChanges(); db.Customers.Remove(customer); db.SaveChanges(); } Console.WriteLine("DONE"); }
public static IEnumerable<Order> GetAllSalesBySpecifiedRegionAndPeriod(NorthwindEntities db, string region, DateTime start, DateTime end) { var result = db.Orders.Where(o => o.OrderDate <= end && o.OrderDate >= start && o.ShipRegion == region).ToList(); return result; }