/// <summary>
/// 9.
/// Create a method that places a new order in the Northwind database.
/// The order should contain several order items.
/// Use transaction to ensure the data consistency.
/// </summary>
private static void AddOrder()
{
var databaseConnection = new NorthwindEntities();
using (databaseConnection)
{
using (var dbContextTransaction = databaseConnection.Database.BeginTransaction())
{
try
{
var newOrder = new Order()
{
CustomerID = "BOTTM",
EmployeeID = 5,
OrderDate = DateTime.Now
};
newOrder.Order_Details.Add(new Order_Detail { ProductID = 11, UnitPrice = 14.00m, Quantity = 12, Discount = 0 });
newOrder.Order_Details.Add(new Order_Detail { ProductID = 42, UnitPrice = 9.80m, Quantity = 5, Discount = 0 });
newOrder.Order_Details.Add(new Order_Detail { ProductID = 72, UnitPrice = 34.50m, Quantity = 7, Discount = 0 });
databaseConnection.Orders.Add(newOrder);
databaseConnection.SaveChanges();
dbContextTransaction.Commit();
}
catch (Exception)
{
dbContextTransaction.Rollback();
}
}
}
}
public static void UpdateCustomer(string customerToEditID, string newName)
{
var databaseConnection = new NorthwindEntities();
using (databaseConnection)
{
var customer = databaseConnection.Customers.FirstOrDefault(c => c.CustomerID == customerToEditID);
customer.ContactName = newName;
databaseConnection.SaveChanges();
}
}
public static void DeleteCustomer(string customerToEditID)
{
var databaseConnection = new NorthwindEntities();
using (databaseConnection)
{
var customer = databaseConnection.Customers.FirstOrDefault(c => c.CustomerID == customerToEditID);
if (customer != null)
{
databaseConnection.Customers.Remove(customer);
databaseConnection.SaveChanges();
}
}
}
public static void InsertCustomer(string customerID, string companyName, string contactName, string contactTitle)
{
var databaseConnection = new NorthwindEntities();
var newCustomer = new Customer
{
CustomerID = customerID,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle
};
using (databaseConnection)
{
databaseConnection.Customers.Add(newCustomer);
databaseConnection.SaveChanges();
}
}
/// <summary>
/// 5.
/// Write a method that finds all the sales by specified region and period (start / end dates).
/// </summary>
private static void FindAllOrdersByRegionAndDate(string region, DateTime startDate, DateTime endDate)
{
var databaseConnection = new NorthwindEntities();
using (databaseConnection)
{
var result =
databaseConnection.Orders
.Where(o => o.ShipRegion == region &&
o.OrderDate >= startDate &&
o.OrderDate <= endDate)
.Select(o => o.OrderID);
foreach (var item in result)
{
Console.WriteLine(item);
}
}
}
/// <summary>
/// 4.
/// Implement previous by using native SQL query and wxecuting it through the DbContext
/// </summary>
private static void ShowOrdersByCountryAndDateClassic(string country, DateTime date)
{
var databaseConnection = new NorthwindEntities();
using (databaseConnection)
{
string nativeSqlQuery =
"SELECT c.ContactName " +
"FROM Customers c " +
"INNER JOIN Orders o ON c.CustomerID = o.CustomerID " +
"WHERE o.ShipCountry = {0} " +
"AND YEAR(o.OrderDate) = {1}";
object[] parameters = { country, date.Year };
var resultingCustomers = databaseConnection.Database.SqlQuery<string>(nativeSqlQuery, parameters);
foreach (var cust in resultingCustomers)
{
Console.WriteLine(cust);
}
}
}
/// <summary>
/// 3.
/// Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
/// </summary>
private static void ShowOrdersByCountryAndDate(string country, DateTime date)
{
var databaseConnection = new NorthwindEntities();
using (databaseConnection)
{
var result =
databaseConnection.Customers
.Join(
databaseConnection.Orders,
c => c.CustomerID,
o => o.CustomerID,
(c, o) => new { Customer = c, Order = o })
.Where(x => x.Order.ShipCountry == country)
.Where(x => x.Order.OrderDate.Value.Year == date.Year)
.Select(x => x.Customer);
foreach (var item in result)
{
Console.WriteLine(item.ContactName);
}
}
}
/// <summary>
/// 10.
/// Create a stored procedures in the Northwind database for finding the total incomes
/// for given supplier name and period (start date, end date).
/// Implement a C# method that calls the stored procedure and returns the retuned record set.
/// </summary>
private static void GetTotalSupplierIncome(string supplierName, DateTime startDate, DateTime endDate)
{
//CREATE PROC [dbo].[usp_GetSupplierIncome] (@supplierName nvarchar(max), @startYear int, @endYear int)
//AS
// SELECT SUM(od.Quantity * od.UnitPrice) AS TotalIncome
// FROM Suppliers s
// INNER JOIN Products p
// ON s.SupplierID = p.SupplierID
// INNER JOIN [ORDER Details] od
// ON od.ProductID = p.ProductID
// INNER JOIN Orders o
// ON od.OrderID = o.OrderID
// WHERE s.CompanyName = @supplierName
// AND YEAR(o.OrderDate) >= @startYear
// AND YEAR(o.OrderDate) <= @endYear
//GO
//EXEC dbo.GetSupplierIncome 'Exotic Liquids', 1996, 1998
var databaseConnection = new NorthwindEntities();
using (databaseConnection)
{
var income = databaseConnection.usp_GetSupplierIncome(supplierName, startDate.Year, endDate.Year).ToList();
Console.WriteLine(income[0]);
}
}
/// <summary>
/// 8.
/// By inheriting the Employee entity class create a class which allows
/// employees to access their corresponding territories as property of type EntitySet<T>.
/// </summary>
private static void GetEmployeeTerritories()
{
var databaseConnection = new NorthwindEntities();
using (databaseConnection)
{
var result = databaseConnection.Employees;
foreach (var item in result)
{
foreach (var territory in item.GetTerritories)
{
Console.WriteLine(territory.TerritoryDescription);
}
}
}
}