public void AddProducts()
{
TransactionScope scope = new TransactionScope();
using (var db = new DbNorthwind(providerName, connectionString))
{
List <Product> productList = GetProductList();
foreach (var product in productList)
{
Category category = db.Categories.FirstOrDefault(c => c.Name == product.Category.Name);
Supplier supplier = db.Suppliers.FirstOrDefault(s => s.CompanyName == product.Supplier.CompanyName);
if (category == null)
{
//если такой категории не существует то заносим ее в бд и получаем её id
product.CategoryId = db.InsertWithInt32Identity(new Category {
Name = product.Category.Name
});
}
else
{
product.CategoryId = category.Id;
}
if (supplier == null)
{
product.SupplierId = db.InsertWithIdentity(new Supplier {
CompanyName = product.Supplier.CompanyName
}).ToString();
}
else
{
product.SupplierId = supplier.Id;
}
}
db.BulkCopy(productList);
#region Demonstration
foreach (var product in productList)
{
foreach (var item in db.Products.LoadWith(p => p.Supplier).LoadWith(p => p.Category).Where(p => p.Name == product.Name))
{
Console.WriteLine($" Product name: {item.Name} Supplier name: {item.Supplier.CompanyName} Category name: {item.Category.Name}");
}
}
#endregion
scope.Dispose();
}
}
public void RunZ3()
{
//Z3.1
Console.WriteLine("Z3.1");
using (var db = new DbNorthwind())
{
Employees e = new Employees()
{
FirstName = "TBobby",
LastName = "TMask",
Title = null,
TitleOfCourtesy = null,
BirthDate = DateTime.Today,
HireDate = DateTime.Today,
Address = null,
City = null,
Region = null,
PostalCode = null,
Country = null,
HomePhone = null,
Extension = null,
Photo = null,
Notes = null,
ReportsTo = 1
};
var employeeID = Convert.ToInt32(db.InsertWithIdentity(e));
Territories territory = db.Territories.SingleOrDefault(t => t.TerritoryDescription == "Cary");
var et = new EmlpoyeeTerritories()
{
EmployeeID = employeeID,
TerritoryID = territory.TerritoryID.ToString()
};
db.InsertWithIdentity(et);
}
Console.WriteLine("Success!");
//Z3.2
Console.WriteLine("Z3.2");
using (var db = new DbNorthwind())
{
Random rnd = new Random();
db.Products.Where(p => p.ProductID == 5).Set(p => p.CategoryID, rnd.Next(1, 9)).Update();
Console.WriteLine("Success");
}
//Z3.3
Console.WriteLine("Z3.3");
using (var db = new DbNorthwind())
{
var category = new Categories()
{
CategoryName = "New category",
Description = "New cat",
Picture = null
};
var newCatID = Convert.ToInt32(db.InsertWithIdentity(category));
var supplier = new Suppliers()
{
CompanyName = "NewCompSup",
ContactName = "NewContactName"
};
var newSupID = Convert.ToInt32(db.InsertWithIdentity(supplier));
var p1 = new Products()
{
ProductName = "new Product1",
CategoryID = newCatID,
SupplierID = newSupID
};
var p2 = new Products()
{
ProductName = "new Product2",
CategoryID = newCatID,
SupplierID = newSupID
};
db.Insert(p1);
db.Insert(p2);
}
Console.WriteLine("Success");
//Z3.4
Console.WriteLine("Z3.4");
using (var db = new DbNorthwind())
{
List <OrderDetails> newOD = new List <OrderDetails>();
var query = db.OrderDetails.LoadWith(t => t.Products.Categories).LoadWith(t => t.Orders)
.Where(o => o.Orders.ShippedDate == null).ToArray();
var groupQuery = query.GroupBy(g => g.OrderID);
Console.WriteLine();
foreach (var g in groupQuery)
{
var idFilter = g.Select(i => i.ProductID).ToList();
Console.WriteLine("OrderID is:{0}", g.Key);
foreach (var p in g)
{
Console.WriteLine("|Product:{0}|Category:{1}|", p.Products.ProductName,
p.Products.Categories.CategoryName);
var newProductID = db.Products.LoadWith(t => t.Categories).Where(c =>
c.ProductID != p.ProductID && c.CategoryID == p.Products.CategoryID).Select(c => c.ProductID).ToArray().Except(idFilter).FirstOrDefault();
idFilter.Add(newProductID);
newOD.Add(new OrderDetails()
{
OrderID = p.OrderID,
ProductID = newProductID,
Quantity = p.Quantity,
UnitPrice = p.UnitPrice,
Discount = p.Discount
});
}
}
var newODArr = newOD.ToArray();
for (int i = 0; i < query.Length; i++)
{
if (newODArr[i].ProductID != 0)
{
db.OrderDetails.Where(od => od.OrderID == query[i].OrderID && od.ProductID == query[i].ProductID).Set(od => od.ProductID, newODArr[i].ProductID).Update();
}
}
Console.WriteLine("Success");
}
}