public static void DeleteCustomer(string customerId)
{
using (var db = new NorthwindEntities())
{
var customerToRemove = db.Customers.Where(c => c.CustomerID == customerId).First();
db.Customers.Remove(customerToRemove);
db.SaveChanges();
}
Console.WriteLine("Customer deleted!");
}
//5.Write a method that finds all the sales by specified region and period (start / end dates).
public static void GetSalesBetween(DateTime start, DateTime end)
{
using (var db = new NorthwindEntities())
{
var sales = db.Orders.Where(o => o.OrderDate >= start && o.OrderDate <= end);
Console.WriteLine("Sales between {0} - {1}", start, end);
foreach (var sale in sales)
{
Console.WriteLine("{0} - {1}", sale.OrderID, sale.OrderDate);
}
}
}
//4.Implement previous by using native SQL query and executing it through the DbContext.
public static void CustomersWithOrdersShippedToCanadaIn1997NativeSQL()
{
using (var db = new NorthwindEntities())
{
string query = "SELECT * FROM Orders WHERE ShipCountry like 'Canada' AND OrderDate LIKE '%1997%'";
var customers = db.Orders.SqlQuery(query).Select(c => c.CustomerID).Distinct();
Console.WriteLine("Customer IDs with orders in 1997 to Canada with native SQL:");
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
}
//3.Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
public static void CustomersWithOrdersShippedToCanadaIn1997()
{
using (var db = new NorthwindEntities())
{
var customers = db.Orders
.Where(o => o.ShipCountry == "Canada" && o.OrderDate.Value.Year == 1997)
.Select(c => c.CustomerID)
.Distinct();
Console.WriteLine("Customer IDs with orders in 1997 to Canada:");
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
}
public static void Main()
{
DatabaseAccess.InsertCustomer("AAAAA", "Telerik", "aaa", "bbb", "Mladost 1", "Sofia", "Mladost", "2506", "Bulgaria", "0885555555", "094-2132-12");
Customer customer;
using (var db = new NorthwindEntities())
{
customer = db.Customers.Where(c => c.CompanyName == "Telerik").First();
}
DatabaseAccess.UpdateCustomerPhone(customer.CustomerID, "0886666666");
DatabaseAccess.DeleteCustomer(customer.CustomerID);
DatabaseAccess.CustomersWithOrdersShippedToCanadaIn1997();
DatabaseAccess.CustomersWithOrdersShippedToCanadaIn1997NativeSQL();
DatabaseAccess.GetSalesBetween(new DateTime(1997, 6, 1), new DateTime(1997, 7, 15));
//When we try to make concurrent changes with two database context the last one to call save changes wins :)
DatabaseAccess.MakeConcurrentChanges();
}
//2.Create a DAO class with static methods which provide functionality for inserting, modifying and deleting customers.
public static void InsertCustomer(
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 (var db = new NorthwindEntities())
{
db.Customers.Add(new Customer()
{
CustomerID = customerID,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax
});
db.SaveChanges();
}
Console.WriteLine("Inserted new customer!");
}
public static void UpdateCustomerPhone(
string customerId, string phone)
{
using (var db = new NorthwindEntities())
{
var customerToUpdate = db.Customers.Where(c => c.CustomerID == customerId).First();
customerToUpdate.Phone = phone;
db.SaveChanges();
}
Console.WriteLine("Customer phone changed!");
}
//7.Try to open two different data contexts and perform concurrent changes on the same records.
public static void MakeConcurrentChanges()
{
using (var db = new NorthwindEntities())
{
using (var anotherDB = new NorthwindEntities())
{
var category = db.Categories.Where(c => c.CategoryName == "Beverages").First();
category.Description = "First change";
var category2 = anotherDB.Categories.Where(c => c.CategoryName == "Beverages").First();
category2.Description = "Second Change";
db.SaveChanges();
anotherDB.SaveChanges();
}
}
}
