private void GetProducts()
{
using (var db = new ShoppingContext())
{
var query = from p in db.Products
join c in db.Categories on p.CategoryID equals c.CategoryID
select new
{
Picture = c.Picture,
ProductName = p.ProductName,
UnitPrice = p.UnitPrice,
UnitInStock = p.UnitsInStock,
CategoryName = c.CategoryName
};
gridControl1.DataSource = query.ToList();
}
}
private void GetEmployees()
{
using (var db = new ShoppingContext())
{
var query = from e in db.Employees
orderby e.FirstName
select new
{
Photo = e.Photo,
EmployeeID = e.EmployeeID,
FirstName = e.FirstName,
LastName = e.LastName,
Title = e.Title,
Birthdate = e.BirthDate,
Country = e.Country
};
gcEmployees.DataSource = query.ToList();
}
}
private void GetOrders()
{
using (var db = new ShoppingContext())
{
var query = from o in db.Orders
join c in db.Customers on o.CustomerID equals c.CustomerID
join e in db.Employees on o.EmployeeID equals e.EmployeeID
select new
{
CustomerId = c.CustomerID,
CompanyName = c.CompanyName,
FirstName = e.FirstName,
LastName = e.LastName,
ShipName = o.ShipName,
ShipCountry = o.ShipCountry,
Freight = o.Freight
};
gcOrders.DataSource = query.ToList();
}
}
private void GetSummary2()
{
//Her bir Customer ID tarafindan siparis edilen order’lari
//categori’leri bazinda gruplayarak toplam order quantity,
//averaj unit price ve average discount miktari hesaplanacak.
//“Customer ID”, “Category ID”, “Toplam SIparis Miktari”,
//“Averaj Birim Fiyat”, “Average Iskonto Miktari”
/*select CustomerID, C.CategoryID,
* COUNT(A.OrderID) as OrderID,
* SUM(A.Quantity) as TotalQuantity,
* AVG(A.UnitPrice) as AverageUnitPrice,
* AVG(A.Discount) as AverageDiscount
* from
* [Order Details] as A,
* Orders as B,
* Products as P,
* Categories as C
* where A.OrderID = B.OrderID and P.ProductID = A.ProductID and P.CategoryID = C.CategoryID
* group by CustomerID, C.CategoryID*/
using (var db = new ShoppingContext())
{
var query = from orderDetails in db.Order_Details
join orders in db.Orders on orderDetails.OrderID equals orders.OrderID
join products in db.Products on orderDetails.ProductID equals products.ProductID
join categories in db.Categories on products.ProductID equals categories.CategoryID
group new { orderDetails, categories, orders } by new { categories.CategoryID, orders.CustomerID } into grouping
select new
{
CustomerID = grouping.Key.CustomerID,
CategoryID = grouping.Key.CategoryID,
ToplamSiparisMiktari = grouping.Sum(x => x.orderDetails.Quantity),
AverajBirimFiyat = grouping.Average(x => x.orderDetails.UnitPrice),
AverageIskontoMiktari = grouping.Average(x => x.orderDetails.Discount)
};
gcSummary2.DataSource = query.ToList();
}
}
private void Delete_Click(object sender, EventArgs e)
{
DXMenuItem menuItem = sender as DXMenuItem;
if (XtraMessageBox.Show("Are you sure" + " ?", "Delete Employee", MessageBoxButtons.YesNo) != DialogResult.Yes)
{
return;
}
using (var db = new ShoppingContext())
{
var query = db.Employees
.Where(x => x.EmployeeID == id);
var employee = query.FirstOrDefault();
db.Employees.Remove(employee);
db.SaveChanges();
MainForm mainForm = new MainForm();
mainForm.Show();
Hide();
}
}
private void GetSummary1()
{
//Her bir Employee ID tarafindan isleme tabii tutulan
//order’lari gruplayarak toplam order quantity,
//averaj unit price ve average discount miktari hesaplanacak.
//“Employee ID”, “Toplam SIparis Miktari”, “Averaj Birim Fiyat”,
//“Average Iskonto Miktari”
/* select EmployeeID,
* COUNT(A.OrderID) as OrderID,
* SUM(A.Quantity) as TotalQuantity,
* AVG(A.UnitPrice) as AverageUnitPrice,
* AVG(A.Discount) as AverageDiscount
* from
* [Order Details] as A,
* Orders as B
* where A.OrderID = B.OrderID
* group by B.EmployeeID, B.OrderID
* order by EmployeeID*/
using (var db = new ShoppingContext())
{
var query = from orderDetails in db.Order_Details
join orders in db.Orders
on orderDetails.OrderID equals orders.OrderID
group new { orderDetails, orders } by new { orders.OrderID, orders.EmployeeID } into grouping
orderby grouping.Key.EmployeeID
select new
{
EmployeeID = grouping.Key.EmployeeID,
ToplamSiparisMiktari = grouping.Sum(x => x.orderDetails.Quantity),
AverajBirimFiyat = grouping.Average(x => x.orderDetails.UnitPrice),
AverageIskontoMiktari = grouping.Average(x => x.orderDetails.Discount)
};
gcSummary1.DataSource = query.ToList();
}
}
