public static void ModifyCustomer(string id, string propertyName, string value)
{
if(string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("Id cannot be null or contains white space!");
}
if(string.IsNullOrWhiteSpace(propertyName))
{
throw new ArgumentNullException("Property name cannot be null!");
}
using(var dbContext = new NorthwindEntities())
{
var customer = dbContext.Customers
.Where(c => c.CustomerID == id)
.FirstOrDefault();
if(customer == null)
{
throw new ArgumentException("Customer with given id not found!");
}
dbContext.Entry(customer).Property(propertyName).CurrentValue = value;
dbContext.SaveChanges();
}
}
static void Main()
{
using (var db = new NorthwindEntities())
{
db.Categories
.Select(c => c.CategoryName)
.ToList()
.ForEach(c => Console.WriteLine(c));
Console.WriteLine("Problem 1: Done :)");
}
TestDao();
Console.WriteLine(new String('-', 40));
GetAllCusomersWithOrderIn1997ToCanada();
Console.WriteLine(new String('-', 40));
GetAllCusomersWithOrderIn1997ToCanadaUsingSQL();
Console.WriteLine(new String('-', 40));
FindAllOrdersByRegionAndDates("wa", new DateTime(1997, 10, 1), new DateTime(1998, 9, 30));
Console.WriteLine(new String('-', 40));
DifferentDataContext();
Console.WriteLine(new String('-', 40));
}
private static void CreateNorthwindTwin()
{
using(var dbContext = new NorthwindEntities())
{
dbContext.Database.CreateIfNotExists();
}
}
private static void GetAllCusomersWithOrderIn1997ToCanada()
{
using (var dbContext = new NorthwindEntities())
{
dbContext.Customers
.Where(c => c.Orders.Any(o => (o.OrderDate.Value.Year == 1997 && o.ShipCountry.ToLower() == "canada")))
.ToList()
.ForEach(c => Console.WriteLine(c.ContactName));
}
}
private static void FindAllOrdersByRegionAndDates(string region, DateTime startDate, DateTime endDate)
{
using(var dbContext = new NorthwindEntities())
{
dbContext.Orders
.Where(o => (o.ShipRegion.ToLower() == region) && o.OrderDate > startDate && o.OrderDate < endDate)
.Select(o => new { o.OrderDate, o.ShipRegion })
.ToList()
.ForEach(c => Console.WriteLine("{0} - {1}", c.OrderDate, c.ShipRegion));
}
}
public static void InsertCustomer(Customer customer)
{
if(customer == null)
{
throw new ArgumentNullException("Customer cannot be null!");
}
using(var dbContext = new NorthwindEntities())
{
dbContext.Customers.Add(customer);
dbContext.SaveChanges();
}
}
private static void GetAllCusomersWithOrderIn1997ToCanadaUsingSQL()
{
var query = @"SELECT DISTINCT(c.ContactName)
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE YEAR(o.OrderDate) = '1997' AND o.ShipCountry = 'Canada'";
using (var dbContext = new NorthwindEntities())
{
dbContext.Database
.SqlQuery<string>(query)
.ToList()
.ForEach(c => Console.WriteLine(c));
}
}
public static void DeleteCustomerById(string id)
{
if(string.IsNullOrWhiteSpace(id))
{
throw new ArgumentNullException("Id cannot be null or contains white space!");
}
using(var dbContext = new NorthwindEntities())
{
var customer = dbContext.Customers
.Where(c => c.CustomerID == id)
.FirstOrDefault();
if(customer == null)
{
throw new ArgumentException("Customer with given id not found!");
}
dbContext.Customers.Remove(customer);
dbContext.SaveChanges();
}
}
static void DifferentDataContext()
{
using(var dbFirst = new NorthwindEntities())
{
using(var dbSecond = new NorthwindEntities())
{
var firstProduct = dbFirst.Products.FirstOrDefault();
firstProduct.UnitsInStock = 25;
var secondProduct = dbSecond.Products.FirstOrDefault();
secondProduct.UnitsInStock = 15;
dbFirst.SaveChanges();
dbSecond.SaveChanges();
}
}
using (var dbFirst = new NorthwindEntities())
{
using (var dbSecond = new NorthwindEntities())
{
var firstProduct = dbFirst.Products.FirstOrDefault();
Console.WriteLine(firstProduct.UnitsInStock);
}
}
}
