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); } }