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();
}
