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(); } } }
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."); } } }
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); } }