Esempio n. 1
0
        public void Query_Task_3_1()
        {
            var newEmployee = new Employee
            {
                FirstName       = "Jon",
                LastName        = "Volta",
                Title           = "Sales Representative",
                TitleOfCourtesy = "Mr.",
                BirthDate       = new DateTime(1982, 5, 25),
                HireDate        = new DateTime(2010, 5, 4),
                Address         = "Coventry House Miner Rd.",
                City            = "London",
                Region          = "WA",
                PostalCode      = "EC2 7JR",
                Country         = "UK",
                HomePhone       = "(71) 555-7773",
                Extension       = "428",
            };

            using (var connection = new NorthwindDB(_connectionString))
            {
                try
                {
                    connection.BeginTransaction();

                    var checkExistEmployee = connection.Employees.Where(employee =>
                                                                        employee.FirstName.Equals(newEmployee.FirstName) &&
                                                                        employee.LastName.Equals(newEmployee.LastName));

                    if (!checkExistEmployee.Any())
                    {
                        var newEmployeeId = Convert.ToInt32(connection.InsertWithIdentity(newEmployee));

                        connection.Territories
                        .Where(territory =>
                               territory.TerritoryDescription.Equals("Westboro") &&
                               territory.TerritoryDescription.Equals("Bedford"))
                        .Insert(connection.EmployeeTerritories, employeeTerritories => new EmployeeTerritory
                        {
                            TerritoryID = employeeTerritories.TerritoryID,
                            EmployeeID  = newEmployeeId
                        });

                        connection.CommitTransaction();

                        var result = connection.Employees.Where(employee => employee.EmployeeID == newEmployeeId)
                                     .ToArray();

                        Assert.AreEqual(newEmployee.FirstName, result[0].FirstName);
                        Assert.AreEqual(newEmployee.LastName, result[0].LastName);
                    }
                }
                catch (Exception e)
                {
                    connection.RollbackTransaction();
                }
            }
        }
Esempio n. 2
0
        public void Query_Task_3_4()
        {
            using (var connection = new NorthwindDB(_connectionString))
            {
                var notShippedOrders = connection.OrderDetails
                                       .LoadWith(orderDetail => orderDetail.OrderDetailsOrder)
                                       .LoadWith(orderDetail => orderDetail.OrderDetailsProduct)
                                       .Where(orderDetails => orderDetails.OrderDetailsOrder.ShippedDate == null).ToList();

                if (notShippedOrders.Any())
                {
                    connection.BeginTransaction();

                    var countUpdateRows = 0;

                    try
                    {
                        foreach (var order in notShippedOrders)
                        {
                            countUpdateRows += Convert.ToInt32(connection.OrderDetails
                                                               .LoadWith(orderDetail => orderDetail.OrderDetailsProduct)
                                                               .Update(orderDetail => orderDetail.OrderID == order.OrderID && orderDetail.ProductID == order.ProductID,
                                                                       product => new OrderDetail
                            {
                                ProductID = connection.Products
                                            .FirstOrDefault(prod => prod.ProductID != order.ProductID && prod.CategoryID == order.OrderDetailsProduct.CategoryID).ProductID == 0 ?
                                            order.ProductID :
                                            connection.Products
                                            .FirstOrDefault(prod => prod.ProductID != order.ProductID && prod.CategoryID == order.OrderDetailsProduct.CategoryID)
                                            .ProductID
                            }));
                        }

                        connection.CommitTransaction();
                    }
                    catch (Exception e)
                    {
                        connection.RollbackTransaction();
                    }

                    Console.WriteLine($"{countUpdateRows} was updated.");
                }
                else
                {
                    Console.WriteLine("Orders for update is absent.");
                }
            }
        }
Esempio n. 3
0
        public void Query_Task_3_3()
        {
            var newProductList = new List <Product>
            {
                new Product
                {
                    ProductName = "Diesel",
                    Category    = new Category
                    {
                        CategoryName = "Oil",
                        Description  = "Oil and gas production",
                        Picture      = null
                    },
                    Supplier = new Supplier
                    {
                        CompanyName  = "PetrolUnit",
                        ContactName  = "Charlotte Cooper",
                        ContactTitle = "Purchasing Manager",
                        Address      = "49 Gilbert St.",
                        City         = "London",
                        Region       = null,
                        PostalCode   = "EC1 4SD",
                        Country      = "UK",
                        Phone        = "(171) 555-2222",
                        Fax          = null,
                        HomePage     = null
                    },
                    QuantityPerUnit = "12 - 550 ml bottles",
                    UnitPrice       = new decimal(50.00),
                    UnitsInStock    = 0,
                    UnitsOnOrder    = 0,
                    ReorderLevel    = 0,
                    Discontinued    = false
                },
                new Product
                {
                    ProductName = "Petrol",
                    Category    = new Category
                    {
                        CategoryName = "Oil",
                        Description  = "Oil and gas production",
                        Picture      = new byte[0]
                    },
                    Supplier = new Supplier
                    {
                        CompanyName  = "PetrolUnit",
                        ContactName  = "Charlotte Cooper",
                        ContactTitle = "Purchasing Manager",
                        Address      = "49 Gilbert St.",
                        City         = "London",
                        Region       = null,
                        PostalCode   = "EC1 4SD",
                        Country      = "UK",
                        Phone        = "(171) 555-2222",
                        Fax          = null,
                        HomePage     = null
                    },
                    QuantityPerUnit = "12 - 550 ml bottles",
                    UnitPrice       = new decimal(50.00),
                    UnitsInStock    = 0,
                    UnitsOnOrder    = 0,
                    ReorderLevel    = 0,
                    Discontinued    = false
                }
            };

            var productNames = new[] { "Diesel", "Petrol" };

            using (var connection = new NorthwindDB(_connectionString))
            {
                try
                {
                    connection.BeginTransaction();

                    foreach (var product in newProductList)
                    {
                        var existProduct = connection.Products.FirstOrDefault(prod => prod.ProductName == product.ProductName);

                        if (existProduct == null)
                        {
                            var existCategory =
                                connection.Categories.FirstOrDefault(category => category.CategoryName == product.Category.CategoryName);

                            product.CategoryID = existCategory?.CategoryID ?? Convert.ToInt32(connection.InsertWithIdentity(new Category
                            {
                                CategoryName = product.Category.CategoryName,
                                Description  = product.Category.Description,
                                Picture      = product.Category.Picture
                            }));

                            var existSupplier =
                                connection.Suppliers.FirstOrDefault(supplier => supplier.CompanyName == product.Supplier.CompanyName);

                            product.SupplierID = existSupplier?.SupplierID ?? Convert.ToInt32(connection.InsertWithIdentity(new Supplier
                            {
                                CompanyName  = product.Supplier.CompanyName,
                                ContactName  = product.Supplier.ContactName,
                                ContactTitle = product.Supplier.ContactTitle,
                                Address      = product.Supplier.Address,
                                City         = product.Supplier.City,
                                Region       = product.Supplier.Region,
                                PostalCode   = product.Supplier.PostalCode,
                                Country      = product.Supplier.Country,
                                Phone        = product.Supplier.Phone,
                                Fax          = product.Supplier.Fax,
                                HomePage     = product.Supplier.HomePage
                            }));

                            connection.InsertWithIdentity(product);
                        }
                    }

                    connection.CommitTransaction();
                }
                catch (Exception e)
                {
                    connection.RollbackTransaction();
                }

                var result = connection.Products.Where(prod => prod.ProductName.In(productNames)).ToArray();

                Assert.AreEqual(newProductList.Count, result.Length);
            }
        }