public static decimal? UseFindIncomesForSupplierInPeriod() { decimal? income; using (var dbContext = new NorthwindEntities()) { income = dbContext.uspTotalIncomesForSupplierInPeriod("Tokyo Traders", new DateTime(1994, 01, 01), new DateTime(2014, 09, 09)).First(); } return income; }
public static void GenerateTwinDb() { var northwindEntities = new NorthwindEntities(); string connectionString = northwindEntities.Database.Connection.ConnectionString; connectionString = connectionString.Replace("Northwind", "NorthwindTwin"); //Console.WriteLine(connectionString); DbContext context = new DbContext(connectionString); var dbScript = CreateDatabaseScript(northwindEntities); dbScript = RenameDb(dbScript); //Console.WriteLine(dbScript); context.Database.ExecuteSqlCommand(dbScript); }
public static void Process() { using(var firstContext = new NorthwindEntities()) using (var secondContext = new NorthwindEntities()) { var firstProdToModify = firstContext.Products.FirstOrDefault(pr => pr.ProductName == "Pavlova"); var secondProdToModify = secondContext.Products.FirstOrDefault(pr => pr.ProductName == "Pavlova"); firstProdToModify.ProductName = "Petrova"; secondProdToModify.ProductName = "Georgieva"; firstContext.SaveChanges(); secondContext.SaveChanges(); } // To ensure smooth operation Async Query & Save could be used. }
public static void ModifyCustomer(string customerId, CustomerProperty property, string newValue) { using (var northwindEntities = new NorthwindEntities()) { var customerToModify = FindCustomerById(northwindEntities, customerId); switch (property) { case CustomerProperty.CompanyName: customerToModify.CompanyName = newValue; break; case CustomerProperty.ContactName: customerToModify.ContactName = newValue; break; case CustomerProperty.ContactTitle: customerToModify.ContactTitle = newValue; break; case CustomerProperty.Address: customerToModify.Address = newValue; break; case CustomerProperty.City: customerToModify.City = newValue; break; case CustomerProperty.Region: customerToModify.Region = newValue; break; case CustomerProperty.PostalCode: customerToModify.PostalCode = newValue; break; case CustomerProperty.Country: customerToModify.Country = newValue; break; case CustomerProperty.Phone: customerToModify.Phone = newValue; break; case CustomerProperty.Fax: customerToModify.Fax = newValue; break; default: throw new ApplicationException(string.Format("No such property found: {0}", property.ToString())); } northwindEntities.SaveChanges(); } }
public static void CreateFindIncomesForSupplierInPeriod() { string storedProcedure = "CREATE PROCEDURE [dbo].[uspTotalIncomesForSupplierInPeriod] (@supplierName nvarchar(50), @startDate date, @endDate date) " + "AS " + "BEGIN " + "SELECT SUM(o.Freight) FROM [dbo].[Orders] o " + "INNER JOIN [dbo].[Order Details] od " + "ON o.OrderID = od.OrderID " + "INNER JOIN [dbo].[Products] p " + "ON od.ProductID = p.ProductID " + "INNER JOIN [dbo].[Suppliers] s " + "ON p.SupplierID = s.SupplierID " + "WHERE @supplierName = s.[CompanyName] " + "AND o.ShippedDate BETWEEN " + "CAST(@startDate AS datetime) AND CAST(@endDate AS datetime)" + "END;"; using (var dbContext = new NorthwindEntities()) { dbContext.Database.ExecuteSqlCommand(storedProcedure); dbContext.SaveChanges(); } }
/*Create a DAO class with static methods which provide functionality for inserting, modifying and deleting customers. Write a testing class.*/ public static string InsertCustomer( string companyName, string contactName = null, string contactTitle = null, string address = null, string city = null, string region = null, string postalCode = null, string country = null, string phone = null, string fax = null) { var northwindEntities = new NorthwindEntities(); int indexOfWordSeparator = companyName.IndexOf(' '); indexOfWordSeparator = indexOfWordSeparator > 0 ? indexOfWordSeparator + 1 : 4; string customerId = (companyName.Substring(0, 4) + companyName.Substring(indexOfWordSeparator, 1)).ToUpperInvariant(); var newCustomer = new Customer() { CustomerID = customerId, CompanyName = companyName, ContactName = contactName, ContactTitle = contactTitle, Address = address, City = city, Region = region, PostalCode = postalCode, Country = country, Phone = phone, Fax = fax, }; northwindEntities.Customers.Add(newCustomer); northwindEntities.SaveChanges(); return newCustomer.CustomerID; }
public static void NewOrder(string customerId, int employeeId, DateTime orderDate, int shipVia, string shipName, string shipCity) { // using (TransactionScope scope = new TransactionScope()) // { using (var dbContext = new NorthwindEntities()) { using (var tran = dbContext.Database.BeginTransaction()) { var newOrder = new Order(); newOrder.CustomerID = customerId; newOrder.EmployeeID = employeeId; newOrder.OrderDate = orderDate; newOrder.ShipVia = shipVia; newOrder.ShipName = shipName; newOrder.ShipCity = shipCity; dbContext.Orders.Add(newOrder); dbContext.SaveChanges(); tran.Commit(); } } //scope.Complete(); //} }
public static IEnumerable<Customer> GetCustomersToList() { var result = new List<Customer>(); using (var northwindEntities = new NorthwindEntities()) { result = northwindEntities.Customers.ToList(); } return result; }
/*Write a method that finds all the sales by specified region and period (start / end dates). */ public static IEnumerable<Order> SalesByRegionAndPeriod(string region, DateTime startDate, DateTime endDate) { var result = new List<Order>(); using (var northwindEntities = new NorthwindEntities()) { result = northwindEntities .Orders .Where( o => (o.ShippedDate.Value >= startDate && o.ShippedDate.Value <= endDate) && (o.ShipRegion == region) ).ToList(); } return result; }
/*Implement previous by using native SQL query and executing it through the DbContext.*/ public static IEnumerable<Customer> GetCustomersByOrderMadeIn1997SShippedToCanadaSqlQuery() { IEnumerable<Customer> result; using (var northwindEntities = new NorthwindEntities()) { string sqlQuery = "SELECT DISTINCT c.* FROM Customers c " + "INNER JOIN Orders o " + "ON c.CustomerID = o.CustomerID " + "WHERE (1997 = (DATEPART (year, o.OrderDate)))" + "AND (o.ShipCountry = 'Canada')"; result = northwindEntities.Customers.SqlQuery(sqlQuery).ToList(); } return result; }
/*Write a method that finds all customers who have orders made in 1997 and shipped to Canada.*/ public static IEnumerable<Customer> GetCustomersByOrderMadeIn1997SShippedToCanada() { var result = new List<Customer>(); using (var northwindEntities = new NorthwindEntities()) { result = northwindEntities.Customers.Where(c => c.Orders.Any(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada")).ToList(); } return result; }
private static Customer FindCustomerById(NorthwindEntities entities, string customerId) { Customer foundCustomer = entities.Customers.FirstOrDefault( c => c.CustomerID == customerId); return foundCustomer; }
public static void DeleteCustomer(string customerId) { using (var northwindEntities = new NorthwindEntities()) { var customerToDelete = FindCustomerById(northwindEntities, customerId); northwindEntities.Customers.Remove(customerToDelete); northwindEntities.SaveChanges(); } }