static void Main() { var db = new NORTHWNDEntities(); var pesho = new Customer() { CustomerID = "Pesho", CompanyName = "Pesho's Company" }; // DAO.InsertCustomer(pesho, db); // DAO.DeleteCustomer(pesho.CustomerID, db); // DAO.ModifyCustomerInfo("ANATR", db, "NEW COMPANY", "Pesho"); foreach (var cust in DAO.GetCustomersByOrderDateAndShippingCountry(1997, "Canada", db)) { Console.WriteLine(cust.ContactName); Console.WriteLine(cust.Country); } Console.WriteLine(new string('=', 30)); foreach (var customerName in DAO.GetCustomersByOrderDateAndShippingCountryNativeSQL(db)) { Console.WriteLine(customerName); } Console.ReadKey(); }
public static float getAveragePrice() { using (var db = new NORTHWNDEntities()) { var minPrice = (float)db.Products.Select(p => p.UnitPrice).Average(); return(minPrice); } }
public static float GetMaximumPrice() { using (var db = new NORTHWNDEntities()) { var maxPrice = (float)db.Products.Select(p => p.UnitPrice).Max(); return(maxPrice); } }
static void Main(string[] args) { using (var db = new NORTHWNDEntities()) { Application.EnableVisualStyles(); Application.Run(new Interface()); } }
public static HashSet<Customer> GetCustomersByOrderDateAndShippingCountry(int year, string shippingDestination, NORTHWNDEntities dataBase) { var result = dataBase.Orders.Where(o => o.OrderDate.Value.Year == year && o.ShipCountry == shippingDestination) .Select(o => o.Customer) .ToList(); var hashSet = new HashSet<Customer>(result); return hashSet; }
public static float GetAverageTotalPrice() { using (var db = new NORTHWNDEntities()) { var avg_total = db.Customers.Select(c => c.Orders.Sum(o => o.Order_Details.Sum(od => (float)od.Quantity * (float)od.UnitPrice))).Average(); return(avg_total); } }
public static Array GetProductDetail() { using (var db = new NORTHWNDEntities()) { var products = db.Products.Select(p => new { p.ProductID, p.ProductName, Price = (float)p.UnitPrice, PriceInVAT = (float)p.UnitPrice + (float)p.UnitPrice * (float)0.07 }).ToArray(); return(products); } }
public static Array GetCustomerDetail() { using (var db = new NORTHWNDEntities()) { var customers = db.Customers.Select(c => new { c.CustomerID, c.CompanyName, c.ContactName, TotalOrderPrice = (float?)c.Orders.Sum(o => (float)o.Order_Details.Sum(g => (float)g.Quantity * (float)g.UnitPrice)) }).ToArray(); return(customers); } }
public static IList<string> GetCustomersByOrderDateAndShippingCountryNativeSQL(NORTHWNDEntities dataBase) { var result = new List<Customer>(); string sqlQuery = "SELECT DISTINCT c.ContactName " + "FROM Customers c " + "JOIN Orders o " + "ON c.CustomerID = o.CustomerID " + "WHERE YEAR(o.OrderDate) = 1997 " + "AND o.ShipCountry = 'Canada';"; var customers = dataBase.Database.SqlQuery<string>(sqlQuery).ToList(); return customers; }
public static void ModifyCustomerInfo(string customerId, NORTHWNDEntities dataBase, string companyName = null, string contactName = null, string contactTitle = null, string address = null, string city = null, string region = null, string postalCode = null, string country = null, string phone = null, string fax = null) { var customer = dataBase.Customers.FirstOrDefault(x => x.CustomerID.ToLower() == customerId.ToLower()); customer.CompanyName = companyName; customer.ContactName = contactName; customer.ContactTitle = contactTitle; customer.Address = address; customer.City = city; customer.Region = region; customer.PostalCode = postalCode; customer.Country = country; customer.Phone = phone; customer.Fax = fax; dataBase.SaveChanges(); }
public static void InsertCustomer(Customer customer, NORTHWNDEntities dataBase) { dataBase.Customers.Add(customer); dataBase.SaveChanges(); }
public static void DeleteCustomer(string customerId, NORTHWNDEntities dataBase) { var customer = dataBase.Customers.FirstOrDefault(x => x.CustomerID.ToLower() == customerId.ToLower()); dataBase.Customers.Remove(customer); dataBase.SaveChanges(); }