public static void AddCustomer(string customerID, string companyName, 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)
{
using (NorthwindEntities DBcontext = new NorthwindEntities())
{
Customer customer = new Customer()
{
CustomerID = customerID,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax
};
DBcontext.Customers.Add(customer);
DBcontext.SaveChanges();
Console.WriteLine("Row is inserted");
}
}
public static void CustomersWithOrdersIn1997FromCanada()
{
using (NorthwindEntities DBcontext = new NorthwindEntities())
{
var selectedCustomers =
from order in DBcontext.Orders
join customer in DBcontext.Customers on order.CustomerID equals customer.CustomerID
where order.OrderDate.Value.Year == 1997 && order.ShipCountry == "Canada"
orderby customer.CompanyName, customer.ContactName, order.OrderDate, order.ShipCountry
select new
{
CompanyName = customer.CompanyName,
CustomerName = customer.ContactName,
OrderDate = order.OrderDate,
ShipCountry = order.ShipCountry
};
int counter = 0;
foreach (var customer in selectedCustomers)
{
counter++;
Console.WriteLine("{0}. Company name: {1} || Customer name: {2} || Date: {3} || ShipCountry: {4}", counter, customer.CompanyName, customer.CustomerName, customer.OrderDate, customer.ShipCountry);
Console.WriteLine();
}
}
}
public static void DeleteCustomer(string customerID)
{
using (NorthwindEntities DBcontext = new NorthwindEntities())
{
try
{
Customer customer = DBcontext.Customers.First(c => c.CustomerID == customerID);
DBcontext.Customers.Remove(customer);
DBcontext.SaveChanges();
Console.WriteLine("Row is deleted");
}
catch (InvalidOperationException e)
{
throw new InvalidOperationException("There is no customer with this ID", e);
}
}
}
public static void UpdateCustomer(string customerID, string companyName, 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)
{
using (NorthwindEntities DBcontext = new NorthwindEntities())
{
Customer customer = DBcontext.Customers.First(x => x.CustomerID == customerID);
customer.CompanyName = companyName ?? customer.CompanyName;
customer.ContactName = contactName ?? customer.ContactName;
customer.ContactTitle = contactTitle ?? customer.ContactTitle;
customer.Address = address ?? customer.Address;
customer.City = city ?? customer.City;
customer.Region = region ?? customer.Region;
customer.PostalCode = postalCode ?? customer.PostalCode;
customer.Country = country ?? customer.Country;
customer.Phone = phone ?? customer.Phone;
customer.Fax = fax ?? customer.Fax;
DBcontext.SaveChanges();
Console.WriteLine("Row is updated");
}
}
public static void SQLQueryCustomersWithOrdersIn1997FromCanada()
{
using (NorthwindEntities DBcontext = new NorthwindEntities())
{
string nativeSQLQuery = ("SELECT c.CompanyName " + "FROM Orders o JOIN Customers c " + "ON (o.CustomerID = c.CustomerID) " + "WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada' ");
var result = DBcontext.Database.SqlQuery<string>(nativeSQLQuery);
int counter = 0;
foreach (var customer in result)
{
counter++;
Console.WriteLine("{0}. Company name: {1}", counter, customer);
Console.WriteLine();
}
//Other query which will take company name, contact name, order date and ship country
//SELECT c.CompanyName, c.ContactName AS CustomerName, o.OrderDate, o.ShipCountry
//FROM Orders o JOIN Customers c
// ON (o.CustomerID = c.CustomerID)
//WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada'
//GROUP BY c.CompanyName, c.ContactName, o.OrderDate, o.ShipCountry
}
}
