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);
}
}