static void AddProducts(NorthwindDataConnection connection, List <Product> products)
{
try
{
connection.BeginTransaction();
foreach (var product in products)
{
var category = connection.Categories.FirstOrDefault(c => c.CategoryName == product.Category.CategoryName);
product.CategoryID = category?.CategoryID ?? Convert.ToInt32(connection.InsertWithIdentity(
new Category
{
CategoryName = product.Category.CategoryName
}));
var supplier = connection.Suppliers.FirstOrDefault(s => s.CompanyName == product.Supplier.CompanyName);
product.SupplierID = supplier?.SupplierID ?? Convert.ToInt32(connection.InsertWithIdentity(
new Supplier
{
CompanyName = product.Supplier.CompanyName
}));
}
connection.BulkCopy(products);
connection.CommitTransaction();
Console.WriteLine("Products added succesfully");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
connection.RollbackTransaction();
}
}
// Task 3
static void AddNewEpmloyeeWithRegion(NorthwindDataConnection connection, Employee emp, int regionID)
{
try
{
connection.BeginTransaction();
var newID = Convert.ToInt32(connection.InsertWithIdentity(emp));
Console.WriteLine($"Tring add new employee with id {newID}");
connection.Territories.Where(x => x.RegionID == regionID)
.Insert(connection.EmployeeTerritorys,
t => new EmployeeTerritory {
EmployeeID = newID, TerritoryID = t.TerritoryID
});
connection.CommitTransaction();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
connection.RollbackTransaction();
}
}