//2.Create a DAO class with static methods which provide functionality for inserting, // modifying and deleting customers. Write a testing class public void InsertCustomer( string companyName, string contactName, string contactTitle, string address, string city, string region, string postalCode, string country, string phone, string fax) { var db = new NORTHWNDEntities(); using (db) { Customer myCustomer = new Customer(); myCustomer.Address = address; myCustomer.City = city; myCustomer.CompanyName = companyName; myCustomer.ContactName = contactName; myCustomer.ContactTitle = contactTitle; myCustomer.Country = country; myCustomer.Fax = fax; myCustomer.Phone = phone; myCustomer.Region = region; myCustomer.PostalCode = postalCode; string cusId = contactName.Substring(0, 5); myCustomer.CustomerID = cusId; db.Customers.Add(myCustomer); db.SaveChanges(); } }
public void UpdateCustomer( string oldName, string companyName, string contactName, string contactTitle, string address, string city, string region, string postalCode, string country, string phone, string fax) { var db = new NORTHWNDEntities(); using (db) { var customer = db.Customers.Where(x => x.CompanyName == oldName).First(); customer.Address = address; customer.City = city; customer.CompanyName = companyName; customer.ContactName = contactName; customer.ContactTitle = contactTitle; customer.Country = country; customer.Fax = fax; customer.Phone = phone; customer.Region = region; customer.PostalCode = postalCode; string cusId = contactName.Substring(0, 5); customer.CustomerID = cusId; db.SaveChanges(); } }
public void DeleteCustomer(string customerName) { var db = new NORTHWNDEntities(); using (db) { var customer = db.Customers.Where(x => x.CompanyName == customerName).First(); db.Customers.Remove(customer); db.SaveChanges(); } }
public void FindCustomer(string customerName) { var db = new NORTHWNDEntities(); using (db) { var customer = db.Customers.Where(x => x.CompanyName == customerName).First(); Console.WriteLine(customer.CompanyName); } }
//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. public void CalculateIncoms(string supplierName, DateTime minDate, DateTime maxDate) { var db = new NORTHWNDEntities(); using (db) { //Execute first USP.sql var result = db.usp_FindTotalIncome(minDate.Year, maxDate.Year, supplierName); Console.WriteLine(result.FirstOrDefault()); } }
//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. public void MakeOrder(Order myOrder, List<Tuple<int, int, int>> items) { var db = new NORTHWNDEntities(); using (db) { using(TransactionScope scope = new TransactionScope()) { var newOrder = db.Orders.Add(myOrder); foreach (var item in items) { Order_Detail myDetail = new Order_Detail { OrderID = newOrder.OrderID, ProductID = item.Item1, Quantity = (short)item.Item2, UnitPrice = (short)item.Item3 }; db.Order_Details.Add(myDetail); } try { db.SaveChanges(); scope.Complete(); Console.WriteLine("OK"); } catch (Exception) { Console.WriteLine("Rollback!!"); scope.Dispose(); } } } }
//7.Try to open two different data contexts and perform concurrent changes on the same records. //What will happen at SaveChanges()? How to deal with it? public void CheckConcurrent() { var db = new NORTHWNDEntities(); var db2 = new NORTHWNDEntities(); var region = db.Regions.Find(5); region.RegionDescription = "Burgas"; db.SaveChanges(); Console.WriteLine("Updated"); var region2 = db.Regions.Find(5); db2.Regions.Remove(region2); db2.SaveChanges(); Console.WriteLine("Deleted"); }
//5.Write a method that finds all the sales by specified region and period (start / end dates). public void FindSales(string region, DateTime minDate, DateTime maxDate) { var db = new NORTHWNDEntities(); using (db) { var sales = db.Orders.Where(x => x.ShipRegion == region). Where(x => x.ShippedDate > minDate). Where(x => x.ShippedDate < maxDate); foreach (var sale in sales) { Console.WriteLine(sale.ShippedDate + " " + sale.ShipRegion); } } }
//4.Implement previous by using native SQL query and executing it through the DbContext. public void FindCustomerByYearSql() { var db = new NORTHWNDEntities(); using (db) { string query = @"SELECT DISTINCT CompanyName FROM Customers c JOIN Orders o ON o.CustomerID = c.CustomerID WHERE o.ShipCountry = 'Canada' AND YEAR(o.OrderDate) = '1997'"; var queryResult = db.Database.SqlQuery<string>(query); foreach (var res in queryResult) { Console.WriteLine(res); } } }
//3.Write a method that finds all customers who have orders made in 1997 and shipped to Canada public void FindCustomerByYear() { var db = new NORTHWNDEntities(); using (db) { DateTime time = new DateTime(1997, 1, 1); var customers = db.Orders.Where(x => x.OrderDate.Value.Year == time.Year).Where(x => x.ShipCountry == "Canada"); foreach (var customer in customers.Distinct()) { Console.WriteLine(customer.Customer.CompanyName); } } }