public static string AddCustomer(NorthwindEntities northwind, Customer customer) { northwind.Customers.Add(customer); northwind.SaveChanges(); return "Customer added"; }
/// <summary> /// Problem solved by only using 1 db connection or introducing transactions isolation levels /// </summary> public static void Main() { using (var db1 = new NorthwindEntities()) { using (var db2 = new NorthwindEntities()) { Console.WriteLine("First connection altering"); Console.WriteLine("Original -> " + db1.Employees.First().City); db1.Employees.First().City = "Changed city"; Console.WriteLine("Changed -> " + db1.Employees.First().City); Console.WriteLine("Second connection altering"); Console.WriteLine("Original -> " + db2.Employees.First().City); db2.Employees.First().City = "Altered city"; Console.WriteLine("Changed -> " + db2.Employees.First().City); db1.SaveChanges(); db2.SaveChanges(); } } using (var db3 = new NorthwindEntities()) { Console.WriteLine("Final connection altering"); Console.WriteLine(db3.Employees.First().City); } }
public static void Main() { IObjectContextAdapter dataBase = new NorthwindEntities(); string cloneNorthwind = dataBase.ObjectContext.CreateDatabaseScript(); SqlConnection connection = new SqlConnection("Server=.\\; " + "Database=master; Integrated Security=true"); connection.Open(); using (connection) { SqlCommand createDatabase = new SqlCommand( "USE master; " + "CREATE DATABASE NorthwindTwin; " + "SELECT name, size, size*1.0/128 AS [Size in MBs] " + "FROM sys.master_files " + "WHERE name = N'NorthwindTwin'; ", connection); createDatabase.ExecuteNonQuery(); string changeDB = "USE NorthwindTwin; "; SqlCommand useNewDatabase = new SqlCommand(changeDB, connection); useNewDatabase.ExecuteNonQuery(); SqlCommand cloneDatabase = new SqlCommand(cloneNorthwind, connection); cloneDatabase.ExecuteNonQuery(); } }
public static void Main() { using (var northwind1 = new NorthwindEntities()) { TransactionOptions options = new TransactionOptions(); options.IsolationLevel = IsolationLevel.Serializable; using (var scope = new TransactionScope(TransactionScopeOption.Required, options)) { using (var northwind2 = new NorthwindEntities()) { Customer customer = northwind1.Customers.FirstOrDefault(); customer.Country = "NewCountry"; Customer customer2 = northwind2.Customers.FirstOrDefault(); northwind1.SaveChanges(); Console.WriteLine("First connection country ->" + customer.Country); Console.WriteLine("Second connection country ->" + customer2.Country); Console.WriteLine("The second connection works with the old data - dirty read."); scope.Complete(); } } } }
static void Main() { using (NorthwindEntities db = new NorthwindEntities()) { int year = 1997; string country = "Canada"; object[] parameters = {year, country}; string queryString = @"SELECT DISTINCT cust.ContactName AS [ContactName], cust.CompanyName AS [CompanyName] FROM Orders AS ord INNER JOIN Customers AS cust ON ord.CustomerID = cust.CustomerID WHERE YEAR(ord.OrderDate) = {0} AND cust.Country = {1}"; Type outputType = typeof(Result); var customers = db.Database.SqlQuery(outputType, queryString, parameters); foreach (Result item in customers) { Console.WriteLine("{0,-30} | {1,-30}", item.CompanyName, item.ContactName); } } }
public static void Main() { using (var northwind = new NorthwindEntities()) { Customer customer = new Customer(); customer.Address = "155, Madison Avenue"; customer.City = "New York"; customer.CompanyName = "Company"; customer.Country = "United States"; customer.CustomerID = "US123"; customer.ContactName = "James Peterson"; Console.WriteLine(InsertModifyDeleteCustomers.AddCustomer(northwind, customer)); Customer anotherCustomer = new Customer(); anotherCustomer.CustomerID = "US123"; anotherCustomer.Address = "253, Madison Avenue"; anotherCustomer.City = "New York"; anotherCustomer.CompanyName = "Another company"; anotherCustomer.Country = "United States"; anotherCustomer.ContactName = "Peter Jameson"; Console.WriteLine(InsertModifyDeleteCustomers.ModifyCustomerById(northwind, "COMPA", anotherCustomer)); Console.WriteLine(InsertModifyDeleteCustomers.DeleteCustomer(northwind, anotherCustomer)); } }
public static string DeleteCustomer(NorthwindEntities northwind, Customer newCustomer) { var selectedCustomer = northwind.Customers.Where(c => c.CustomerID == newCustomer.CustomerID).FirstOrDefault(); northwind.Customers.Remove(selectedCustomer); northwind.SaveChanges(); return "Customer deleted"; }
/// <summary> /// EF works with transactions by default - calling SaveChanges() starts a transaction by default. /// However we can use transactions to unify many calling of SaveChanges() as one and if one fails - all fail. /// </summary> public static void Main() { using (var db = new NorthwindEntities()) { using (var transaction = db.Database.BeginTransaction()) { var order = new Order() { CustomerID = "WELLI", EmployeeID = 4 }; db.Orders.Add(order); var orderDetails1 = new Order_Detail() { OrderID = order.OrderID, ProductID = 5, UnitPrice = 12.34m, Quantity = 100, Discount = 0.2f }; var orderDetails2 = new Order_Detail() { OrderID = order.OrderID, ProductID = 3, UnitPrice = 12.34m, Quantity = 100, Discount = 0.2f }; var orderDetails3 = new Order_Detail() { OrderID = order.OrderID, ProductID = 4, UnitPrice = 12.34m, Quantity = 100, Discount = 0.2f }; db.Order_Details.AddRange(new[] { orderDetails1, orderDetails2, orderDetails3 }); try { db.SaveChanges(); } catch (System.Exception ex) { System.Console.WriteLine(ex); transaction.Rollback(); } transaction.Commit(); } } }
public static void Main() { using (var northwind = new NorthwindEntities()) { var a = northwind.Orders.Select(o => o.ShipRegion).ToList(); List<string> sales = FindSales(northwind, 1997, "RJ"); Console.WriteLine(string.Join(Environment.NewLine, sales)); } }
public static void Main() { using (var northwind = new NorthwindEntities()) { var orders = northwind.Orders.Where(o => o.ShipCountry == "Canada" && o.RequiredDate.Value.Year == 1997).ToList(); foreach (var order in orders) { Console.WriteLine("{0} | {1} | {2} | {3}", order.CustomerID, order.Freight, order.ShipAddress, order.ShipRegion); } } }
public static void Main() { using (var northwind = new NorthwindEntities()) { // To see the TerritoriesSet property, look in the model for the ExtendedEmployee class EntitySet<Territory> territories = northwind.Employees.OrderBy(t => t.EmployeeID).Skip(1).FirstOrDefault().TerritoriesSet; Console.WriteLine("Territory IDs:"); Console.WriteLine(string.Join(Environment.NewLine, territories.Select(t => t.TerritoryDescription))); } }
public static int ModifyCustomer(string contactName, string companyName) { using (NorthwindEntities db = new NorthwindEntities()) { string id = GetCustomerId(companyName); var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault(); if (customer == null) throw new ArgumentException("The record you are trying to modify doesn't exist!!!"); customer.CompanyName = companyName; customer.ContactName = contactName; return db.SaveChanges(); } }
public static List<string> FindSales(NorthwindEntities northwind, int year, string region) { List<string> sales = northwind.Orders.Join(northwind.Customers, o => o.CustomerID, c => c.CustomerID, (o, c) => new { CompanyName = c.CompanyName, OrderDate = o.OrderDate, ShipCountry = o.ShipCountry, ShipRegion = o.ShipRegion }).Where(x => x.ShipRegion == region && year.CompareTo(x.OrderDate.Value.Year) == 0).Select(y => y.CompanyName). ToList(); return sales; }
private static void Main(string[] args) { using (NorthwindEntities dbContext = new NorthwindEntities()) { IEnumerable<Employee> employees = (from empl in dbContext.Employees select empl); foreach (var employee in employees) { Console.WriteLine("{0} {1} {2}", employee.EmployeeID, employee.FirstName, employee.LastName ); foreach (var teritory in employee.Territories) { Console.WriteLine("{0} | {1}", teritory.TerritoryID, teritory.TerritoryDescription); } } } }
public static int DeleteCustomer(string companyName) { using (NorthwindEntities db = new NorthwindEntities()) { string id = GetCustomerId(companyName); if (IsCustomerExists(db, id)) { Customer customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault(); db.Customers.Remove(customer); return db.SaveChanges(); } else { return 0; } } }
//TODO: 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. static void Main() { IObjectContextAdapter context = new NorthwindEntities(); string cloneNorthwind = context.ObjectContext.CreateDatabaseScript(); string createNorthwindCloneDB = "CREATE DATABASE NorthwindTwin ON PRIMARY " + "(NAME = NorthwindTwin, " + "FILENAME = 'D:\\NorthwindTwin.mdf', " + "SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " + "LOG ON (NAME = NorthwindTwin_Log, " + "FILENAME = 'D:\\NorthwindTwinLog.ldf', " + "SIZE = 5MB, " + "MAXSIZE = 10MB, " + "FILEGROWTH = 10%)"; SqlConnection dbConForCreatingDB = new SqlConnection( @"Data Source=S_MANHATTAN; Persist Security Info=True; User ID=Manhattan; Password=123; Initial Catalog=master"); dbConForCreatingDB.Open(); using (dbConForCreatingDB) { SqlCommand createDB = new SqlCommand(createNorthwindCloneDB, dbConForCreatingDB); createDB.ExecuteNonQuery(); } SqlConnection dbConForCloning = new SqlConnection( @"Data Source=S_MANHATTAN; Persist Security Info=True; User ID=Manhattan; Password=123; Initial Catalog=NorthwindTwin"); dbConForCloning.Open(); using (dbConForCloning) { SqlCommand cloneDB = new SqlCommand(cloneNorthwind, dbConForCloning); cloneDB.ExecuteNonQuery(); } }
public static void Main() { using (var northwind = new NorthwindEntities()) { string command = @"SELECT c.ContactName from Customers c " + "INNER JOIN Orders o ON o.CustomerID = c.CustomerID " + "WHERE (YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1});"; object[] parameters = { 1997, "Canada" }; var sqlQueryResult = northwind.Database.SqlQuery<string>(command, parameters); foreach (var order in sqlQueryResult) { Console.WriteLine(order); } } }
// TODO: Write a method that finds all customers who have orders made in 1997 and shipped to Canada. static void Main() { using (NorthwindEntities db = new NorthwindEntities()) { DateTime dateStart = new DateTime(1997, 1, 1); DateTime dateEnd = new DateTime(1997, 12, 31); var result = (from order in db.Orders join customer in db.Customers on order.CustomerID equals customer.CustomerID where (order.OrderDate >= dateStart && order.OrderDate <= dateEnd) && (customer.Country == "Canada") select customer).Distinct(); foreach (var customer in result) { Console.WriteLine("{0,-30} | {1,-30}",customer.CompanyName, customer.ContactName); } } }
protected void Page_Load(object sender, EventArgs e) { NorthwindModel.NorthwindEntities objContext = new NorthwindModel.NorthwindEntities(); //var result = from item in objContext.Categories // where item.CategoryID == 65985 // select item; //outer join var result = from cat in objContext.Categories join prod in objContext.Products on cat.CategoryID equals prod.CategoryID into groupedItems //group cat by cat.CategoryID into groupedItems select new { CategoryID = cat.CategoryID }; GridView1.DataSource = result; GridView1.DataBind(); Label1.Text = ((ObjectQuery)result).ToTraceString(); }
// TODO: Write a method that finds all the sales by specified region and period (start / end dates). static void Main() { using(NorthwindEntities db = new NorthwindEntities()) { DateTime startDate = new DateTime(1997,01,01); DateTime endDate = new DateTime(2001,12,31); string region = "ID"; var orders = from ord in db.Orders where ord.OrderDate >= startDate && ord.OrderDate <= endDate && ord.ShipRegion == region select new { ord.OrderDate, ord.ShipRegion, ord.OrderID }; foreach (var ord in orders) { Console.WriteLine("{0,-30} | {1,-10} | {2,-20}", ord.OrderDate, ord.ShipRegion, ord.OrderID); } } }
public static void Main() { using (var northwind = new NorthwindEntities()) { using (var scope = new TransactionScope()) { for (int i = 0; i < 10; i++) { northwind.Orders.Add(new Order() { ShipAddress = "455, Metropolitan Avenue" }); } northwind.SaveChanges(); scope.Complete(); } Console.WriteLine("Number of added orders: {0}", northwind.Orders.Where(o => o.ShipAddress == "455, Metropolitan Avenue").Count()); } }
public static void Main() { using (var db = new NorthwindEntities()) { try { Console.WriteLine("Creating stored procedure"); db.Database.ExecuteSqlCommand(GetStoreProcedureCreationQuery(), null); } catch (Exception) { Console.WriteLine("Store procedure already exists"); } var income = CallGetSupplierIncomeProcedure(db, "Pavlova, Ltd.", new DateTime(1996, 1, 1), new DateTime(1999, 1, 1)); Console.WriteLine(income); } }
public static int InsertCustomer(string contactName, string companyName) { using (NorthwindEntities db = new NorthwindEntities()) { Customer customer = new Customer() { ContactName = contactName, CompanyName = companyName, CustomerID = GetCustomerId(companyName), }; if (IsCustomerExists(db, GetCustomerId(companyName))) { throw new ArgumentException("Customer already exist!!!"); } db.Customers.Add(customer); return db.SaveChanges(); } }
public static string ModifyCustomerById(NorthwindEntities northwind, string customerId, Customer newCustomer) { var selectedCustomer = northwind.Customers.Where(c => c.CustomerID == customerId).FirstOrDefault(); selectedCustomer.Address = newCustomer.Address; selectedCustomer.City = newCustomer.City; selectedCustomer.CompanyName = newCustomer.CompanyName; selectedCustomer.ContactName = newCustomer.ContactName; selectedCustomer.ContactTitle = newCustomer.ContactTitle; selectedCustomer.Country = newCustomer.Country; selectedCustomer.CustomerDemographics = newCustomer.CustomerDemographics; selectedCustomer.Fax = newCustomer.Fax; selectedCustomer.Orders = newCustomer.Orders; selectedCustomer.Phone = newCustomer.Phone; selectedCustomer.PostalCode = newCustomer.PostalCode; selectedCustomer.Region = newCustomer.Region; northwind.SaveChanges(); return "Customer modified"; }
//TODO: 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? static void Main() { string companyName = "Concurency Test"; string contactName = "Concurent1"; string contactName2 = "Concurent2"; using (NorthwindEntities db = new NorthwindEntities()) { using(NorthwindEntities db2 = new NorthwindEntities()) { string id = GetCustomerId(companyName); var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault(); var customer2 = db2.Customers.Where(x => x.CustomerID == id).FirstOrDefault(); if (customer == null) throw new ArgumentException("The record you are trying to modify doesn't exist!!!"); customer.CompanyName = companyName; customer.ContactName = contactName; customer2.ContactName= contactName2; db.SaveChanges(); db2.SaveChanges(); } } }
private static void CreateOrder(Order newOrder, int numberOfRepeatsUntillSuccess) { bool isTransactionSucceed = false; using (NorthwindEntities dbContext = new NorthwindEntities()) { using (TransactionScope transaction = new TransactionScope()) { for (int i = 0; i < numberOfRepeatsUntillSuccess; i++) { try { dbContext.Orders.Add(newOrder); dbContext.SaveChanges(); transaction.Complete(); isTransactionSucceed = true; break; } catch (UpdateException ex) { if (i == numberOfRepeatsUntillSuccess - 1) { throw new InvalidOperationException("Cannot complete order creation", ex); } } } if (isTransactionSucceed) { // Reset the context since the operation succeeded. Console.WriteLine("Transaction sompited"); } else { Console.WriteLine("The operation could not be completed in " + numberOfRepeatsUntillSuccess + " tries."); } } } }
public static void Main() { using (var northwind = new NorthwindEntities()) { // This procedure is saved in the database and the model now contains it //northwind.Database.ExecuteSqlCommand( // "CREATE PROCEDURE usp_FindTotalIncome (@startDate INT, @endDate INT, @companyName NVARCHAR(100)) " + // "AS " + // "SELECT SUM(od.Quantity * od.UnitPrice) AS [Income] " + // "FROM Suppliers s JOIN Products p " + // "ON s.SupplierID = p.SupplierID " + // "JOIN [Order Details] od " + // "ON od.ProductID = p.ProductID " + // "JOIN Orders o " + // "ON od.OrderID = o.OrderID " + // "WHERE s.CompanyName = @companyName AND (YEAR(o.OrderDate) >= @startDate AND YEAR(o.OrderDate) <= @endDate)"); //northwind.SaveChanges(); var income = northwind.usp_FindTotalIncome(1997, 1998, "Exotic Liquids"); Console.WriteLine("Total income: {0:F2}", income.FirstOrDefault()); } }
public void RefineCombinedSpecifications() { Specification<Customer> german_customer_spec = new Specification<Customer>(c => c.Country == "Germany"); Specification<Customer> us_customer_spec = new Specification<Customer>(c => c.Country == "Usa"); Specification<Product> ords_with_discontinued_prods = new Specification<Product>(p => p is DiscontinuedProduct); var comb_country_spec = (german_customer_spec | us_customer_spec); using (var ctx = new NorthwindEntities()) { var discontinued_products_on_order = (ObjectQuery<Product>) (from p in ctx.Products .Where(ords_with_discontinued_prods.EvalPredicate) from od in ctx.OrderDetails from o in ctx.Orders from c in ctx.Customers .Where(comb_country_spec.EvalPredicate) where od.ProductID == p.ProductID && o.OrderID == od.OrderID && c.CustomerID == o.Customer.CustomerID select p).Distinct(); Console.WriteLine(discontinued_products_on_order.ToTraceString()); foreach (var product in discontinued_products_on_order) { Console.WriteLine("The product ID is {0} with a name of {1}", product.ProductID, product.ProductName); } } }
protected void serverModeDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e) { NorthwindModel.NorthwindEntities dataContext = new NorthwindModel.NorthwindEntities(); e.QueryableSource = dataContext.Products; e.KeyExpression = "ProductID"; }
public void CombinedOrSpecExample() { Specification<Order> country_customer_spec = new Specification<Order> (o => o.Customer.Country == "Germany" || o.Customer.Country == "Usa"); Specification<Order> german_customer_spec = new Specification<Order> (o => o.Customer.Country == "Germany"); Specification<Order> us_customer_spec = new Specification<Order> (o => o.Customer.Country == "Usa"); Specification<Order> orderid_spec = new Specification<Order>(o => o.OrderID != 10279); Specification<Order> ords_with_discontinued_prods = new Specification<Order>(o => o.Order_Details.All (od => od.Product is DiscontinuedProduct)); var orSpec = ords_with_discontinued_prods & orderid_spec & (german_customer_spec | us_customer_spec); using (var ctx = new NorthwindEntities()) { //note the eager loading here //and still further down we have //to lazy load the same data //as eager loading has not worked //as expected. var discontinued_products_on_order = (ObjectQuery<Order_Detail>) from o in ctx.Orders .Include("Order_Details") .Include("Customer") .Where(orSpec.EvalPredicate) from od in ctx.OrderDetails where od.OrderID == o.OrderID select od; Console.WriteLine(discontinued_products_on_order.ToTraceString()); foreach (var ord_detail in discontinued_products_on_order) { if (!ord_detail.OrderReference.IsLoaded) { ord_detail.OrderReference.Load(); } if (!ord_detail.ProductReference.IsLoaded) { ord_detail.ProductReference.Load(); } if (!ord_detail.Order.CustomerReference.IsLoaded) { ord_detail.Order.CustomerReference.Load(); } Console.WriteLine("The OrderID: {0} the product name: {1} the customers location {2}", ord_detail.OrderID, ord_detail.Product.ProductName, ord_detail.Order.Customer.Country); } Assert.IsTrue(discontinued_products_on_order .Count(p => p.Order.Customer.Country != "Germany" && p.Order.Customer.Country != "USA") == 0); } }
private static bool IsCustomerExists(NorthwindEntities db, string customerId) { bool alreadyInDB = db.Customers.Where(customer => customer.CustomerID == customerId).Any(); return alreadyInDB; }
public void SimpleSpecificationExample() { Specification<Order> order_spec = new Specification<Order>(o => o.Order_Details.All (od => od.Product is DiscontinuedProduct)); using (var ctx = new NorthwindEntities()) { ObjectQuery<Order> orders = (ObjectQuery<Order>) from o in ctx.Orders.Include("Customer") .Include("Order_Details") .Include("Order_Details.Product") .Where(order_spec.EvalPredicate) select o; Console.WriteLine(orders.ToTraceString()); foreach (var order in orders) { if (order.Order_Details.Count() > 0) { var sumup = order.Order_Details.Sum (od => od.Quantity * od.UnitPrice); Console.WriteLine("The sum of orders for order ID: " + "{0} is {1} and Country is {2}", order.OrderID.ToString(), sumup.ToString(), order.Customer.Country); } } //test if all the products retrieved are indeed discontinued var discontinued = orders.Count(o => o.Order_Details.Count (od => od.Product as DiscontinuedProduct == null) > 1); Assert.AreEqual(discontinued, 0); } }