public static void Delete(string id)
{
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
db.Customers.Remove(customer);
db.SaveChanges();
}
}
public static void Modify(string id, string newContactName)
{
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
customer.ContactName = newContactName;
db.SaveChanges();
}
}
public static void Insert(string id, string name)
{
Customer newCustomer = new Customer()
{
CompanyName = name,
CustomerID = id
};
using (NORTHWNDEntities db = new NORTHWNDEntities())
{
bool isInDB = IsInDataBase(db, id);
if (!isInDB)
{
db.Customers.Add(newCustomer);
db.SaveChanges();
Console.WriteLine("Added Successful.");
}
else
{
throw new ArgumentException("Such customer already exists");
}
}
}
private static bool IsInDataBase(NORTHWNDEntities db, string id)
{
bool alreadyInDB = db.Customers.Where(a => a.CustomerID == id).Any();
return alreadyInDB;
}
// Task.10
private static void FindTotalIncome(string name, DateTime start, DateTime end)
{
// Added in Northwind DB
/*
USE NORTHWND
GO
CREATE PROC usp_FindTotalIncome (@supplierName nvarchar(50), @startDate datetime, @endDate DateTime)
AS
SELECT SUM(od.Quantity*od.UnitPrice) AS TotalIncome
FROM Suppliers s, Products p, [ORDER Details] od, Orders o
WHERE s.SupplierID = p.SupplierID
AND od.ProductID = p.ProductID
AND od.OrderID = o.OrderID
AND s.CompanyName = @supplierName
AND (o.OrderDate >= @startDate AND o.OrderDate <= @endDate)
GO
EXEC usp_FindTotalIncome 'Tokyo Traders', '1994-01-01', '2000-12-31'
*/
NORTHWNDEntities db = new NORTHWNDEntities();
SqlParameter param1 = new SqlParameter("@companyName", name);
SqlParameter param2 = new SqlParameter("@start", start);
SqlParameter param3 = new SqlParameter("@end", end);
var result = db.Database.ExecuteSqlCommand(
"usp_FindTotalIncome @companyName, @start, @end", param1, param2, param3);
Console.WriteLine("Total Incomes from '" + name + "' are: {0}", result);
}
// Task 8
// EmployeeEx.cs class is created in EntityFramework.Model project
private static void InheritClass()
{
NORTHWNDEntities db = new NORTHWNDEntities();
Employee temp = db.Employees.First(e => e.EmployeeID == 1);
var territoriesList = temp.TerritoriesSet;
foreach (var territory in territoriesList)
{
Console.WriteLine(territory.TerritoryDescription);
}
}
// Task 9
private static void CreateNewOrder(Order order)
{
using (TransactionScope scope = new TransactionScope())
{
NORTHWNDEntities db = new NORTHWNDEntities();
var customer = db.Customers.First(c => c.CustomerID == order.CustomerID);
if (customer != null)
{
order.ShipAddress = customer.Address;
order.ShipCity = customer.City;
order.ShipPostalCode = customer.PostalCode;
order.ShipCountry = customer.Country;
}
db.Orders.Add(order);
db.SaveChanges();
scope.Complete();
}
}
// Task 7
private static void ConcurrentChages()
{
using (TransactionScope scope = new TransactionScope())
//using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new
// TransactionOptions { IsolationLevel= IsolationLevel.Snapshot }))
{
NORTHWNDEntities db = new NORTHWNDEntities();
Product product = db.Products.First(p => p.ProductID == 1);
product.ProductName = "Test 001";
db.SaveChanges();
NORTHWNDEntities db2 = new NORTHWNDEntities();
Product product2 = db2.Products.First(p => p.ProductID == 1);
product2.ProductName = "Test 002";
try
{
int result = db2.SaveChanges();
Console.WriteLine(result.ToString() +
" products changed");
}
catch (OptimisticConcurrencyException)
{
db2.Entry(product2).Reload();
//db2.Refresh(RefreshMode.ClientWins, db2.Products);
db2.SaveChanges();
}
scope.Complete();
}
}
private static void GetSchema()
{
NORTHWNDEntities db = new NORTHWNDEntities();
ObjectContext objectContext = ((IObjectContextAdapter)db).ObjectContext;
var dbSchema = objectContext.CreateDatabaseScript();
NorthwindTwin dbTwin = new NorthwindTwin();
ObjectContext objectContext2 = ((IObjectContextAdapter)dbTwin).ObjectContext;
objectContext2.ExecuteStoreCommand(dbSchema);
}
// Task 5
private static void FindAllSales
(string region, DateTime start, DateTime end)
{
NORTHWNDEntities db = new NORTHWNDEntities();
var orders = db.Orders.Where(o =>
o.ShippedDate.Value >= start &&
o.ShippedDate.Value <= end &&
o.ShipRegion == region);
foreach (var order in orders)
{
Console.WriteLine("OrderID : {0}", order.OrderID);
}
}
// Task 4
private static IEnumerable<string> FindAllCustomersSQLquery
(int year, string country)
{
NORTHWNDEntities db = new NORTHWNDEntities();
string nativeSqlQuery =
"SELECT c.ContactName as Customer " +
"FROM dbo.Orders o, Customers c " +
"WHERE YEAR(o.OrderDate) = '" + year + "'" +
"AND o.ShipCountry = '" + country + "'" +
"Group By c.ContactName";
var customers =
db.Database.SqlQuery<string>(nativeSqlQuery);
return customers;
}
// Task 3
private static void FindAllCustomers
(int year, string country)
{
NORTHWNDEntities db = new NORTHWNDEntities();
var orders = db.Orders.Where(o => o.OrderDate.Value.Year == year &&
o.ShipCountry == country);
foreach (var order in orders)
{
Console.WriteLine("Customer : {0}", order.Customer.ContactName);
}
}