public static ICollection<Order> FindSales(ExtendDbContext database, string region, DateTime startDate, DateTime endDate) { var sales = from o in database.Orders where o.ShipRegion == region && (o.OrderDate >= startDate && o.OrderDate <= endDate) select o; ICollection<Order> salesList = sales.ToList(); return salesList; }
public static void InsertCustomer(ExtendDbContext database, Customer customer) { if (!string.IsNullOrWhiteSpace(customer.CompanyName)) { database.Customers.Add(customer); database.SaveChanges(); } else { throw new ArgumentNullException("Company name is mandatory"); } }
public static ICollection<Customer> FindCustomers(ExtendDbContext database, DateTime date, string country) { var customers = from o in database.Orders join c in database.Customers on o.CustomerID equals c.CustomerID where o.OrderDate >= date && o.ShipCountry == country select c; ICollection<Customer> customerList = customers.ToList(); return customerList; }
public static ICollection<string> FindCustomersSQL(ExtendDbContext database, DateTime date, string country) { string query = "SELECT c.ContactName " + "FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID " + "WHERE o.OrderDate >= {0} AND o.ShipCountry = {1}"; object[] parameters = { date, country }; var resultView = database.Database.SqlQuery<string>(query, parameters); ICollection<string> customersList = resultView.ToList(); return customersList; }
public static void AddNewOrder(ExtendDbContext db, ICollection<Order> orders) { using (TransactionScope transaction = new TransactionScope()) { foreach (var order in orders) { db.Orders.Add(order); } db.SaveChanges(); transaction.Complete(); } }
public static void UpdateCustomer(ExtendDbContext database, string id, string newContactName) { var customer = database.Customers.Where(x => x.CustomerID == id).FirstOrDefault(); if (customer != null) { customer.ContactName = newContactName; database.SaveChanges(); } else { throw new InvalidOperationException("This customer doens't exists."); } }
public static void Delete(ExtendDbContext database, string id) { var customer = database.Customers.Where(x => x.CustomerID == id).FirstOrDefault(); if (customer != null) { database.Customers.Remove(customer); database.SaveChanges(); } else { throw new InvalidOperationException("This customer doens't exists."); } }
static void Main(string[] args) { Order firstOrder = new Order() { CustomerID = "ALFKI", EmployeeID = 1, OrderDate = DateTime.Now, RequiredDate = DateTime.Now.AddDays(10), ShippedDate = DateTime.Now.AddDays(9), ShipVia = 1, Freight = 500.0m, ShipName = "First Ship", ShipAddress = "Kralstvo Trqvna", ShipCity = "Gabrovo", ShipRegion = "Sitnqkovo", ShipPostalCode = "it9au93", ShipCountry = "Bulgaria" }; Order secondOrder = new Order() { CustomerID = "ALFKI", EmployeeID = 1, OrderDate = DateTime.Now, RequiredDate = DateTime.Now.AddDays(10), ShippedDate = DateTime.Now.AddDays(9), ShipVia = 1, Freight = 500.0m, ShipName = "Second Ship", ShipAddress = "Kralstvo Trqvna", ShipCity = "Blabla", ShipRegion = "BlahBlah", ShipPostalCode = "itu93", ShipCountry = "Bulgaria" }; IList<Order> orders = new List<Order>(); orders.Add(firstOrder); orders.Add(secondOrder); using (var database = new ExtendDbContext()) { AddNewOrder(database, orders); Console.WriteLine("Order(s) added successfully!"); } }
static void Main() { using (var database = new ExtendDbContext()) { /* Task 2 Customer customer = new Customer(); customer.CustomerID = "GFDSA"; customer.CompanyName = "NewCompanyName"; customer.ContactName = "NewContactName"; DataAccessObject.InsertCustomer(database, customer); DataAccessObject.UpdateCustomer(database, "GFDSA", "JustNewContactName"); DataAccessObject.Delete(database, "GFDSA"); */ var findedCustomers = FindCustomers(database, new DateTime(1995, 01, 01), "Canada"); foreach (var customer in findedCustomers) { Console.WriteLine("{0}", customer.ContactName); } Console.WriteLine("=========================================================="); var findedCustomersSQL = FindCustomersSQL(database, new DateTime(1995, 01, 01), "Canada"); foreach (var customer in findedCustomersSQL) { Console.WriteLine(customer); } Console.WriteLine("=========================================================="); var sales = FindSales(database, "RJ", new DateTime(1990, 1, 1), new DateTime(2000, 1, 1)); foreach (var sale in sales) { Console.WriteLine("Sale from {0} region from {1}", sale.ShipRegion, sale.OrderDate); } } }