public 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();
}
catch
{
connection.RollbackTransaction();
}
}
static void Main(string[] args)
{
var qd = new QueryDemostation();
using (var connection = new NorthwindDataConnection())
{
// qd.ListProductsWithCategorySupplier(connection);
// qd.EmployeesWithRegion(connection);
// qd.RegionWithCountEmployee(connection);
// qd.EmployeesWithShippers(connection);
// qd.MoveProcuctToNewCategory(connection, 1, 2);
// qd.AddNewEpmloyeeWithTerretory(connection, new Employee { FirstName = "Sergey", LastName = "Davydov" }, 1);
//qd.AddProducts(connection, new List<Product>
//{
//new Product
//{
// ProductName = "Pipelace",
// Category = new Category {CategoryName = "Vehicles"},
// Supplier = new Supplier {CompanyName = "SD industries"}
//},
//new Product
//{
// ProductName = "Pipelace with Gravicapa",
// Category = new Category {CategoryName = "Vehicles"},
// Supplier = new Supplier {CompanyName = "SD industries"}
//}
//});
// qd.ReplaceProductInNotShippedOrder(connection);
Console.Read();
}
}
public void AddNewEpmloyeeWithTerretory(NorthwindDataConnection connection, Employee emp, int regionID)
{
try
{
connection.BeginTransaction();
var newID = Convert.ToInt32(connection.InsertWithIdentity(emp));
Console.WriteLine($"Add new employee with id {newID}");
connection.Territorys.Where(x => x.RegionID == regionID)
.Insert(connection.EmployeeTerritorys,
t => new EmployeeTerritory {
EmployeeID = newID, TerritoryID = t.TerritoryID
});
connection.CommitTransaction();
}
catch (Exception e)
{
connection.RollbackTransaction();
}
}
