public static void DeleteCustomer(string customerId)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
Customer customer = GetCustomerById(northwindEntities, customerId);
northwindEntities.Customers.Remove(customer);
northwindEntities.SaveChanges();
Console.WriteLine("Customer deleted");
}
public static void ModifyCustomer(string customerId, string name)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
Customer customer = GetCustomerById(northwindEntities, customerId);
customer.ContactName = name;
northwindEntities.SaveChanges();
Console.WriteLine("Customer modified");
}
static void Edit(string id, string newContactName)
{
using (NorthwindEntities db = new NorthwindEntities())
{
var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
customer.ContactName = newContactName;
db.SaveChanges();
}
}
static void Delete(string id)
{
using (NorthwindEntities db = new NorthwindEntities())
{
var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
db.Customers.Remove(customer);
db.SaveChanges();
}
}
static void Main()
{
//changed App.config to initial catalog=NorthwindTwin
using (var db = new NorthwindEntities())
{
var result = db.Database.CreateIfNotExists();
Console.WriteLine(result);
}
}
private static void FindOrdersByDateAndRegion(string region, DateTime start, DateTime end)
{
using (var db = new NorthwindEntities())
{
db.Orders
.Where(o => o.ShipRegion == region && o.ShippedDate > start && o.ShippedDate < end)
.OrderBy(o => o.ShippedDate)
.ToList()
.ForEach(o => Console.WriteLine("Order {0} shipped to {1} on {2}", o.OrderID, o.ShipAddress, o.ShippedDate));
}
}
private static void FindCustomersByOrder(string country, int year)
{
using (var db = new NorthwindEntities())
{
db.Orders
.Where(o => o.ShipCountry == country && o.ShippedDate.Value.Year == year)
.Select(c => c.Customer.ContactName)
.Distinct()
.ToList()
.ForEach(c => Console.WriteLine(c));
}
}
private static void FindCustomersByOrder(string country, int year)
{
using (var db = new NorthwindEntities())
{
string sql = "SELECT distinct[ContactName] FROM Customers c, Orders o where c.[CustomerID]=o.CustomerID and o.[ShipCountry]='" + country + "' and year([ShippedDate])=" + year;
db.Database
.SqlQuery<string>(sql)
.ToList()
.ForEach(c => Console.WriteLine(c));
}
}
public static void InsertCustomer(Customer customer)
{
using (var db = new NorthwindEntities())
{
if (customer==null)
{
throw new ArgumentNullException("Customer cannot be null");
}
db.Customers.Add(customer);
db.SaveChanges();
}
Console.WriteLine("Customer added");
}
// TASK 3
// Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
static void FindAllCustomers(int orderDate, string shipDestination)
{
using (NorthwindEntities db = new NorthwindEntities())
{
var orders = from order in db.Orders
where order.OrderDate.Value.Year == orderDate && order.ShipCountry == shipDestination
select order;
foreach (var item in orders)
{
Console.WriteLine("Order made by: {0} with CustomerId: {1}", item.Customer.ContactName, item.Customer.CustomerID);
}
}
}
// TASK 4
// Implement previous by using native SQL query and executing it through the DbContext.
static void FindAllCustomersWithNativeSQLQuery(int orderDate, string country)
{
using (NorthwindEntities db = new NorthwindEntities())
{
string sqlQuery = @"SELECT c.ContactName from Customers" +
" c INNER JOIN Orders o ON o.CustomerID = c.CustomerID " +
"WHERE (YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1});";
object[] parameters = { orderDate, country };
var sqlQueryResult = db.Database.SqlQuery<string>(sqlQuery, parameters);
foreach (var order in sqlQueryResult)
{
Console.WriteLine(order);
}
}
}
// TASK 2
// Create a DAO class with static methods which provide functionality for inserting, modifying and deleting customers.
// Write a testing class.
static void Add(string name, string id)
{
Customer newCustomer = new Customer()
{
CompanyName = name,
CustomerID = id
};
using (NorthwindEntities db = new NorthwindEntities())
{
bool isInDB = IsInDataBase(db, id);
if (!isInDB)
{
db.Customers.Add(newCustomer);
db.SaveChanges();
Console.WriteLine("Added Successful.");
}
else
{
throw new ArgumentException("Such customer already exists");
}
}
}
public static Customer GetCustomerById(NorthwindEntities northwindEntities, string customerId)
{
Customer customer = northwindEntities.Customers.FirstOrDefault(
c => c.CustomerID == customerId);
return customer;
}
static bool IsInDataBase(NorthwindEntities db, string id)
{
bool alreadyInDB = db.Customers.Where(a => a.CustomerID == id).Any();
return alreadyInDB;
}
// TASK 5
// Write a method that finds all the sales by specified region and period (start / end dates).
static void FindSales(string region, DateTime from, DateTime to)
{
using (NorthwindEntities db = new NorthwindEntities())
{
var askedSales = db.Orders.Where(o => o.ShipRegion == region && o.OrderDate > from && o.OrderDate < to)
.Select(o => new { ShipName = o.ShipName, OrderDate = o.OrderDate });
foreach (var item in askedSales)
{
Console.WriteLine(item.ShipName + " was shiped on: " + item.OrderDate);
}
}
}
