public static void FindCustomersByOrdersAndShipping(NorthwindEntities db, string shipCountry, int orderYear)
{
var selectedCustomers = db.Orders
.Where(o => o.OrderDate.Value.Year == orderYear && o.ShipCountry == shipCountry)
.Select(c => c.Customer.CompanyName)
.ToList();
Print(selectedCustomers);
}
static void Main()
{
var db = new NorthwindEntities();
using (db)
{
var query = db.usp_FindTotalIncomeForSupplierAndPeriod("Exotic Liquids", new DateTime(1996, 1, 1), new DateTime(1997, 12, 31)).ToList();
Console.WriteLine(query[0]);
}
}
public static void FindCustomersByOrdersAndShippingNativeSQL(NorthwindEntities db, string country, int year)
{
string nativeSQLQuery = "SELECT c.CompanyName " +
"FROM Orders o " +
"INNER JOIN Customers c " +
"ON c.CustomerID = o.CustomerID " +
"WHERE o.ShipCountry = 'Canada' AND DATEDIFF(YEAR, o.OrderDate, '1997') = 0 ";
var selectedCustomers = db.Database.SqlQuery<string>(nativeSQLQuery).ToList();
Print(selectedCustomers);
}
public static void DeleteCustomer(string customerID)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers
.FirstOrDefault(c => c.CustomerID == customerID);
db.Customers.Remove(customer);
db.SaveChanges();
}
}
static void Main()
{
//Create a database called NorthwindTwin with
//the same structure as Northwind using the
//features from DbContext
//need to change the name of the initial catalog=NorthwindTwin in App.config
using (var db = new NorthwindEntities())
{
db.Database.CreateIfNotExists();
}
}
public static void FindSalesByRegionAndPeriod(NorthwindEntities db, string region, DateTime periodStartDate, DateTime periodEndDate)
{
var salesQuery = db.Orders
.Where(o => o.ShipRegion == region && o.OrderDate >= periodStartDate && o.OrderDate <= periodEndDate)
.Select(o => o.OrderID)
.ToList();
foreach (var sale in salesQuery)
{
Console.WriteLine(sale);
}
}
public static void ModifyCustomer(string customerID, string contactName, string companyName, string address)
{
var db = new NorthwindEntities();
using (db)
{
var customer = db.Customers
.FirstOrDefault(c => c.CustomerID == customerID);
customer.ContactName = contactName;
customer.CompanyName = companyName;
customer.Address = address;
db.SaveChanges();
}
}
static void Main()
{
NorthwindEntities db = new NorthwindEntities();
Order newOrder = new Order
{
CustomerID = "1",
OrderDate = DateTime.Now,
ShippedDate = DateTime.Now,
ShipAddress = "Mladost 1",
ShipCountry = "Bulgaria"
};
InsertOrderUsingExternalTransaction(db, newOrder);
}
static void Main()
{
NorthwindEntities db = new NorthwindEntities();
using (db)
{
//Write a method that finds all customers who have orders made in 1997 and shipped to Canada
FindCustomersByOrdersAndShipping(db, "Canada", 1997);
Console.WriteLine("--------------");
//Implement previous by using native SQL query and executing it through the DbContext
FindCustomersByOrdersAndShippingNativeSQL(db, "Canada", 1997);
Console.WriteLine("--------------");
//Write a method that finds all the sales by specified region and period (start / end dates)
FindSalesByRegionAndPeriod(db, "RJ", new DateTime(1996, 1, 1), new DateTime(1997, 12, 31));
}
}
public static string InsertCustomer(Customer customer)
{
if (customer == null)
{
throw new ArgumentNullException("The customer can't be null");
}
else
{
var db = new NorthwindEntities();
using (db)
{
db.Customers.Add(customer);
db.SaveChanges();
}
return customer.CustomerID;
}
}
static void Main()
{
//Try to open two different data contexts and perform concurrent changes
//on the same records. What will happen at SaveChanges()? - it will going to update
//the record two times first to "Ihu" than to "Bla"
//How to deal with it? - use only one connection
var db1 = new NorthwindEntities();
var db2 = new NorthwindEntities();
Customer customer1 = db1.Customers.FirstOrDefault(c => c.CustomerID == "A");
Customer customer2 = db2.Customers.FirstOrDefault(c => c.CustomerID == "A");
customer1.ContactName = "Ihu";
customer2.ContactName = "Bla";
db1.SaveChanges();
db2.SaveChanges();
}
static void Main()
{
Employee testempl = new Employee();
var db = new NorthwindEntities();
using (db)
{
Employee empl = db.Employees.First();
var territories = empl.TerritoriesSet;
Console.WriteLine("All territories for employee {0} are:", empl.FirstName);
foreach (var territory in territories)
{
Console.WriteLine(territory.TerritoryDescription);
}
}
}
private static void InsertOrderUsingExternalTransaction(NorthwindEntities db, Order order)
{
using (db)
{
using(var transaction = db.Database.BeginTransaction())
{
try
{
db.Orders.Add(order);
db.SaveChanges();
transaction.Commit();
}
catch (Exception ex)
{
Console.WriteLine("Can't insert record", ex.Message);
transaction.Rollback();
}
}
}
}
