private static void InsertNewCustomerInCustomerTable(string customerId,
string companyName,
string contactName,
string contactTitle,
string address,
string city,
string region,
string postalCode,
string country,
string phone,
string fax)
{
using (var db = new NorthwindEntities())
{
Customer newCustomer = new Customer();
newCustomer.CustomerID = customerId;
newCustomer.CompanyName = companyName;
newCustomer.ContactName = contactName;
newCustomer.ContactTitle = contactTitle;
newCustomer.Address = address;
newCustomer.City = city;
newCustomer.Region = region;
newCustomer.PostalCode = postalCode;
newCustomer.Country = country;
newCustomer.Phone = phone;
newCustomer.Fax = fax;
db.Customers.Add(newCustomer);
db.SaveChanges();
}
}
private static void CreateNorthwindTwin()
{
using (var db = new NorthwindEntities())
{
db.Database.CreateIfNotExists();
db.SaveChanges();
}
}
private static void DeleteCustomerFromCustomerTable(string customerId)
{
using (var db = new NorthwindEntities())
{
Customer customer = GetCustomerByCustomerId(customerId, db);
db.Customers.Remove(customer);
db.SaveChanges();
}
}
private static void FindAllCustomerOrderedToCanadaIn1997()
{
using (var db = new NorthwindEntities())
{
var customers = db.Orders
.Where(x => x.ShippedDate > new DateTime(1997, 1, 1))
.Where(x => x.ShippedDate <= new DateTime(1997, 12, 31))
.Where(x => x.ShipCountry == "Canada")
.OrderBy(x => x.Customer.CompanyName)
.Select(c => c.Customer.CompanyName)
.ToList();
foreach (var customer in customers)
{
Console.WriteLine(" --> {0}", customer);
}
}
}
private static void FindAllSalesByRegionAndPeriod(string region, DateTime periodBegin, DateTime periodEnd)
{
using (var db = new NorthwindEntities())
{
var sales = db.Order_Details
.Where(x => x.Order.ShipRegion == region)
.Where(x => x.Order.OrderDate >= periodBegin)
.Where(x => x.Order.OrderDate <= periodEnd)
.Select(x => x.Product.ProductName)
.OrderBy(x => x)
.ToList();
foreach (var product in sales)
{
Console.WriteLine(" --> {0}", product);
}
}
}
private static void FindAllCustomerOrderedToCanadaIn1997UsingSQLQuery()
{
using (var db = new NorthwindEntities())
{
string query = @"SELECT c.CompanyName AS [Company]
FROM Orders o
JOIN Customers c
ON o.CustomerID = c.CustomerID
WHERE (OrderDate BETWEEN '1997.01.01' AND '1997.12.31') AND
ShipCountry = 'Canada'
ORDER BY c.CompanyName";
var customers = db.Database.SqlQuery<View>(query);
foreach (var customer in customers)
{
Console.WriteLine(" --> {0}", customer.Company);
}
}
}
private static void ModifyCustomerInCustomerTable(string customerId,
string companyName,
string contactName,
string contactTitle,
string address,
string city,
string region,
string postalCode,
string country,
string phone,
string fax)
{
using (var db = new NorthwindEntities())
{
Customer customer = GetCustomerByCustomerId(customerId, db);
customer.CustomerID = customerId;
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;
db.SaveChanges();
}
}
private static Customer GetCustomerByCustomerId(string customerId, NorthwindEntities db)
{
Customer customer = db.Customers.FirstOrDefault(c => c.CustomerID == customerId);
return customer;
}
