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