public static void AddCustomer(string id, string name, string title, string phone, string fax, string address, string postalCode, string city, string region, string country, string company) { using (NorthwindEntities northwind = new NorthwindEntities()) { Customer customer = new Customer(); customer.CustomerID = id; customer.ContactName = name; customer.ContactTitle = title; customer.Phone = phone; customer.Fax = fax; customer.Address = address; customer.PostalCode = postalCode; customer.City = city; customer.Region = region; customer.Country = country; customer.CompanyName = company; if (northwind.Customers.Find(id) == null) { northwind.Customers.Add(customer); } northwind.SaveChanges(); } }
public static void DeleteCustomer(string id) { using (NorthwindEntities northwind = new NorthwindEntities()) { Customer customerToDelete = northwind.Customers.Find(id); northwind.Customers.Remove(customerToDelete); northwind.SaveChanges(); } }
public static string GetCustomerNameById(string id) { using (NorthwindEntities northwind = new NorthwindEntities()) { var customer = northwind.Customers.Find(id); if (customer != null) { return customer.ContactName; } else { return string.Empty; } } }
public static void ModifyCustomer(string id, string name, string title, string phone, string fax, string address, string postalCode, string city, string region, string country, string company) { using (NorthwindEntities northwind = new NorthwindEntities()) { Customer customerToEdit = northwind.Customers.Find(id); customerToEdit.ContactName = name; customerToEdit.ContactTitle = title; customerToEdit.Phone = phone; customerToEdit.Fax = fax; customerToEdit.Address = address; customerToEdit.PostalCode = postalCode; customerToEdit.City = city; customerToEdit.Region = region; customerToEdit.Country = country; customerToEdit.CompanyName = company; northwind.Entry(customerToEdit).State = System.Data.EntityState.Modified; northwind.SaveChanges(); } }
public static void Main() { #region Problem 2 - Add, Modify and Delete NorthwindDAO.AddCustomer("ALABA", "Pesho Peshov", "Owner", "555-123123", "555-123124", "Dolno uino", "1000", "Sofia", "Sofia-grad", "Bulgaria", "McDonalds"); var customerName = NorthwindDAO.GetCustomerNameById("ALABA"); Console.WriteLine("Customer name will be blank if no entry found."); Console.WriteLine("Name for customer with id ALABA: {0}", customerName); NorthwindDAO.ModifyCustomer("ALABA", "Gosho Goshov", "Employee", "555-123123", "555-123124", "Gorno uino", "9999", "Pleven", "Plovdiv", "Bulgaria", "KFC"); customerName = NorthwindDAO.GetCustomerNameById("ALABA"); Console.WriteLine("Name for customer with id ALABA: {0}", customerName); NorthwindDAO.DeleteCustomer("ALABA"); customerName = NorthwindDAO.GetCustomerNameById("ALABA") ?? ""; Console.WriteLine("Name for customer with id ALABA: {0}", customerName); #endregion #region Problem 3 - Get by year and location var customerNames = NorthwindDAO.GetCustomersByYearAndLocation("Canada", 1997); foreach (var name in customerNames) { Console.WriteLine(name); } #endregion #region Problem 4 - Native SQL query var queryResult = NorthwindDAO.ExecuteQuery("SELECT c.ContactName FROM Customers c " + "INNER JOIN Orders o ON o.CustomerId = c.CustomerId " + "WHERE (YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1})", new object[] { 1997, "Canada" }).Distinct(); foreach (var result in queryResult) { Console.WriteLine(result); } #endregion #region Problem 5 - All sales by region and period var orders = NorthwindDAO.SalesByRegionAndDate("CA", new DateTime(1000, 1, 1), new DateTime(2000, 1, 1)); foreach (var order in orders) { Console.WriteLine(order); } #endregion #region Problem 6 - Twin database string createNorthwindCloneDBCommand = @"CREATE DATABASE NorthwindTwin ON PRIMARY (NAME = NorthwindTwin, FILENAME = 'D:\NorthwindTwin.mdf', SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) LOG ON (NAME = NorthwindTwinLog, FILENAME = 'D:\NorthwindTwin.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)"; SqlConnection dbConnectionForCreatingDB = new SqlConnection("Server=localhost;Database=master;Integrated Security=true"); dbConnectionForCreatingDB.Open(); using (dbConnectionForCreatingDB) { SqlCommand createDBCommand = new SqlCommand(createNorthwindCloneDBCommand, dbConnectionForCreatingDB); createDBCommand.ExecuteNonQuery(); } IObjectContextAdapter northwindContext = new NorthwindEntities(); string cloneNorthwindScript = northwindContext.ObjectContext.CreateDatabaseScript(); SqlConnection dbConnectionForCloningDB = new SqlConnection("Server=localhost;Database=NorthwindTwin;Integrated Security=true"); dbConnectionForCloningDB.Open(); using (dbConnectionForCloningDB) { SqlCommand cloneDBCommand = new SqlCommand(cloneNorthwindScript, dbConnectionForCloningDB); cloneDBCommand.ExecuteNonQuery(); } Console.WriteLine("Cloning done!"); #endregion #region Problem 7 - Two dbcontext - solved using Singleton pattern Console.WriteLine("Using double context..."); NorthwindDAO.DoubleContext(); Console.WriteLine("Done"); #endregion #region Problem 8 - Employee inheritance NorthwindDAO.Inheritance(); #endregion #region Problem 9 - NorthwindDAO.CreateOrder(); Console.WriteLine("Order added"); #endregion }
public static IEnumerable<string> GetCustomersByYearAndLocation(string country, int year) { using (NorthwindEntities northwind = new NorthwindEntities()) { var customers = northwind.Customers.Where(c => c.Orders.Where(o => o.OrderDate.Value.Year == year && o.ShipCountry == country).Any() ); IEnumerable<string> names = customers.Select(x => x.ContactName).ToList(); return names; } }
public static void CreateOrder() { NorthwindEntities northwind = new NorthwindEntities(); using (northwind) { var order = new Order() { CustomerID = "BOTTM", EmployeeID = 6, ShipCity = "Sofia", ShipCountry = "Bulgaria", ShippedDate = DateTime.Now, ShipPostalCode = "1000", ShipVia = 2 }; var orderDetail1 = new Order_Detail() { Order = order, ProductID = 23, Quantity = 10, UnitPrice = 15 }; var orderDetail2 = new Order_Detail() { Order = order, ProductID = 27, Quantity = 11, UnitPrice = 19 }; northwind.Orders.Add(order); northwind.Order_Details.Add(orderDetail1); northwind.Order_Details.Add(orderDetail2); northwind.SaveChanges(); } }
public static void Inheritance() { NorthwindEntities northwindEntities = new NorthwindEntities(); int employeeID = 1; Employee employee = northwindEntities.Employees.Find(employeeID); // Property added at Northwind.Data ExtendedEmployee file EntitySet<Territory> territories = employee.EntityTerritories; Console.WriteLine("All territories for employee with ID {0} are:", employeeID); foreach (var territory in territories) { Console.WriteLine(territory.TerritoryDescription); } }
public static void DoubleContext() { using (NorthwindEntities northwindEntities1 = new NorthwindEntities()) { using (NorthwindEntities northwindEntities2 = new NorthwindEntities()) { Customer customerByFirstDataContext = northwindEntities1.Customers.Find("CHOPS"); customerByFirstDataContext.Region = "SW"; Customer customerBySecondDataContext = northwindEntities2.Customers.Find("CHOPS"); customerBySecondDataContext.Region = "SSWW"; northwindEntities1.SaveChanges(); northwindEntities2.SaveChanges(); } } }
public static void CreateNewDatabase(NorthwindEntities northwind) { // TODO }
public static IEnumerable<string> SalesByRegionAndDate(string region, DateTime start, DateTime end) { using (NorthwindEntities northwind = new NorthwindEntities()) { var result = northwind.Orders.Where(o => o.ShipRegion == region && o.OrderDate.Value > start && o.OrderDate.Value < end).Select(x => "Contact name: " + x.Customer.ContactName + " Ship address: " + x.ShipAddress).ToList(); return result; } }
public static IEnumerable<string> ExecuteQuery(string query, object[] parameters) { using (NorthwindEntities northwind = new NorthwindEntities()) { var queryResult = northwind.Database.SqlQuery<string>(query, parameters).ToList(); return queryResult; } }