static void FindCustomers(DateTime year, string country)
        {
            using (NorthwindEntities northwindDBContext = new NorthwindEntities())
            {

                //var customers = northwindDBContext.Orders
                //                                   .Where(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada")
                //                                   .GroupBy(o => o.Customer.CompanyName)
                //                                   .ToList();
                var customers = 
                    from customer in northwindDBContext.Customers
                    join order in northwindDBContext.Orders
                    on customer.CustomerID equals order.CustomerID
                    where order.ShipCountry == country && order.OrderDate.Value.Year == year.Year

                    select new
                    {
                        CustomerID = customer.CustomerID,
                        OrderDate = order.OrderDate,
                        ShipCountry = order.ShipCountry
                    };

                northwindDBContext.SaveChanges();

                foreach (var customer in customers)
                {
                    Console.WriteLine("{0} ordered on {1} from {2}", customer.CustomerID, customer.OrderDate, customer.ShipCountry);
                }
            }
        }
Ejemplo n.º 2
0
 public static void InsertCustomer(Customer customer)
 {
     using (var dbContext = new NorthwindEntities())
     {
         dbContext.Customers.Add(customer);
         dbContext.SaveChanges();
     }
 }
        public static decimal? GetTotalIncomes(string supplierName, DateTime? startDate, DateTime? endDate)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var totalIncome = dbContext
                    .usp_GetTotalIncome(supplierName, startDate, endDate)
                    .First();

                return totalIncome;
            }
        }
        public static void Main(string[] args)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var totalIncome = GetTotalIncomes(
                    "Pavlova, Ltd.",
                    new DateTime(1990, 1, 1),
                    new DateTime(2000, 1, 1));

                Console.WriteLine(totalIncome);
            }
        }
Ejemplo n.º 5
0
        public static void DeleteCustomer(string customerID)
        {
            using (NorthwindEntities northwindDBContext = new NorthwindEntities())
            {
                Customer customer = northwindDBContext.Customers.First(x => x.CustomerID == customerID);

                northwindDBContext.Customers.Remove(customer);
                northwindDBContext.SaveChanges();

                Console.WriteLine("Customer is deleted.");
            }
        }
Ejemplo n.º 6
0
        public static ICollection<Customer> FilterCustomersByOrders(int year, string shippedTo)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var customers = dbContext
                    .Orders
                    .Where(x => x.OrderDate.Value.Year == 1997)
                    .Where(x => x.ShipCountry == "Canada")
                    .Select(x => x.Customer);

                return customers.ToList();
            }
        }
        public static void Main()
        {
            using (var dbContext = new NorthwindEntities())
            {
                var createQuery = "CREATE DATABASE NorthwindTwin";
                dbContext.Database.ExecuteSqlCommand(createQuery);

                var generatedScript = ((IObjectContextAdapter)dbContext)
                    .ObjectContext.CreateDatabaseScript();
                var fillQuery = "USE NorthwindTwin " + generatedScript;
                dbContext.Database.ExecuteSqlCommand(fillQuery);
            }
        }
        public static void Main()
        {
            // Check EmployeeExtension class in Models project
            using (var context = new NorthwindEntities())
            {
                var employee = context.Employees.Find(5);

                foreach (var territory in employee.CorrespondingTerritories)
                {
                    Console.WriteLine(
                        "Corresponding territories - {0}",
                        territory.TerritoryDescription);
                }
            }
        }
Ejemplo n.º 9
0
        public static void InsertCustomer(string customerID, string companyName)
        {
            using (NorthwindEntities northwindDBContext = new NorthwindEntities())
            {
                Customer customer = new Customer
                {
                    CustomerID = customerID,
                    CompanyName = companyName,
                };

                northwindDBContext.Customers.Add(customer);
                northwindDBContext.SaveChanges();

                Console.WriteLine("Customer is inserted");
            }
        }
        public static void CustomersWithOrdersIn1997FromCanada_SQLQuery()
        {
            using (NorthwindEntities northwindDBContext = new NorthwindEntities())
            {

                var customers = northwindDBContext.Database.SqlQuery<string>(@"SELECT c.CompanyName FROM Orders o 
	                                                                         JOIN Customers c ON o.CustomerID = c.CustomerID
                                                                          WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada'
                                                                          GROUP BY c.CompanyName");
                northwindDBContext.SaveChanges();

                foreach (var customer in customers)
                {
                    Console.WriteLine(customer);
                }
            }
        }
Ejemplo n.º 11
0
        public static ICollection<Customer> FilterCustomersByOrders(int year, string shippedTo)
        {
            using (var dbContext = new NorthwindEntities())
            {
                object[] param = { year, shippedTo };
                string query = "SELECT * " +
                    "FROM Orders o " +
                    "JOIN Customers c " +
                    "ON o.CustomerID = c.CustomerID " +
                    "WHERE YEAR(o.OrderDate) = {0} " + 
                    "AND o.ShipCountry = {1}";

                var customers = dbContext.Database.SqlQuery<Customer>(query, param);

                return customers.ToList();
            }
        }
Ejemplo n.º 12
0
        private static bool InsertOrders(int retries, Order[] orders)
        {
            var success = false;
            var dbContext = new NorthwindEntities();

            // Retry loop.
            for (int i = 0; i < retries; i++)
            {
                using (var transaction = new TransactionScope())
                {
                    try
                    {
                        foreach (var order in orders)
                        {
                            dbContext.Orders.Add(order);
                        }

                        dbContext.SaveChanges();
                        transaction.Complete();
                        success = true;
                        break;
                    }
                    catch (Exception ex)
                    {
                        if (ex.GetType() != typeof(UpdateException))
                        {
                            Console.WriteLine(
                                "An error occured. The operation cannot be retried. {0}",
                                ex.Message);

                            break;
                        }
                    }
                }
            }

            if (success)
            {
                ((IObjectContextAdapter)dbContext)
                    .ObjectContext.AcceptAllChanges();
            }

            dbContext.Dispose();
            return success;
        }
        public static void FindSalesBySpecifiedRegionAndPeriod(string region = null, string startDate = null, string endDate = null)
        {
            using (NorthwindEntities northwindDBContext = new NorthwindEntities())
            {
                DateTime startDateParsed = DateTime.Parse(startDate);
                DateTime endDateParsed = DateTime.Parse(endDate);

                var customers = northwindDBContext.Orders
                                                   .Where(o => (o.OrderDate > startDateParsed && o.OrderDate < endDateParsed) || o.ShipCountry == region)
                                                   .GroupBy(o => o.ShipName);
                northwindDBContext.SaveChanges();

                foreach (var customer in customers)
                {
                    Console.WriteLine(customer.Key);
                }
            }
        }
Ejemplo n.º 14
0
        public static void DeleteCustomer(string customerID)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var customer = dbContext
                    .Customers
                    .Where(x => x.CustomerID == customerID)
                    .FirstOrDefault();

                if (customer == null)
                {
                    throw new ArgumentNullException(
                        "No such user in database!");
                }

                dbContext.Customers.Remove(customer);
                dbContext.SaveChanges();
            }
        }
        public static void Main()
        {
            using (var firstContext = new NorthwindEntities())
            using (var secondContext = new NorthwindEntities())
            {
                firstContext
                    .Customers
                    .Find("FOLKO")
                    .ContactName = "Svetlin";

                secondContext
                    .Customers
                    .Find("FOLKO")
                    .ContactName = "Nakov";

                firstContext.SaveChanges();
                secondContext.SaveChanges();
            }
        }
Ejemplo n.º 16
0
        public static void Main()
        {
            using (var dbContext = new NorthwindEntities())
            {
                var customer = new Customer()
                {
                    CustomerID = "ZAZA",
                    CompanyName = "Mad House LTD",
                    ContactName = "Mad Man",
                    ContactTitle = "Owner",
                };

                DataAccessUtils.InsertCustomer(customer);

                customer.Country = "Bulgaria";
                DataAccessUtils.ModifyCustomer(customer);

                DataAccessUtils.DeleteCustomer(customer.CustomerID);
            }
        }
Ejemplo n.º 17
0
        public static void UpdateCustomer(string customerID, Customer newCustomer)
        {
            using (NorthwindEntities northwindDBContext = new NorthwindEntities())
            {
                Customer customer = northwindDBContext.Customers.First(x => x.CustomerID == customerID);

                customer.CompanyName = newCustomer.CompanyName;
                customer.ContactName = newCustomer.ContactName;
                customer.ContactTitle = newCustomer.ContactTitle;
                customer.Address = newCustomer.Address;
                customer.City = newCustomer.City;
                customer.Region = newCustomer.Region;
                customer.PostalCode = newCustomer.PostalCode;
                customer.Country = newCustomer.Country;
                customer.Phone = newCustomer.Phone;
                customer.Fax = newCustomer.Fax;

                northwindDBContext.SaveChanges();

                Console.WriteLine("Customer is updated.");
            }
        }
Ejemplo n.º 18
0
        public static ICollection<Order> FilterOrdersByRegionAndPeriod(string region, DateTime periodFrom, DateTime periodTo)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var orders = dbContext
                    .Orders
                    .Where(x => x.OrderDate >= periodFrom)
                    .Where(x => x.OrderDate <= periodTo);

                if (region == null)
                {
                    orders = orders
                        .Where(x => x.ShipRegion == null);
                }
                else
                {
                    orders = orders
                        .Where(x => x.ShipRegion == region);
                }

                return orders.ToList();
            }
        }