public static void Insert(string customerId, string companyName, string address = null, string city = null, string contactName = null, string contactTitle = null, string country = null, string fax = null, string phone = null, string postalCode = null, string region = null) { using (NorthwindEntities dbContext = new NorthwindEntities()) { Customer newCustomer = new Customer() { Address = address, City = city, CompanyName = companyName, ContactName = contactName, ContactTitle = contactTitle, Country = country, CustomerID = customerId, Fax = fax, Phone = phone, PostalCode = postalCode, Region = region }; dbContext.Customers.Add(newCustomer); dbContext.SaveChanges(); } }
/// <summary> /// Mains this instance. /// </summary> public static void Main() { Console.WriteLine("To get this working fix dbPath and db connection string if required!"); // Get DB structure. string createClone = string.Empty; using (var northwindDbContext = new NorthwindEntities()) { createClone = (northwindDbContext as IObjectContextAdapter).ObjectContext.CreateDatabaseScript(); } string createDBScript = "" + "IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NorthwindTwin')"+ "DROP DATABASE NorthwindTwin;" + "CREATE DATABASE NorthwindTwin ON PRIMARY " + "(NAME = NorthwindTwin, FILENAME = '" + dbPath + "NorthwindTwin.mdf', SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " + "LOG ON (NAME = NorthwindTwinLog, FILENAME = '" + dbPath + "NorthwindTwin.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%);"; using (var db = new SqlConnection("Server=.; Database=master; Integrated Security=true")) { // Create empty DB db.Open(); var initializeEmptyDB = new SqlCommand(createDBScript, db); initializeEmptyDB.ExecuteNonQuery(); db.ChangeDatabase("NorthwindTwin"); // Create twin structure. SqlCommand createTwin = new SqlCommand(createClone, db); createTwin.ExecuteNonQuery(); } Console.WriteLine("Cloning Done!"); }
public static int AddCustomer(Customer newCustomer) { using (var dataBase = new NorthwindEntities()) { dataBase.Customers.Add(newCustomer); return dataBase.SaveChanges(); } }
/// <summary> /// Finds all customers who have orders made in 1997 and shipped to Canada. /// </summary> /// <returns>The customers.</returns> public static IEnumerable AnotherFindCustomersWithOrdersFromCanadaIn1997() { using (var northwindDbContext = new NorthwindEntities()) { var customers = northwindDbContext.Orders.Include("Customer").Where(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada").GroupBy(o => o.Customer.ContactName).Select(c => c.Key).ToList(); return customers; } }
/// <summary> /// Finds all customers who have orders made in 1997 and shipped to Canada. /// </summary> /// <returns>The customers.</returns> public static IEnumerable FindCustomersWithOrdersFromCanadaIn1997() { using (var northwindDbContext = new NorthwindEntities()) { var customers = northwindDbContext.Orders.Where(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada").Join(northwindDbContext.Customers, o => o.CustomerID, c => c.CustomerID, (o,c) => c.ContactName).Distinct().ToList(); return customers; } }
/// <summary> /// Finds Total Income. /// </summary> /// <param name="supplierName">The supplier name.</param> /// <param name="startDate">Period start date.</param> /// <param name="endDate">Period end date.</param> /// <returns>The total income.</returns> private static decimal? FindTotalIncome(string supplierName, DateTime startDate, DateTime endDate) { using (var northwindDbContext = new NorthwindEntities()) { var totalIncome = northwindDbContext.usp_FindTotalIncome(supplierName, startDate, endDate).First(); return totalIncome; } }
public static int DeleteCustomer(string id) { using (var dataBase = new NorthwindEntities()) { Customer customer = dataBase.Customers.First(x => x.CustomerID == id); dataBase.Customers.Remove(customer); return dataBase.SaveChanges(); } }
/// <summary> /// Adds a customer to customers table. /// </summary> /// <param name="customer">The customer to be added.</param> public static void AddCustomer(Customer customer) { using (var northwindDbContext = new NorthwindEntities()) { northwindDbContext.Customers.Add(customer); northwindDbContext.SaveChanges(); Console.WriteLine("Customer successfully added!"); } }
//2. Create a DAO class with static methods which provide functionality //for inserting, modifying and deleting customers. Write a testing class. public static void Delete(string customerId) { using (NorthwindEntities dbContext = new NorthwindEntities()) { Customer customer = dbContext.Customers.Find(customerId); dbContext.Customers.Remove(customer); dbContext.SaveChanges(); } }
public static int UpdateContactNameTitle(Customer customer, string contactName, string contactTitle) { using (var northwindEntities = new NorthwindEntities()) { northwindEntities.Customers.Attach(customer); customer.CompanyName = contactName; customer.ContactTitle = contactTitle; return northwindEntities.SaveChanges(); } }
public static void FindSaleByDateRegion(string region, DateTime start, DateTime end) { using (var dataBase = new NorthwindEntities()) { foreach (var item in dataBase.Orders.Where(x => x.ShippedDate >= start && x.ShippedDate <= end && x.ShipRegion == region)) { Console.WriteLine("{0} {1} {2}", item.OrderID, item.ShippedDate.GetValueOrDefault().ToString("dd/MM/yyyy"), item.ShipCity); } } }
private static void Main(string[] args) { using (var northwindDbContext = new NorthwindEntities()) { foreach (var territory in northwindDbContext.Employees.First().TerritoriesSet) { Console.WriteLine(territory); } } }
static void Main(string[] args) { using(var context = new NorthwindEntities()) { var result = context.usp_FindTotalIncome(new DateTime(1994, 1, 1), new DateTime(2000, 12, 31), "Exotic Liquids").First(); Console.WriteLine("All Income are {0}",result); } }
public static void Modify(string customerId, string companyName /*= null, string address = null, string city = null, string contactName = null, string contactTitle = null, string country = null, string fax = null, string phone = null, string postalCode = null, string region = null*/) { using (NorthwindEntities dbContext = new NorthwindEntities()) { Customer customer = dbContext.Customers.Find(customerId); customer.CompanyName = companyName; dbContext.SaveChanges(); } }
public static void FindCustomerByCountryOrderYear(string country, int year) { using (var dataBase = new NorthwindEntities()) { foreach (var item in dataBase.Orders.Join(dataBase.Customers, (o => o.CustomerID), (c => c.CustomerID), (o, c) => new { Order = o.OrderDate, Country = o.ShipCountry, Customer = c.CompanyName }).Where(x => x.Country == country && x.Order.Value.Year == year)) { Console.WriteLine("{0} {1} {2}", item.Customer, item.Order, item.Country); } } }
/* 02. Create a DAO class with static methods which provide functionality for inserting, modifying and deleting customers. Write a testing class.*/ /// <summary> /// Deletes a customer identified by CustomerID. /// </summary> /// <param name="customer">The customer.</param> public static void DeleteCustomer(Customer customer) { using (var northwindDbContext = new NorthwindEntities()) { var customerForDelete = northwindDbContext.Customers.First(c => c.CustomerID == customer.CustomerID); northwindDbContext.Customers.Remove(customerForDelete); northwindDbContext.SaveChanges(); Console.WriteLine("Customer successfully deleted!"); } }
public static IEnumerable<string> FindAllCustomersInCanadaWithOrdersIn1997() { //3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada. using (NorthwindEntities dbContext = new NorthwindEntities()) { var customers = dbContext.Orders .Where(o => o.OrderDate.Value.Year == 1997) .Where(o => o.ShipCountry == "Canada") .Join(dbContext.Customers, (o => o.CustomerID), (c => c.CustomerID), (o, c) => c.ContactName); return customers.ToList(); } }
/// <summary> /// Finds all customers who have orders made in 1997 and shipped to Canada using plain SQL. /// </summary> /// <returns>The customers.</returns> public static IEnumerable FindCustomersWithOrdersFromCanadaIn1997SQL() { using (var northwindDbContext = new NorthwindEntities()) { var query = "SELECT "+ "DISTINCT(cust.ContactName) "+ "FROM "+ "dbo.Orders ord "+ "INNER JOIN dbo.Customers cust "+ "ON cust.CustomerID = ord.CustomerID "+ "WHERE DATEPART(YEAR, ord.OrderDate) = 1997 "+ "AND ord.ShipCountry = 'Canada'"; var customers = northwindDbContext.Database.SqlQuery<string>(query).ToList(); return customers; } }
public static void FindCustomerByCountryYearSql(string country, int year) { using (var dataBase = new NorthwindEntities()) { //"SELECT CompanyName +' '+ CONVERT(VARCHAR, OrderDate, 104) +' ' + ShipCity FROM Orders " string query = "SELECT CompanyName , OrderDate, ShipCity FROM Orders " + "join Customers " + "on Orders.CustomerID = Customers.CustomerID " + "WHERE year(OrderDate) = {0} and ShipCountry = {1}"; object[] parameters = { year, country }; var table = dataBase.Database.SqlQuery<CompanyAndDate>(query, parameters); //var table = dataBase.Database.SqlQuery<string>(query, parameters); foreach (var info in table) { Console.WriteLine("{0,-12} {1, -12} {2}", info.ShipCity, info.OrderDate.ToString("dd/MM/yyyy"), info.CompanyName); } } }
public static int AddOrder(Order newOrder) { using (var dataBase = new NorthwindEntities()) { var customer = dataBase.Customers.First(c => c.CustomerID == newOrder.CustomerID); if (customer != null) { newOrder.ShipAddress = customer.Address; newOrder.ShipCity = customer.City; newOrder.ShipPostalCode = customer.PostalCode; newOrder.ShipCountry = customer.Country; } dataBase.Orders.Add(newOrder); dataBase.Orders.Add(newOrder); dataBase.Orders.Add(newOrder); return dataBase.SaveChanges(); } }
/* 07. Try to open two different data contexts and perform concurrent changes on the same records. What will happen at SaveChanges()? How to deal with it?*/ // EF executes everything in transactions by default. We don`t need to worry about it. /// <summary> /// Mains this instance. /// </summary> public static void Main() { using (var northwindDbContext = new NorthwindEntities()) { using (var secondNorthwindDbContext = new NorthwindEntities()) { var order = northwindDbContext.Orders.Find(10248); var sameOrder = secondNorthwindDbContext.Orders.Find(10248); // Mark for delete northwindDbContext.Orders.Remove(order); // Update some stuff sameOrder.ShipCountry = "Bulgaria"; // Make update try { secondNorthwindDbContext.SaveChanges(); Console.WriteLine("Updating complete!"); } catch (Exception) { Console.WriteLine("Updating failed!"); } // Delete -> Produces an exception because of data integrity. try { northwindDbContext.SaveChanges(); Console.WriteLine("Deleting complete!"); } catch (Exception) { Console.WriteLine("Delete cannot be executed!"); } } } Console.WriteLine("Done!"); }
/// <summary> /// Adds an order multiple times to the db. Transaction demo. /// </summary> /// <param name="order">The order.</param> private static void AddOrderMultipleTimes(Order order) { using (var northwindDbContext = new NorthwindEntities()) { northwindDbContext.Orders.Add(order); northwindDbContext.Orders.Add(order); northwindDbContext.Orders.Add(order); //// Uncomment to see transaction power! // order.CustomerID = "PESHO_ZLIA"; northwindDbContext.Orders.Add(order); northwindDbContext.Orders.Add(order); try { northwindDbContext.SaveChanges(); } catch (Exception) { Console.WriteLine("Error! Orders not confirmed!"); } } }
public static IEnumerable<string> FindAllCustomersInCanadaWithOrdersIn1997Native() { //4. Implement previous by using native SQL query and executing it through the DbContext. using (NorthwindEntities dbContext = new NorthwindEntities()) { var customers = dbContext.Database.SqlQuery<string>( @"SELECT c.ContactName FROM Customers AS c JOIN Orders AS o ON c.CustomerID = o.CustomerID WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry ='Canada'"); return customers.ToList(); } }
static decimal? GetIncome(string supplierName, DateTime startDate, DateTime endDate) { //10. Create a stored procedures in the Northwind database for finding the total incomes //for given supplier name and period (start date, end date). Implement a C# method that //calls the stored procedure and returns the retuned record set. using (NorthwindEntities dbContext = new NorthwindEntities()) { decimal? totalncome = dbContext.usp_GetTotalIncome(supplierName, startDate, endDate).ToList().FirstOrDefault(); return totalncome; } }
private static void PlaceNewOrder( int orderId, string customerId = null, int? employeeId = null, DateTime? orderDate = null, DateTime? requireDate = null, DateTime? shippedDate = null, int? shipVia = null, decimal? freight = null, string shipName = null, string shipAddress = null, string shipCity = null, string shipRegion = null, string shipPostalCode = null, string shipCountry = null) { //9. Create a method that places a new order in the Northwind database. The order //should contain several order items. Use transaction to ensure the data consistency. using (NorthwindEntities dbContext = new NorthwindEntities()) { Order newOrder = new Order() { CustomerID = customerId, EmployeeID = employeeId, Freight = freight, OrderDate = orderDate, OrderID = orderId, RequiredDate = requireDate, ShipAddress = shipAddress, ShipCity = shipCity, ShipCountry = shipCountry, ShipName = shipName, ShippedDate = shippedDate, ShipPostalCode = shipPostalCode, ShipRegion = shipRegion, ShipVia = shipVia, }; dbContext.Orders.Add(newOrder); dbContext.SaveChanges(); } }
private static void MakeConcurencyChanges() { //7. Try to open two different data contexts and perform concurrent changes //on the same records. What will happen at SaveChanges()? How to deal with it? NorthwindEntities dbContext1 = new NorthwindEntities(); Employee employee1 = dbContext1.Employees.Find(10); //employee1.City = "Burgas"; dbContext1.Employees.Remove(employee1); //dbContext1.SaveChanges(); NorthwindEntities dbContext2 = new NorthwindEntities(); Employee employee2 = dbContext2.Employees.Find(10); employee2.City = "Varna"; dbContext1.SaveChanges(); dbContext2.SaveChanges(); dbContext1.Dispose(); dbContext2.Dispose(); }
/// <summary> /// Finds all customers who have orders made in 1997 and shipped to Canada using plain SQL. /// </summary> /// <returns>The customers.</returns> public static IEnumerable GetOrdersByRegionAndPeriod(string region, DateTime startDate, DateTime endDate) { using (var northwindDbContext = new NorthwindEntities()) { var orders = northwindDbContext.Orders.Where(o => o.ShipRegion == region && o.ShippedDate > startDate && o.ShippedDate < endDate).ToList(); return orders; } }
public static IEnumerable<object> FindAllSalesIn(string region, DateTime startDate, DateTime endDate) { //5. Write a method that finds all the sales by specified region and period (start / end dates). using (NorthwindEntities dbContext = new NorthwindEntities()) { var orders = dbContext.Orders .Where(o => o.ShippedDate > startDate) .Where(o => o.ShippedDate < endDate) .Where(o => o.ShipRegion.ToLower() == region.ToLower()) .Select(o => new { o.ShippedDate, o.ShipName, o.ShipRegion }).ToList(); return orders.ToList(); } }
public static void CreateNorthwindTwin() { //6. Create a database called NorthwindTwin with the same structure as Northwind //using the features from DbContext. Find for the API for schema generation in MSDN or in Google. IObjectContextAdapter dbContextAdapter = new NorthwindEntities(); string dbScript = "DROP DATABASE IF EXISTS NorthwindTwin" + "CREATE DATABASE NorthwindTwin ON PRIMARY " + "(NAME = NorthwindTwin, " + "FILENAME = 'C:\\NorthwindTwin.mdf', " + "SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " + "LOG ON (NAME = NorthwindTwinLog, " + "FILENAME = 'C:\\NorthwindTwin.ldf', " + "SIZE = 1MB, " + "MAXSIZE = 5MB, " + "FILEGROWTH = 10%)" + dbContextAdapter.ObjectContext.CreateDatabaseScript().Replace("Northwind", "NorthwindTwin"); string connectionString = "Server=LOCALHOST;Database=master;Integrated Security=true"; SqlConnection dbConForCreatingDB = new SqlConnection(connectionString); dbConForCreatingDB.Open(); using (dbConForCreatingDB) { SqlCommand createDB = new SqlCommand(dbScript, dbConForCreatingDB); createDB.ExecuteNonQuery(); } }
/// <summary> /// Updates a customer identified by CustomerID. /// </summary> /// <param name="customer">The customer.</param> public static void UpdateCustomer(Customer customer) { using (var northwindDbContext = new NorthwindEntities()) { var customerForUpdate = northwindDbContext.Customers.First(c => c.CustomerID == customer.CustomerID); customerForUpdate.CompanyName = customer.CompanyName ?? customerForUpdate.CompanyName; customerForUpdate.ContactName = customer.ContactName ?? customerForUpdate.ContactName; customerForUpdate.ContactTitle = customer.ContactTitle ?? customerForUpdate.ContactTitle; customerForUpdate.Address = customer.Address ?? customerForUpdate.Address; customerForUpdate.City = customer.City ?? customerForUpdate.City; customerForUpdate.Region = customer.Region ?? customerForUpdate.Region; customerForUpdate.PostalCode = customer.PostalCode ?? customerForUpdate.PostalCode; customerForUpdate.Country = customer.Country ?? customerForUpdate.Country; customerForUpdate.Phone = customer.Phone ?? customerForUpdate.Phone; customerForUpdate.Fax = customer.Fax ?? customerForUpdate.Fax; northwindDbContext.SaveChanges(); Console.WriteLine("The customer is Updated!"); } }