public static void Main()
{
var db = new NorthwindEntities();
using (db)
{
Console.WriteLine("--------------------------Task3----------------------------");
var counter = 1;
var customers = ExtensionMethods.GetCustomersWithSpecificOrders(db, 1997, "Canada");
foreach (var c in customers)
{
Console.WriteLine(counter++ + " " + c);
}
Console.WriteLine();
Console.WriteLine("--------------------------Task4----------------------------");
counter = 1;
var customersNative = ExtensionMethods.GetCustomersWithSpecificOrdersNativeSQL(db, 1997, "Canada");
foreach (var c in customersNative)
{
Console.WriteLine(counter++ + " " + c);
}
Console.WriteLine();
Console.WriteLine("--------------------------Task5----------------------------");
counter = 1;
var orders = ExtensionMethods.GetSalesByRegionAndPeriod(db, "SP", new DateTime(1998, 01, 01), DateTime.Now);
foreach (var order in orders)
{
Console.WriteLine(counter++ + " " + order);
}
}
}
public static void Main()
{
using (var db = new NorthwindEntities())
{
var isCreated = db.Database.CreateIfNotExists();
Console.WriteLine("DB is {0} created. ", isCreated ? "successfully" : "NOT");
}
}
public static void Main()
{
using (var db = new NorthwindEntities())
{
var counter = 1;
var countries = db.Employees.Select(c => c.Country).ToList();
foreach (var c in countries)
{
Console.WriteLine(counter++ + " " + c);
}
}
}
//// 4. Implement previous by using native SQL query and executing it through the DbContext.
public static IEnumerable<object> GetCustomersWithSpecificOrdersNativeSQL(NorthwindEntities db, int year = 1997, string country = "Canada")
{
string queryString =
"SELECT DISTINCT c.CompanyName, c.ContactName " +
"FROM Customers c " +
"JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE YEAR(o.ShippedDate) = " + year + " AND o.ShipCountry = '" + country + "'";
var result = db.Database.SqlQuery<CustomerInfo>(queryString).ToList();
return result;
}
//// 3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
public static IEnumerable<object> GetCustomersWithSpecificOrders(NorthwindEntities db, int year = 1997, string country = "Canada")
{
var result = db.Orders
.Where(o => o.ShippedDate.Value.Year == year && o.ShipCountry == country)
.Select(c => new
{
c.Customer.CompanyName,
c.Customer.ContactName
})
.Distinct()
.ToList();
return result;
}
public static string InsertNewCustomer(NorthwindEntities db, Customer customer)
{
if (customer == null)
{
throw new ArgumentNullException("Customer cannot be null.");
}
using (db)
{
db.Customers.Add(customer);
db.Customers.Add(customer);
db.SaveChanges();
return customer.CustomerID;
}
}
//// 5. Write a method that finds all the sales by specified region and period (start / end dates).
public static IEnumerable<object> GetSalesByRegionAndPeriod(NorthwindEntities db, string region, DateTime startDate, DateTime endDate)
{
var orders = db.Orders
.Where(o =>
o.ShipRegion == region &&
o.OrderDate > startDate &&
o.OrderDate < endDate)
.Select(o => new
{
o.OrderID,
o.OrderDate,
o.ShipName
})
.ToList();
return orders;
}
public static void UpdateCustomerCompanyName(NorthwindEntities db, string customerID, string newCompanyName)
{
if (string.IsNullOrWhiteSpace(customerID))
{
throw new ArgumentException(string.Format("Invalid ID : {0}.", customerID));
}
using (db)
{
var customer = db.Customers
.FirstOrDefault(c => c.CustomerID == customerID);
if (customer == null)
{
throw new ArgumentException("No such customer with this id.");
}
customer.CompanyName = newCompanyName;
db.SaveChanges();
}
}
public static void Delete(NorthwindEntities db, string customerID)
{
if (string.IsNullOrWhiteSpace(customerID))
{
throw new ArgumentException("Id is null, empty or contains only whitespaces.");
}
using (db)
{
var customer = db.Customers
.FirstOrDefault(c => c.CustomerID == customerID);
if (customer == null)
{
throw new ArgumentException("No such customer with this id.");
}
db.Customers.Remove(customer);
db.SaveChanges();
}
}
//// To deal with this problem next time use Transactions
public static void Main()
{
var db1 = new NorthwindEntities();
var db2 = new NorthwindEntities();
var employeeFromDb1 = db1.Employees.FirstOrDefault();
var employeeFromDb2 = db2.Employees.FirstOrDefault();
Console.WriteLine("Name from db1: {0}", employeeFromDb1.FirstName + " " + employeeFromDb1.LastName);
Console.WriteLine("Name from db2: {0}", employeeFromDb2.FirstName + " " + employeeFromDb2.LastName);
employeeFromDb1.LastName = "context 1";
// Second name will overwrite the first.
employeeFromDb2.LastName = "context 2";
db1.SaveChanges();
db2.SaveChanges();
var dbResult = new NorthwindEntities().Employees.FirstOrDefault();
Console.WriteLine("\nResult: : {0}", dbResult.FirstName + " " + dbResult.LastName);
}
public static void Main()
{
var db = new NorthwindEntities();
}