public static void Main() { string regionName = "RJ"; DateTime startDate = new DateTime(1997, 1, 1); DateTime endDate = new DateTime(1998, 1, 1); using (var northwindEntities = new NorthwindEntities()) { var sales = from finishedOrder in northwindEntities.Orders.Include("Order Details").Include("Products") where ((finishedOrder.ShipRegion == regionName) && (finishedOrder.ShippedDate != null) && (DateTime.Compare(startDate, finishedOrder.ShippedDate.Value) <= 0) && (DateTime.Compare(finishedOrder.ShippedDate.Value, endDate) <= 0)) select new { OrderDate = finishedOrder.OrderDate, ShippedDate = finishedOrder.ShippedDate, CompanyName = finishedOrder.Customer.CompanyName, Details = finishedOrder.Order_Details, }; int counter = 0; foreach (var order in sales) { counter++; Console.WriteLine(GetOrderString(order)); } Console.WriteLine(Environment.NewLine + counter + " finished sales found."); } }
public static void Main() { // The script for generating the stored procedure is in the main folder for this project - 10.CreateStoredProcedure. // Warning: you must map the procedure to the name GetSupplierIncome via the database model: right-click on the model - // update, right-click again - add new - function import. I am enclosing (just in case) a script with my current version // of the database with the procedure in it in the project folder - you must still map though. using (NorthwindEntities northwindEntites = new NorthwindEntities()) { Console.WriteLine((double)northwindEntites.GetSupplierIncome(1, new DateTime(1996, 7, 15), new DateTime(1996, 8, 24)).FirstOrDefault()); } }
static void Main() { using (var northwindEntities = new NorthwindEntities()) { StringBuilder scriptToExecute = new StringBuilder(); scriptToExecute.AppendLine("Use NorthwindTwin"); scriptToExecute.AppendLine((northwindEntities as IObjectContextAdapter).ObjectContext.CreateDatabaseScript()); northwindEntities.Database.ExecuteSqlCommand("CREATE DATABASE NorthwindTwin"); northwindEntities.Database.ExecuteSqlCommand(scriptToExecute.ToString()); } }
public static void DeleteCustomer(string customerId) { NorthwindEntities northwindEntities = new NorthwindEntities(); Customer customerToDelete = northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerId); if (customerToDelete == null) { throw new System.Data.EntityException("There is no such customer."); } northwindEntities.Customers.Remove(customerToDelete); northwindEntities.SaveChanges(); }
public static void Main(string[] args) { using (NorthwindEntities northwindEntities = new NorthwindEntities()) { foreach (var entity in northwindEntities.Employees) { foreach (var item in entity.TerritoriesEntitySet) { Console.WriteLine(item.TerritoryDescription); } } } }
public static void Main() { NorthwindEntities northwindEntities = new NorthwindEntities(); string nativeSqlQuery = "SELECT DISTINCT c.CompanyName + ' represented by ' + c.ContactName " + "FROM dbo.Customers c JOIN dbo.Orders o ON c.CustomerID = o.CustomerID " + "WHERE o.ShipCountry = {0} AND o.OrderDate IS NOT NULL AND YEAR(o.OrderDate) = {1}"; object[] parameters = { "Canada", 1997 }; var customers = northwindEntities.Database.SqlQuery<string>(nativeSqlQuery, parameters); foreach (var customer in customers) { Console.WriteLine(customer); } }
public static void Main() { NorthwindEntities firstContext = new NorthwindEntities(); NorthwindEntities secondContext = new NorthwindEntities(); ModifyCustomer(firstContext, "ALFKI", city: "Moscow"); ModifyCustomer(secondContext, "ALFKI", city: "London"); // Since database is not locked, the last modification will be the one to remain. According to MSDN: // http://msdn.microsoft.com/en-us/library/bb739065.aspx SaveChanges(boolean) is obsolete and should // not be used. firstContext.SaveChanges(); secondContext.SaveChanges(); firstContext.Dispose(); secondContext.Dispose(); }
public static void ModifyCustomer(NorthwindEntities northwindEntities, string customerId, string companyName = null, string contactName = null, string address = null, string city = null, string region = null, string postalCode = null, string country = null, string phone = null, string fax = null) { Customer customerToModify = northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerId); if (customerToModify == null) { throw new System.Data.EntityException("No such customer exists."); } customerToModify.CompanyName = string.IsNullOrEmpty(companyName) ? customerToModify.CompanyName : companyName; customerToModify.ContactName = string.IsNullOrEmpty(contactName) ? customerToModify.ContactName : contactName; customerToModify.Address = string.IsNullOrEmpty(address) ? customerToModify.Address : address; customerToModify.City = string.IsNullOrEmpty(city) ? customerToModify.City : city; customerToModify.Region = string.IsNullOrEmpty(region) ? customerToModify.Region : region; customerToModify.PostalCode = string.IsNullOrEmpty(postalCode) ? customerToModify.PostalCode : postalCode; customerToModify.Country = string.IsNullOrEmpty(country) ? customerToModify.Country : country; customerToModify.Phone = string.IsNullOrEmpty(phone) ? customerToModify.Phone : phone; customerToModify.Fax = string.IsNullOrEmpty(fax) ? customerToModify.Fax : fax; }
public static void InsertCustomer(string customerId, string companyName, string contactName = null, string address = null, string city = null, string region = null, string postalCode = null, string country = null, string phone = null, string fax = null) { NorthwindEntities northwindEntities = new NorthwindEntities(); int numberOfCustomersBefore = (from customer in northwindEntities.Customers select customer).Count(); Customer customerToInsert = new Customer { CustomerID = customerId, CompanyName = companyName, Address = address, City = city, PostalCode = postalCode, Region = region, Country = country, Phone = phone, Fax = fax }; northwindEntities.Customers.Add(customerToInsert); northwindEntities.SaveChanges(); int numberOfCustomersAfter = (from customer in northwindEntities.Customers select customer).Count(); if (numberOfCustomersAfter - numberOfCustomersBefore == 1) { Console.WriteLine("Customer added successfully."); } else { throw new System.Data.EntityException("Incorrect insertion result."); } }
static void Main() { NorthwindEntities northwindEntities = new NorthwindEntities(); DateTime date = DateTime.Now; var customers = from order in northwindEntities.Orders where ((order.OrderDate != null) && (order.OrderDate.Value.Year == 1997) && (order.ShipCountry == "Canada")) select new { CompanyName = order.Customer.CompanyName, ContactName = order.Customer.ContactName }; // This is done so that the same names are not listed more than once. customers = customers.Distinct(); foreach (var customer in customers) { Console.WriteLine("Company name is {0} and contact name is {1}", customer.CompanyName, customer.ContactName); } }
public static void InsertOrder(int shipperId, string customerId, int employeeId, string destionation) { using (NorthwindEntities northwindEntities = new NorthwindEntities()) { try { using (TransactionScope scope = new TransactionScope()) { Order orderToInsert = new Order { CustomerID = customerId, EmployeeID = employeeId, OrderDate = DateTime.Now, RequiredDate = DateTime.Now.Add(new TimeSpan(100, 0, 0, 0)), ShipVia = shipperId, Freight = 3000000, ShipCountry = destionation, Customer = northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerId), Employee = northwindEntities.Employees.FirstOrDefault(e => e.EmployeeID == employeeId), Shipper = northwindEntities.Shippers.FirstOrDefault(s => s.ShipperID == shipperId), Order_Details = new HashSet<Order_Detail>() }; orderToInsert.Order_Details.Add(new Order_Detail { ProductID = 2, Quantity = 34, Discount = 1, Order = orderToInsert }); orderToInsert.Order_Details.Add(new Order_Detail { ProductID = 3, Quantity = 70, Discount = 1, Order = orderToInsert }); orderToInsert.Order_Details.Add(new Order_Detail { ProductID = 4, Quantity = 110, Discount = 1, Order = orderToInsert }); northwindEntities.Orders.Add(orderToInsert); northwindEntities.SaveChanges(); scope.Complete(); //This ensures that the transaction has been completed. int orderId = orderToInsert.OrderID; Console.WriteLine(orderId); } } catch (Exception ex) { Console.WriteLine(ex.Message); } } }