public static void FindCustomersByOrdersAndShipping(NorthwindEntities db, string shipCountry, int orderYear) { var selectedCustomers = db.Orders .Where(o => o.OrderDate.Value.Year == orderYear && o.ShipCountry == shipCountry) .Select(c => c.Customer.CompanyName) .ToList(); Print(selectedCustomers); }
static void Main() { var db = new NorthwindEntities(); using (db) { var query = db.usp_FindTotalIncomeForSupplierAndPeriod("Exotic Liquids", new DateTime(1996, 1, 1), new DateTime(1997, 12, 31)).ToList(); Console.WriteLine(query[0]); } }
public static void FindCustomersByOrdersAndShippingNativeSQL(NorthwindEntities db, string country, int year) { string nativeSQLQuery = "SELECT c.CompanyName " + "FROM Orders o " + "INNER JOIN Customers c " + "ON c.CustomerID = o.CustomerID " + "WHERE o.ShipCountry = 'Canada' AND DATEDIFF(YEAR, o.OrderDate, '1997') = 0 "; var selectedCustomers = db.Database.SqlQuery<string>(nativeSQLQuery).ToList(); Print(selectedCustomers); }
public static void DeleteCustomer(string customerID) { using (var db = new NorthwindEntities()) { var customer = db.Customers .FirstOrDefault(c => c.CustomerID == customerID); db.Customers.Remove(customer); db.SaveChanges(); } }
static void Main() { //Create a database called NorthwindTwin with //the same structure as Northwind using the //features from DbContext //need to change the name of the initial catalog=NorthwindTwin in App.config using (var db = new NorthwindEntities()) { db.Database.CreateIfNotExists(); } }
public static void FindSalesByRegionAndPeriod(NorthwindEntities db, string region, DateTime periodStartDate, DateTime periodEndDate) { var salesQuery = db.Orders .Where(o => o.ShipRegion == region && o.OrderDate >= periodStartDate && o.OrderDate <= periodEndDate) .Select(o => o.OrderID) .ToList(); foreach (var sale in salesQuery) { Console.WriteLine(sale); } }
public static void ModifyCustomer(string customerID, string contactName, string companyName, string address) { var db = new NorthwindEntities(); using (db) { var customer = db.Customers .FirstOrDefault(c => c.CustomerID == customerID); customer.ContactName = contactName; customer.CompanyName = companyName; customer.Address = address; db.SaveChanges(); } }
static void Main() { NorthwindEntities db = new NorthwindEntities(); Order newOrder = new Order { CustomerID = "1", OrderDate = DateTime.Now, ShippedDate = DateTime.Now, ShipAddress = "Mladost 1", ShipCountry = "Bulgaria" }; InsertOrderUsingExternalTransaction(db, newOrder); }
static void Main() { NorthwindEntities db = new NorthwindEntities(); using (db) { //Write a method that finds all customers who have orders made in 1997 and shipped to Canada FindCustomersByOrdersAndShipping(db, "Canada", 1997); Console.WriteLine("--------------"); //Implement previous by using native SQL query and executing it through the DbContext FindCustomersByOrdersAndShippingNativeSQL(db, "Canada", 1997); Console.WriteLine("--------------"); //Write a method that finds all the sales by specified region and period (start / end dates) FindSalesByRegionAndPeriod(db, "RJ", new DateTime(1996, 1, 1), new DateTime(1997, 12, 31)); } }
public static string InsertCustomer(Customer customer) { if (customer == null) { throw new ArgumentNullException("The customer can't be null"); } else { var db = new NorthwindEntities(); using (db) { db.Customers.Add(customer); db.SaveChanges(); } return customer.CustomerID; } }
static void Main() { //Try to open two different data contexts and perform concurrent changes //on the same records. What will happen at SaveChanges()? - it will going to update //the record two times first to "Ihu" than to "Bla" //How to deal with it? - use only one connection var db1 = new NorthwindEntities(); var db2 = new NorthwindEntities(); Customer customer1 = db1.Customers.FirstOrDefault(c => c.CustomerID == "A"); Customer customer2 = db2.Customers.FirstOrDefault(c => c.CustomerID == "A"); customer1.ContactName = "Ihu"; customer2.ContactName = "Bla"; db1.SaveChanges(); db2.SaveChanges(); }
static void Main() { Employee testempl = new Employee(); var db = new NorthwindEntities(); using (db) { Employee empl = db.Employees.First(); var territories = empl.TerritoriesSet; Console.WriteLine("All territories for employee {0} are:", empl.FirstName); foreach (var territory in territories) { Console.WriteLine(territory.TerritoryDescription); } } }
private static void InsertOrderUsingExternalTransaction(NorthwindEntities db, Order order) { using (db) { using(var transaction = db.Database.BeginTransaction()) { try { db.Orders.Add(order); db.SaveChanges(); transaction.Commit(); } catch (Exception ex) { Console.WriteLine("Can't insert record", ex.Message); transaction.Rollback(); } } } }