public static Customer GetCustomerByID(string customerID)
 {
     using (var db = new NorthwindEntities())
     {
         return db.Customers.FirstOrDefault(c => c.CustomerID == customerID);
     }
 }
 private static void PrintWhatSecondUserSees(NorthwindEntities secondConnection)
 {
     Console.WriteLine("User2 see: {0}\n", secondConnection.Employees.First().FirstName);
     var firstEmployee2 = secondConnection.Employees.First();
     firstEmployee2.FirstName = "2";
     Console.WriteLine("User2 changes the name with new value: {0}\n", secondConnection.Employees.First().FirstName);
 }
 private static void PrintActualResult()
 {
     using (var northwindEntities = new NorthwindEntities())
     {
         Console.WriteLine("Actual result: {0}\n", northwindEntities.Employees.First().FirstName);
     }
 }
 private static void PrintWhatFirstUserSees(NorthwindEntities firstConnection)
 {
     Console.WriteLine("\rUser1 see: {0}\n", firstConnection.Employees.First().FirstName);
     var firstEmployee1 = firstConnection.Employees.First();
     firstEmployee1.FirstName = "1";
     Console.WriteLine("User1 changes the name with new value: {0}\n", firstConnection.Employees.First().FirstName);
 }
예제 #5
0
        public static void Main()
        {
            using (var firstDbContext = new NorthwindEntities())
            {
                var firstEmployeeFromFirstDbContext = firstDbContext.Employees.First();
                Console.WriteLine("First user see: {0}", firstEmployeeFromFirstDbContext.FirstName);
                firstEmployeeFromFirstDbContext.FirstName = "First";
                Console.WriteLine("First user changes the name with new value: {0}\n", firstEmployeeFromFirstDbContext.FirstName);

                firstDbContext.SaveChanges();

                using (var secondDbContext = new NorthwindEntities())
                {
                    var firstEmployeeFromSecondDbContext = secondDbContext.Employees.First();

                    Console.WriteLine("Second user see: {0}", firstEmployeeFromSecondDbContext.FirstName);
                    firstEmployeeFromSecondDbContext.FirstName = "Second";
                    Console.WriteLine("Second user changes the name with new value: {0}\n", firstEmployeeFromSecondDbContext.FirstName);

                    secondDbContext.SaveChanges();
                }

                Console.WriteLine("After all changes:");
                Console.WriteLine("First user see: {0}\n", firstEmployeeFromFirstDbContext.FirstName);
            }

            using (var northwindEntities = new NorthwindEntities())
            {
                Console.WriteLine("Actual result: {0}", northwindEntities.Employees.First().FirstName);
            }
        }
 private static void StartFirstConnection()
 {
     using (var firstConnection = new NorthwindEntities())
     {
         PrintWhatFirstUserSees(firstConnection);
         StartSecondConnection(firstConnection);
         PrintWhatFirstUserSeesAfterChanges(firstConnection);
     }
 }
        public static void Main()
        {
            Console.Write("Loading...");

            using (var northwindEntities = new NorthwindEntities())
            {
                var contactNames = northwindEntities.Customers.Select(c => c.ContactName); // Projection
                Console.WriteLine("\r" + string.Join(Environment.NewLine, contactNames));
            }
        }
 private static void StartSecondConnection(NorthwindEntities firstConnection)
 {
     using (var secondConnection = new NorthwindEntities())
     {
         PrintWhatSecondUserSees(secondConnection);
         firstConnection.SaveChanges();
         secondConnection.SaveChanges();
         PrintWhatSecondUserSeesAfterChanges(secondConnection);
     }
 }
예제 #9
0
        public static int ModifyCustomerCompanyName(string customerId, string newCompanyName)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var customer = dbContext.Customers.Find(customerId);
                customer.CompanyName = newCompanyName;
                int affectedRows = dbContext.SaveChanges();

                return affectedRows;
            }
        }
        public static int InsertCustomer(Customer customer)
        {
            var affectedRows = 0;
            using (var db = new NorthwindEntities())
            {
                db.Customers.Add(customer);
                affectedRows = db.SaveChanges();
            }

            return affectedRows;
        }
예제 #11
0
        public static int DeleteCustomer(string customerId)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var customer = dbContext.Customers.Find(customerId);
                dbContext.Customers.Remove(customer);
                int affectedRows = dbContext.SaveChanges();

                return affectedRows;
            }
        }
예제 #12
0
        public static void Main()
        {
            // The connection string in App.config is changed

            Console.Write("Loading...");

            using (var northwindEntities = new NorthwindEntities())
            {
                northwindEntities.Database.CreateIfNotExists();
                Console.WriteLine("\rNumbers of customers: " + northwindEntities.Customers.Count());
            }
        }
        public static int ModifyCustomerAddress(string customerID, string newAddress)
        {
            var affectedRows = 0;
            using (var db = new NorthwindEntities())
            {
                var targetCustomer = db.Customers.Find(customerID);
                targetCustomer.Address = newAddress;
                affectedRows = db.SaveChanges();
            }

            return affectedRows;
        }
예제 #14
0
        // 03. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
        public static IEnumerable<IGrouping<string, Customer>> FindCustomersByOrderYearAndCountry(int orderYear, string country)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var targetedCustomers = dbContext.Orders
                                                 .Where(o => o.OrderDate.Value.Year == orderYear && o.ShipCountry == country)
                                                 .Select(o => o.Customer)
                                                 .GroupBy(c => c.ContactName)
                                                 .ToList();

                return targetedCustomers;
            }
        }
예제 #15
0
        public static void Main()
        {
            var supplierName = "Exotic Liquids";
            var startDate = new DateTime(1996, 1, 1);
            var endDate = new DateTime(2000, 12, 31);

            using (var northwindEntities = new NorthwindEntities())
            {
                var totalIncomes = northwindEntities.usp_FindTotalIncomesOfSupplier(supplierName, startDate, endDate).Single();

                Console.WriteLine("Total incomes: {0:C}", totalIncomes);
            }
        }
예제 #16
0
        public static void Main(string[] args)
        {
            using (var dbContext = new NorthwindEntities())
            {
                string generatedScript = ((IObjectContextAdapter) dbContext).ObjectContext.CreateDatabaseScript();

                StringBuilder dbScript = new StringBuilder();
                dbScript.Append("USE NorthwindTwin ");
                dbScript.Append(generatedScript);

                dbContext.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "CREATE DATABASE NorthwindTwin");
                dbContext.Database.ExecuteSqlCommand(dbScript.ToString());
            }
        }
        public static void Main()
        {
            // See file -> Northwind.Models -> `Employee.cs
 
            using (var dbContext = new NorthwindEntities())
            {
                foreach (var employee in dbContext.Employees.Include("Territories"))
                {
                    var correspondingTerritories = employee.CorrespondingTerritories.Select(c => c.TerritoryID);
                    var correspondingTerritoriesAsString = string.Join(", ", correspondingTerritories);
                    Console.WriteLine("{0} -> Territory IDs: {1}", employee.FirstName, correspondingTerritoriesAsString);
                }
            }
        }
예제 #18
0
        private static int TestWithTransaction()
        {
            var affectedRows = 0;
            var customerId = "RATTC";
            var employeeId = 5;

            var invalidEmployeeId = 5000;

            using (var dbContext = new NorthwindEntities())
            {
                using (var transaction = dbContext.Database.BeginTransaction())
                {
                    try
                    {
                        // To test with invalid data and see what happens, change employeeId with invalidEmployeeId
                        // then the transaction will rollback and there will be no added orders
                        var firstOrder = new Order()
                        {
                            CustomerID = customerId,
                            EmployeeID = employeeId
                        };

                        dbContext.Orders.Add(firstOrder);

                        var secondOrder = new Order()
                        {
                            CustomerID = customerId,
                            EmployeeID = employeeId
                        };

                        dbContext.Orders.Add(secondOrder);

                        affectedRows = dbContext.SaveChanges();

                        transaction.Commit();

                        Console.WriteLine("- Finish successfully => Commit transaction");
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();

                        Console.WriteLine("- Exception: Finish Unsuccessfully => Rollback transaction");
                    }
                }
            }

            return affectedRows;
        }
예제 #19
0
        public static void Main()
        {
            using (var northwindEntities = new NorthwindEntities())
            {
                var customerNames = northwindEntities.Customers.Select(c => new
                {
                    Name = c.ContactName
                });

                foreach (var customer in customerNames)
                {
                    Console.WriteLine(customer.Name);
                }
            }
        }
        public static void Main()
        {
            // IMPORTANT: At first you must execute "Create-Stored-Procedure.sql" in Northwind database.

            var supplierName = "Exotic Liquids";
            var startDate = new DateTime(1996, 1, 1);
            var endDate = new DateTime(1997, 12, 31);

            using (var northwindEntities = new NorthwindEntities())
            {
                var totalIncomes = northwindEntities.usp_TotalIncomesOfSupplier(supplierName, startDate, endDate).Single();

                Console.WriteLine("Total incomes: {0:C}", totalIncomes);
            }
        }
        public static int DeleteCustomerById(string customerID)
        {
            var affectedRows = 0;
            using (var db = new NorthwindEntities())
            {
                Customer customerToDelete = db.Customers.Find(customerID);
                if (customerToDelete != null)
                {
                    db.Customers.Remove(customerToDelete);
                    affectedRows = db.SaveChanges();
                }
            }

            return affectedRows;
        }
        public static IEnumerable<string> GetCustomersByOrderSQL(int year, string shipCountry)
        {
            var northwind = new NorthwindEntities();
            string query =
                @"SELECT DISTINCT c.ContactName
                FROM Orders o
                JOIN Customers c ON o.CustomerID = c.CustomerID
                WHERE YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1}
                ORDER BY c.ContactName";

            object[] parameters = { year, shipCountry };
            var customers = northwind.Database.SqlQuery<string>(query, parameters);

            return customers;
        }
 /// <summary>
 /// Finds all customers who have orders made in 1997 and shipped to Canada.
 /// </summary>
 /// <returns>Enumerated collection</returns>
 public static IEnumerable<Customer> Customers_With_Orders_In_1997_Shipped_To_Canada_View()
 {
     var entities = new List<Customer>();
     
     var orderDateYear = 1997;
     var shipCountry = "Canada";
     
     using (var dbContext = new NorthwindEntities())
     {
         entities = (from customer in dbContext.Customers
                     join order in dbContext.Orders on customer.CustomerID equals order.CustomerID
                     where order.OrderDate.Value.Year == orderDateYear && order.ShipCountry == shipCountry
                     select customer).ToList();
     }
     
     return entities;
 }
        public static int DeleteCustomer(string customerId)
        {
            var affectedRows = 0;

            using (var dbContext = new NorthwindEntities())
            {
                var customersToDelete = dbContext.Customers.Where(c => c.CustomerID == customerId);

                if (customersToDelete.Count() > 0)
                {
                    foreach (var customer in customersToDelete)
                    {
                        dbContext.Customers.Remove(customer);
                    }
                }

                affectedRows = dbContext.SaveChanges();
            }

            return affectedRows;
        }
예제 #25
0
        public static int InsertCustomer(
            string customerID,
            string companyName,
            string contactName = null,
            string city = null,
            string contactTitle = null,
            string address = null,
            string region = null,
            string postalCode = null,
            string country = null,
            string phone = null,
            string fax = null)
        {
            var newCustomer = new Customer
            {
                CustomerID = customerID,
                CompanyName = companyName,
                City = city,
                ContactName = contactName,
                ContactTitle = contactTitle,
                Address = address,
                Region = region,
                PostalCode = postalCode,
                Country = country,
                Phone = phone,
                Fax = fax,
            };

            using (var dbContext = new NorthwindEntities())
            {
                dbContext.Customers.Add(newCustomer);
                int affectedRows = dbContext.SaveChanges();

                return affectedRows;
            }
        }
 public CategoryController()
 {
     _entity = new NorthwindEntities();
     _entity.Configuration.ProxyCreationEnabled = false;
 }
 private static void PrintWhatSecondUserSeesAfterChanges(NorthwindEntities secondConnection)
 {
     Console.WriteLine("After all changes:");
     Console.WriteLine("User2 see: {0}\n", secondConnection.Employees.First().FirstName);
 }
        /// <summary>
        /// Started the transaction implicitly. 
        /// </summary>
        private static int TestOnImplicitlyStartedTransaction()
        {
            var affectedRows = 0;
            var customerId = "RATTC";
            var employeeId = 6;
            
            var invalidEmployeeId = int.MaxValue;
            
            using (var dbContext = new NorthwindEntities())
            {
                try
                {
                    #region [Add Orders]
                    
                    // This cause an error
                    var firstOrder = new Order()
                    {
                        CustomerID = customerId,
                        EmployeeID = invalidEmployeeId // employeeId
                    };
                    
                    dbContext.Orders.Add(firstOrder);
                    
                    var secondOrder = new Order()
                    {
                        CustomerID = customerId,
                        EmployeeID = employeeId
                    };
                    
                    secondOrder.Order_Details.Add(new Order_Detail()
                    {
                        OrderID = secondOrder.OrderID,
                        ProductID = 5,
                        UnitPrice = 12.34m,
                        Quantity = 100,
                        Discount = 0.2f
                    });

                    dbContext.Orders.Add(secondOrder);

                    #endregion
                
                    affectedRows = dbContext.SaveChanges();
                
                    Console.WriteLine("- Finish successfully => Commit transaction");
                }
                catch (Exception)
                {
                    Console.WriteLine("- Exception: Finish Unsuccessfully => Rollback transaction");
                }
            }
        
            return affectedRows;
        }
        /// <summary>
        /// Started the transaction explicitly. We have options to commit / roll-back transactions.
        /// </summary>
        private static int TestOnExplicitlyStartedTransaction()
        {
            var affectedRows = 0;
            var customerId = "VINET";
            var employeeId = 5;

            var invalidEmployeeId = int.MaxValue;

            using (var dbContext = new NorthwindEntities())
            {
                using (var transaction = dbContext.Database.BeginTransaction())
                {
                    try
                    {
                        #region [Add Orders]
                        
                        // This cause an error
                        var firstOrder = new Order()
                        {
                            CustomerID = customerId,
                            EmployeeID = invalidEmployeeId // employeeId
                        };
                        
                        dbContext.Orders.Add(firstOrder);
                        
                        var secondOrder = new Order()
                        {
                            CustomerID = customerId,
                            EmployeeID = employeeId
                        };
                        
                        secondOrder.Order_Details.Add(new Order_Detail()
                        {
                            OrderID = secondOrder.OrderID,
                            ProductID = 5,
                            UnitPrice = 12.34m,
                            Quantity = 100,
                            Discount = 0.2f
                        });
                        
                        dbContext.Orders.Add(secondOrder);
                        
                        #endregion
                        
                        affectedRows = dbContext.SaveChanges();
                        
                        // Finish successfully => Commit transaction
                        transaction.Commit();
                        
                        // Test to Rollback() insted of Commit() and you will see that the changes are rolled-back
                        //transaction.Rollback();
                        
                        Console.WriteLine("- Finish successfully => Commit transaction");
                    }
                    catch (Exception)
                    {
                        // Finish Unsuccessfully => Rollback transaction
                        transaction.Rollback();
                        
                        Console.WriteLine("- Exception: Finish Unsuccessfully => Rollback transaction");
                    }
                }
            }
            
            return affectedRows;
        }
 /// <summary>
 /// Finds all the sales by specified region and period (start/end dates).
 /// </summary>
 /// <returns>Enumerated collection</returns>
 public static IEnumerable<Order> Sales_By_Specified_Region_And_Date_Period_View(
     string region,
     DateTime? startDate,
     DateTime? endDate)
 {
     var entities = new List<Order>();
     
     using (var dbContext = new NorthwindEntities())
     {
         entities = (from order in dbContext.Orders
                     where order.ShipRegion == region &&
                           order.OrderDate >= startDate &&
                           order.OrderDate <= endDate
                     select order).ToList();
     }
     
     return entities;
 }