/// <summary>
/// Problem solved by only using 1 db connection
/// </summary>
public static void Main()
{
using (var db1 = new NorthwindEntities())
{
using (var db2 = new NorthwindEntities())
{
Console.WriteLine("First connection");
Console.WriteLine("Original -> " + db1.Employees.First().City);
db1.Employees.First().City = "Orlandovci";
Console.WriteLine("Changed -> " + db1.Employees.First().City);
Console.WriteLine("Second connection");
Console.WriteLine("Original -> " + db2.Employees.First().City);
db2.Employees.First().City = "Krivina";
Console.WriteLine("Changed -> " + db2.Employees.First().City);
db1.SaveChanges();
db2.SaveChanges();
}
}
using (var db3 = new NorthwindEntities())
{
Console.WriteLine("Third connection");
Console.WriteLine(db3.Employees.First().City);
}
}
public static void ModifyCustomer(
string customerId,
string companyName,
string contactName,
string contactTitle,
string address,
string city,
string region,
string postalCode,
string country,
string phone,
string fax)
{
var db = new NorthwindEntities();
using (db)
{
Customer customerToModify = db.Customers.FirstOrDefault(c => c.CustomerID == customerId);
customerToModify.CompanyName = companyName ?? customerToModify.CompanyName;
customerToModify.ContactName = contactName ?? customerToModify.ContactName;
customerToModify.ContactTitle = contactTitle ?? customerToModify.ContactTitle;
customerToModify.Address = address ?? customerToModify.Address;
customerToModify.City = city ?? customerToModify.City;
customerToModify.Region = region ?? customerToModify.Region;
customerToModify.PostalCode = postalCode ?? customerToModify.PostalCode;
customerToModify.Country = country ?? customerToModify.Country;
customerToModify.Phone = phone ?? customerToModify.Phone;
customerToModify.Fax = fax ?? customerToModify.Fax;
db.SaveChanges();
}
}
public static void AddCustomer(
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)
{
var db = new NorthwindEntities();
using (db)
{
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();
}
}
/// <summary>
/// Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
/// </summary>
private static void ExecuteTask3()
{
var db = new NorthwindEntities();
var shippersToCanada =
db.Customers.Where(c => c.Orders.Any(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada")).ToList();
foreach (var shippers in shippersToCanada)
{
Console.WriteLine(shippers.CompanyName);
}
}
public static void RemoveCustomer(string customerId)
{
var db = new NorthwindEntities();
using (db)
{
var customerToRemove = db.Customers.FirstOrDefault(c => c.CustomerID == customerId);
db.Customers.Remove(customerToRemove);
db.SaveChanges();
}
}
public void DeletingCustomerShouldWorkFine()
{
string customerID = "ATA";
DaoClass.AddCustomer(customerID, "Ata company", "1", "2", "3", "4", "5", "6", "7", "8", "9");
DaoClass.RemoveCustomer(customerID);
using (var db = new NorthwindEntities())
{
var deletedCustomer = db.Customers.FirstOrDefault(c => c.CustomerID == customerID);
Assert.AreEqual(deletedCustomer, null);
}
}
/// <summary>
/// Write a method that finds all the sales by specified region and period (start / end dates).
/// </summary>
private static void ExecuteTask5(string region, DateTime start, DateTime end)
{
var db = new NorthwindEntities();
var salesByRegion = db
.Orders
.Where(o => o.ShipRegion == region && o.ShippedDate.Value > start && o.ShippedDate.Value < end)
.ToList();
foreach (var sales in salesByRegion)
{
Console.WriteLine(sales.Shipper.CompanyName);
Console.WriteLine("Order ID" + sales.OrderID);
}
}
public void AddingCustomerShouldWorkFine()
{
string customerID = "ALA";
DaoClass.AddCustomer(customerID, "Ala company", "1", "2", "3", "4", "5", "6", "7", "8", "9");
using (var db = new NorthwindEntities())
{
var addedCustomer = db.Customers.FirstOrDefault(c => c.CustomerID == customerID);
var addedCustomerName = addedCustomer.CompanyName;
Assert.AreEqual("Ala company", addedCustomerName);
DaoClass.RemoveCustomer(customerID);
}
}
/// <summary>
/// Implement previous by using native SQL query and executing it through the DbContext.
/// </summary>
private static void ExecuteTask4()
{
var db = new NorthwindEntities();
var query = @"SELECT DISTINCT c.CompanyName
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
WHERE YEAR(o.ShippedDate) = '1997' AND o.ShipCountry = 'Canada'";
var shippersToCanada = db.Database.SqlQuery<string>(query).ToList();
foreach (var shippers in shippersToCanada)
{
Console.WriteLine(shippers);
}
}
public void ModifyCustomerShouldWorkCorrectly()
{
string customerID = "BETA";
using (var db = new NorthwindEntities())
{
if (db.Customers.Where(c => c.CustomerID == customerID).ToList().Count == 0)
{
DaoClass.AddCustomer(customerID, "Beta company", "1", "2", "3", "4", "5", "6", "7", "8", "9");
}
DaoClass.ModifyCustomer(customerID, "Beta modified", null, null, null, null, null, null, null, null, null);
var modifiedCustomer = db.Customers.FirstOrDefault(c => c.CustomerID == customerID);
Assert.AreEqual("Beta modified", modifiedCustomer.CompanyName);
}
}
public static void Main()
{
var db = new NorthwindEntities();
db.Database.CreateIfNotExists();
}