예제 #1
0
        public static IEnumerable<Customer> GetCustomersByShippmentDateAndCountryNative(
            string country,
            DateTime datetime)
        {
            using (var dbContext = new NorthwindEntities())
            {
                string quotedCountry = "'" + country + "'";
                string query =
                    "SELECT c.CustomerID " +
                    "FROM Customers c " +
                    "JOIN Orders o " +
                    "ON c.CustomerID = o.CustomerID " +
                    "WHERE o.ShipCountry = {0} " +
                    "AND " +
                    "o.OrderDate  BETWEEN '01/01/{1}' AND '12/31/{1}'";

                string formatedQuery = string.Format(
                    query,
                    quotedCountry,
                    datetime.Year);

                // TODO: There must be a way smarter way for doing this!
                var resultCustomersIds = dbContext.Database.SqlQuery<string>(formatedQuery);

                foreach (var customerId in resultCustomersIds)
                {
                    var currentCustomer = dbContext.Customers.Find(customerId);
                    yield return currentCustomer;
                }
            }
        }
예제 #2
0
 // Added a connection string in App.config
 private static void CreateNorthwindTwin()
 {
     using (var dbContext = new NorthwindEntities())
     {
         dbContext.Database.CreateIfNotExists();
     }
 }
예제 #3
0
 public static void AddCustomer(Customer customer)
 {
     using (var dbContext = new NorthwindEntities())
     {
         dbContext.Customers.Add(customer);
         dbContext.SaveChanges();
     }
 }
예제 #4
0
        public static void DeleteCustomer(string customerId)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var customerToRemove = dbContext.Customers.FirstOrDefault(c => c.CustomerID == customerId);

                dbContext.Customers.Remove(customerToRemove);
                dbContext.SaveChanges();
            }
        }
예제 #5
0
        public static IEnumerable<object> FindAllWithOrdersByShippedDateAndShipCountry(DateTime shippedDate, string shipCountry)
        {
            string shipCountryLowered = shipCountry.ToLower();

            using (var dbContext = new NorthwindEntities())
            {
                return dbContext.Customers
                        .Where(c => c.Orders.Any(o => (o.ShippedDate.Value.Year == shippedDate.Year)
                            && (o.ShipCountry.ToLower() == shipCountryLowered)))
                        .Select(c => new { c.CompanyName, c.ContactName })
                        .ToList();
            }
        }
예제 #6
0
        public static void EditCustomerCompanyName(
            string customerId, 
            string newCompany)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var customerToEdit = dbContext.Customers.FirstOrDefault(c => c.CustomerID == customerId);

                customerToEdit.CompanyName = newCompany;

                dbContext.SaveChanges();
            }
        }
        private static IEnumerable<Order> GetSalesByRegionAndDateRange(
            DateTime startDate, 
            DateTime endtDate, 
            string region)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var result =
                    dbContext.Orders
                    .Where(o => o.OrderDate >= startDate && o.OrderDate <= endtDate)
                    .Where(o => o.ShipRegion == region)
                    .ToList();

                return result;
            }
        }
예제 #8
0
        public static IEnumerable<Customer> GetCustomersByShippmentDateAndCountry(string country, DateTime datetime)
        {
            using (var dbContext = new NorthwindEntities())
            {
                var result = dbContext.Customers
                    .Join(
                    dbContext.Orders
                        .Where(o => o.OrderDate.Value.Year == datetime.Year)
                        .Where(o => o.ShipCountry == country),
                    c => c.CustomerID,
                    o => o.CustomerID,
                    (c, o) => c);

                return result.ToList();
            }
        }
예제 #9
0
        public static void Insert(Customer customer)
        {
            if (customer == null)
            {
                throw new ArgumentNullException("Customer cannot be null.");
            }

            if (string.IsNullOrWhiteSpace(customer.CustomerID))
            {
                throw new ArgumentException("CustomerID is mandatory.");
            }

            using (var dbContext = new NorthwindEntities())
            {
                dbContext.Customers.Add(customer);
                dbContext.SaveChanges();
            }
        }
예제 #10
0
        private static void FindAllOrdersByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
        {
            string regionLowered = region.ToLower();

            using (var dbContext = new NorthwindEntities())
            {
                var foundOrders = dbContext.Orders
                                            .Where(o => (o.ShipRegion.ToLower() == regionLowered)
                                            && (o.OrderDate > startDate && o.OrderDate < endDate))
                                            .Select(o => new { o.OrderDate, o.ShipRegion })
                                            .ToList();

                foreach (var order in foundOrders)
                {
                    Console.WriteLine(order);
                }
            }
        }
예제 #11
0
        private static void FindAllCustomersWithOrdersIn1997ToCanadaUsingSql()
        {
            string query = @"SELECT DISTINCT(c.CompanyName)
                            FROM Customers c
                            JOIN Orders o
                            ON c.CustomerID = o.CustomerID
                            WHERE o.ShipCountry = 'Canada'
                                AND DATEPART(yyyy, o.ShippedDate) = '1997'";

            using (var dbContext = new NorthwindEntities())
            {
                var foundCustomers = dbContext.Database.SqlQuery<string>(query).ToList();

                foreach (var c in foundCustomers)
                {
                    Console.WriteLine(c);
                }

                Console.WriteLine();
            }
        }
예제 #12
0
        public static void Modify(string id, string newContactName)
        {
            if (string.IsNullOrWhiteSpace(id))
            {
                throw new ArgumentException("Id is null, empty or contains only whitespaces.");
            }

            string trimmedId = id.Trim();

            using (var dbContext = new NorthwindEntities())
            {
                var customer = dbContext.Customers
                                .Where(c => c.CustomerID.Equals(trimmedId))
                                .FirstOrDefault();

                if (customer == null)
                {
                    throw new ArgumentException("Id not found.");
                }

                customer.ContactName = newContactName;
                dbContext.SaveChanges();
            }
        }
예제 #13
0
        // Separate contexts at the same time are appropriate if they are for completely unrelated modules.
        // In the example below the changes made by the second db context override the changes from the first one.
        private static void TestMultipleDbContexts()
        {
            var firstDbContext = new NorthwindEntities();
            var secondDbContext = new NorthwindEntities();

            var firstCategoryFirstContext = firstDbContext.Categories.FirstOrDefault();
            var firstCategorySecondContext = secondDbContext.Categories.FirstOrDefault();

            firstCategoryFirstContext.CategoryName = "Drinks";
            firstCategorySecondContext.CategoryName = "Override drinks";

            firstDbContext.SaveChanges();
            secondDbContext.SaveChanges();
        }