public void InsertListProducts(List <Product> products)
{
using (var connection = new NorthwindConnection("Northwind"))
{
try
{
connection.BeginTransaction();
foreach (var product in products)
{
var category = connection.Categories.FirstOrDefault(c => c.CategoryName == product.Category.CategoryName);
product.CategoryId = category?.CategoryId ?? connection.InsertWithInt32Identity(
new Category
{
CategoryName = product.Category.CategoryName
});
var supplier = connection.Suppliers.FirstOrDefault(s => s.CompanyName == product.Supplier.CompanyName);
product.SupplierId = supplier?.SupplierId ?? connection.InsertWithInt32Identity(
new Supplier
{
CompanyName = product.Supplier.CompanyName
});
}
connection.BulkCopy(products);
connection.CommitTransaction();
}
catch
{
connection.RollbackTransaction();
}
}
}
public IEnumerable <string> GetAllProducts()
{
using (var connection = new NorthwindConnection("Northwind"))
{
var allProducts = connection.Products.LoadWith(p => p.Category).LoadWith(p => p.Supplier)
.Select(product => $"Product name: {product.ProductName}; Category: {product.Category.CategoryName}; Supplier: {product.Supplier.ContactName}");
return(allProducts);
}
}
public int UpdateNonShippedOrders()
{
using (var connection = new NorthwindConnection("Northwind"))
{
var rowsToUpdate = connection.OrderDetails.LoadWith(od => od.Order).LoadWith(od => od.Product)
.Where(od => od.Order.ShippedDate == null);
int updatedRows = rowsToUpdate.Update(UpdateOrders(connection.Products));
return(updatedRows);
}
}
public IEnumerable <string> GetAllEmployees()
{
using (var connection = new NorthwindConnection("Northwind"))
{
var employeeWithTerritories = from employee in connection.Employees
join employeeTerritory in connection.EmployeeTerritories on employee.EmployeeId equals employeeTerritory.EmployeeId
join territory in connection.Territories on employeeTerritory.TerritoryId equals territory.TerritoryId
join regions in connection.Regions on territory.RegionId equals regions.RegionId
select new { employee.FirstName, employee.LastName, regions };
return(employeeWithTerritories
.Distinct()
.Select(item => $"Employee: {item.FirstName} {item.LastName}; Region: {item.regions.RegionDescription}"));
}
}
public int MoveProductsToAnotherCategory(int categoryIDFirst, int categoryIDSecond)
{
if (categoryIDFirst == categoryIDSecond)
{
throw new Exception("categories are same!");
}
using (var connection = new NorthwindConnection("Northwind"))
{
int updatedCount = connection.Products.Update(p => p.CategoryId == categoryIDFirst, pr => new Product
{
CategoryId = categoryIDSecond
});
return(updatedCount);
}
}
public IEnumerable <string> EmployeesCountForRegion()
{
using (var connection = new NorthwindConnection("Northwind"))
{
var allEmployeesWithRegions = from reg in connection.Regions
join territories in connection.Territories on reg.RegionId equals territories.RegionId
join empTerritories in connection.EmployeeTerritories on territories.TerritoryId equals empTerritories.TerritoryId
join employee in connection.Employees on empTerritories.EmployeeId equals employee.EmployeeId
select new { region = reg.RegionDescription, employee.EmployeeId };
var employeesGroupedByRegion = from row in allEmployeesWithRegions.Distinct()
group row by row.region into groupRegion
select new {
groupRegion.Key,
EmployeesCount = groupRegion.Count(e => e.EmployeeId != 0)
};
return(employeesGroupedByRegion.Select(item => ($"Region: {item.Key}; Employees count: {item.EmployeesCount}")));;
}
}
public int AddNewEmployeeWithTerritories(Employee newEmployee)
{
using (var connection = new NorthwindConnection("Northwind"))
{
try
{
var a = connection.BeginTransaction();
newEmployee.EmployeeId = connection.InsertWithInt32Identity(newEmployee);
connection.Territories.Where(t => t.TerritoryDescription.Length <= 5)
.Insert(connection.EmployeeTerritories, t => new EmployeeTerritory {
EmployeeId = newEmployee.EmployeeId, TerritoryId = t.TerritoryId
});
connection.CommitTransaction();
return(newEmployee.EmployeeId);
}
catch
{
connection.RollbackTransaction();
return(0);
}
}
}