public static void Main()
{
using (var db = new NorthwindEntities())
{
// 2. Create a DAO class with static methods which provide functionality for inserting, modifying and deleting customers.
Customer customer = new Customer();
customer.CompanyName = "GhostBustersLTD";
customer.CustomerID = "202";
DAO.InsertCustomer(customer, db);
Customer anotherCustomer = new Customer();
anotherCustomer.CompanyName = "GhostBustersLTD";
anotherCustomer.CustomerID = "203";
DAO.InsertCustomer(anotherCustomer, db);
DAO.DeleteCustomer("202", db);
DAO.ModifyCustomer("203", "Alcoholics Anonymous", db);
// 3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
var salesToCanada1997 = SalesOperations.FindOrdersByYearAndShipCountry(1997, "Canada", db);
foreach (var sales in salesToCanada1997)
{
Console.WriteLine(sales.ContactName);
Console.WriteLine(sales.Country);
}
// 4. Implement previous by using native SQL query and executing it through the DbContext.
SalesOperations.FindOrdersByYearAndShipCountryNativeSQL(1997, "Canada", db);
// 5. Write a method that finds all the sales by specified region and period (start / end dates).
SalesOperations.FindSalesByRegionByPeriod(new DateTime(1996, 01, 01), new DateTime(1997, 12, 31), "BC", db);
8. By inheriting the Employee entity class create a class which allows employees to access their corresponding territories as property of type EntitySet<T>.
public static List<Customer> FindOrdersByYearAndShipCountry(int year, string country, NorthwindEntities db)
{
var ordersByYearAndCountry = db.Customers
.Where(c => c.Orders
.Any(o => o.OrderDate != null &&
(o.OrderDate.Value.Year == year && o.ShipCountry == country)))
.ToList();
return ordersByYearAndCountry;
}
public static void Main()
{
// 6. Create a database called NorthwindTwin with the same structure as Northwind using the features from DbContext.
// Have to add connection string in app.config with initialcatalog = clone name
using (var db = new NorthwindEntities())
{
var result = db.Database.CreateIfNotExists();
Console.WriteLine(result);
}
}
public static void Main()
{
// Try to open two different data contexts and perform concurrent changes on the same records.
//What will happen at SaveChanges()?
//How to deal with it?
Console.WriteLine("First Connection engaged...");
Thread.Sleep(1000);
using (var firstDb = new NorthwindEntities())
{
var custDemographics = firstDb.CustomerDemographics.Count();
Console.WriteLine("Customer demographics count from first connection: {0}", custDemographics);
Thread.Sleep(1000);
var demographicInfo = new CustomerDemographic();
demographicInfo.CustomerDesc = "Family";
demographicInfo.CustomerTypeID = "404";
firstDb.CustomerDemographics.Add(demographicInfo);
Thread.Sleep(1000);
Console.WriteLine("Second Connection engaged...");
using (var secondDb = new NorthwindEntities())
{
var secondCustDemographics = secondDb.CustomerDemographics.Count();
Console.WriteLine("Customer demographics count from second connection: {0}", custDemographics);
Thread.Sleep(1000);
var secondDemographicInfo = new CustomerDemographic();
secondDemographicInfo.CustomerDesc = "Single";
secondDemographicInfo.CustomerTypeID = "304";
secondDb.CustomerDemographics.Add(secondDemographicInfo);
Thread.Sleep(1000);
var custDemographicsUpdated = secondDb.CustomerDemographics.Count();
Console.WriteLine("First connection still opened, changes unsaved. Customer demographics count from second connection: {0}", custDemographicsUpdated);
firstDb.SaveChanges();
secondDb.SaveChanges();
Console.WriteLine("Closing second connection after saving");
Thread.Sleep(1000);
}
var finalCustomerDemographicsCount = firstDb.CustomerDemographics.Count();
Console.WriteLine("First connection still opened, all changes saved. Customer demographics count from second connection: {0}", finalCustomerDemographicsCount);
Console.WriteLine("Closing first connection after saving");
}
}
public static void FindOrdersByYearAndShipCountryNativeSQL(int year, string country, NorthwindEntities db)
{
string nativeSqlQuery = @"SELECT DISTINCT c.ContactName
FROM Orders o JOIN
Customers c ON c.CustomerID = o.CustomerID
WHERE (o.ShipCountry ='{0}') AND ({1} = DATEPART(year, o.OrderDate))";
object[] parameters = { country, year };
var customers = db.Database.SqlQuery<string>(string.Format(nativeSqlQuery, parameters)).ToList();
Console.WriteLine("Orders to --------> {0} ", country);
Console.WriteLine("Customers:");
foreach (var cust in customers)
{
Console.WriteLine(cust);
}
}
public static void FindSalesByRegionByPeriod(DateTime startDate, DateTime endDate, string region, NorthwindEntities db)
{
var salesByRegionAndDate = db.Orders
.Where(o => o.ShipRegion == region)
.Where(o => o.ShippedDate < endDate)
.Where(o => o.ShippedDate > startDate)
.Select(c => new
{
c.ShippedDate,
c.ShipRegion
}).ToList();
foreach (var cust in salesByRegionAndDate)
{
Console.WriteLine("Shipped Date: {0}", cust.ShippedDate);
Console.WriteLine("Customer Region: {0}", cust.ShipRegion);
Console.WriteLine("------------------------------------");
}
}
public static void ModifyCustomer(string id, string companyName, NorthwindEntities db)
{
var customer = db.Customers.Find(id.Trim());
customer.CompanyName = companyName;
db.SaveChanges();
}
public static void InsertCustomer(Customer customer, NorthwindEntities db)
{
db.Customers.Add(customer);
db.SaveChanges();
}
public static void DeleteCustomer(string id, NorthwindEntities db)
{
var customer = db.Customers.Find(id.Trim());
db.Customers.Remove(customer);
db.SaveChanges();
}
