public override IEnumerable <Model.Customer> GetCustomers()
{
using (NorthwindDataContext context = DataContext)
{
return(context.Customers.ToArray <Customer>());
}
}
public override IEnumerable <Model.Customer> GetCustomersByCountry(string country)
{
using (NorthwindDataContext context = DataContext)
{
return(context.Customers.Where(c => c.Country == country).ToArray <Customer>());
}
}
public override int GetCustomerOrdersCount(string custID)
{
using (NorthwindDataContext context = DataContext)
{
return(context.Orders.Where(c => c.CustomerID == custID).Count());
}
}
public override Model.Customer GetCustomer(string custID)
{
using (NorthwindDataContext context = DataContext)
{
return(context.Customers.Where(c => c.CustomerID == custID).SingleOrDefault <Customer>());
}
}
public void Save()
{
using (var context = new NorthwindDataContext())
{
Save(context);
}
}
public override IEnumerable <string> GetCountries()
{
using (NorthwindDataContext context = DataContext)
{
return(context.Customers.Select(c => c.Country).Distinct <string>().ToArray <string>());
}
}
public override int GetCustomerOrdersCount(string custID)
{
using (NorthwindDataContext context = DataContext)
{
return((int)(from c in context.ap_GetCustomerOrdersCount(custID)
select c.Count).SingleOrDefault <int?>());
}
}
public List<string> GetCountries()
{
using (var context = new NorthwindDataContext())
{
return (from c in context.Customers
select c.Country).Distinct().ToList();
}
}
public List<Customer> GetCustomersByCountry(string country)
{
using (var context = new NorthwindDataContext())
{
return (from c in context.Customers
where c.Country == country
select c).ToList();
}
}
public override IEnumerable <string> GetCountries()
{
using (NorthwindDataContext context = DataContext)
{
IEnumerable <string> countries = (from c in context.ap_GetCountries()
select c.Country).ToArray <string>();
return(countries);
}
}
public override IEnumerable <Model.Customer> GetCustomers()
{
using (NorthwindDataContext context = DataContext)
{
IEnumerable <Customer> custs = (from c in context.Customers
select c).ToArray <Customer>();
return(custs);
}
}
public override Model.Customer GetCustomer(string custID)
{
using (NorthwindDataContext context = DataContext)
{
Customer cust = (from c in context.Customers
where c.CustomerID == custID
select c).SingleOrDefault <Customer>();
return(cust);
}
}
public object GetCategories()
{
object categories = null;
using (var context = new NorthwindDataContext())
{
categories = context.LinqCategories.ToList();
}
return(categories);
}
public override IEnumerable <string> GetCountries()
{
using (NorthwindDataContext context = DataContext)
{
IEnumerable <string> countries = (from c in context.Customers
select c.Country).Distinct <string>();
return(countries.ToList <string>());
}
}
public override IEnumerable <Model.Customer> GetCustomersByCountry(string country)
{
using (NorthwindDataContext context = DataContext)
{
IEnumerable <Customer> custs = (from c in context.Customers
where c.Country == country
select c).ToArray <Customer>();
return(custs);
}
}
public override int GetCustomerOrdersCount(string custID)
{
using (NorthwindDataContext context = DataContext)
{
int count = (from c in context.Orders
where c.CustomerID == custID
select c).Count();
return(count);
}
}
public Customer GetCustomer(string custID)
{
using (var context = new NorthwindDataContext())
{
return (from c in context.Customers
where c.CustomerID == custID
select c).SingleOrDefault();
}
}
public override IEnumerable <Model.Order> GetOrders(string custID)
{
using (NorthwindDataContext context = DataContext)
{
IEnumerable <Order> orders = (from o in context.Orders
where o.CustomerID == custID
select o).ToArray <Order>();
return(orders);
}
}
public void Save(NorthwindDataContext context)
{
try
{
context.SubmitChanges();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
throw;
}
}
public override IEnumerable <OrderDescription> GetOrderDetails(int orderID)
{
//The following query tries to mirror the query in OrderDBLINQ but doesn't leverage data relationships
//in the object model. A simplified query is shown below.
//IEnumerable<OrderDescription> orders =
// context.Orders.Where(order => order.OrderID == orderID).
// Join(context.Shippers, o => o.ShipVia, s => s.ShipperID,
// (o, s) => new { o.OrderID, ShipCompanyName = s.CompanyName }).
// Join(context.OrderDetails, o => o.OrderID, od => od.OrderID,
// (o, od) => new {o.ShipCompanyName, od.ProductID, od.Quantity, od.UnitPrice }).
// Join(context.Products, od => od.ProductID, p => p.ProductID,
// (OrderDetails, p) => new { OrderDetails, p.ProductName, p.SupplierID }).
// Join(context.Suppliers, p => p.SupplierID, s => s.SupplierID,
// (OrderData, s) => new { OrderData, SupplierName = s.CompanyName}).
// Select(o => new OrderDescription
// {
// Product = o.OrderData.ProductName,
// Quantity = o.OrderData.OrderDetails.Quantity,
// ShipperName = o.OrderData.OrderDetails.ShipCompanyName,
// Total = o.OrderData.OrderDetails.Quantity * o.OrderData.OrderDetails.UnitPrice,
// UnitPrice = o.OrderData.OrderDetails.UnitPrice,
// SupplierName = o.SupplierName
// });
using (NorthwindDataContext context = DataContext)
{
IEnumerable <OrderDescription> orders =
context.Orders.Where(order => order.OrderID == orderID).
Join(context.OrderDetails, o => o.OrderID, od => od.OrderID,
(o, od) => new
{
ShipCompanyName = o.Shipper.CompanyName,
od.ProductID,
ProductName = od.Product.ProductName,
Quantity = od.Quantity,
UnitPrice = od.UnitPrice,
SupplierName = od.Product.Supplier.CompanyName
}).
Select(o => new OrderDescription
{
Product = o.ProductName,
Quantity = o.Quantity,
ShipperName = o.ShipCompanyName,
Total = o.Quantity * o.UnitPrice,
UnitPrice = o.UnitPrice,
SupplierName = o.SupplierName
}).ToArray <OrderDescription>();
return(orders);
}
}
private void simpleToolStripMenuItem_Click(object sender, EventArgs e)
{
var ctx = new NorthwindDataContext();
var sw = new StringWriter();
ctx.Log = sw;
var employees = from emp in ctx.Employees
where emp.LastName.StartsWith("D")
select emp;
dg.ItemsSource = employees;
MessageBox.Show(sw.GetStringBuilder().ToString());
}
public object GetProducts(int catId)
{
object productsOfSelectedCategory = null;
using (var context = new NorthwindDataContext())
{
var query = from p in context.LinqProducts
where p.CategoryID == catId
select p;
productsOfSelectedCategory = query.ToList();
}
return(productsOfSelectedCategory);
}
public bool DeleteProduct(int productId)
{
using (var context = new NorthwindDataContext())
{
var query = from p in context.LinqProducts
where p.ProductID == productId
select p;
var linqProduct = query.Single();
context.LinqProducts.DeleteOnSubmit(linqProduct);
Save(context);
}
return(true);
}
public bool AddProduct(Product product)
{
using (var context = new NorthwindDataContext())
{
var linqProduct = new LinqProduct();
linqProduct.ProductName = product.ProductName;
linqProduct.SupplierID = 1;
linqProduct.CategoryID = product.CategoryID;
linqProduct.QuantityPerUnit = product.QuantityPerUnit;
linqProduct.UnitPrice = product.UnitPrice;
linqProduct.UnitsInStock = (short)product.UnitsInStock;
linqProduct.UnitsOnOrder = (short)product.UnitsOnOrder;
linqProduct.ReorderLevel = 0;
linqProduct.Discontinued = product.Discontinued;
context.LinqProducts.InsertOnSubmit(linqProduct);
Save(context);
}
return(true);
}
public override IEnumerable <OrderDescription> GetOrderDetails(int orderID)
{
//SQL-ish way of doing it that's a little more complex than it should be
//IEnumerable<OrderDescription> orders =
// from o in context.Orders
// where o.OrderID == orderID
// join s in context.Shippers on o.ShipVia equals s.ShipperID
// join od in context.OrderDetails on o.OrderID equals od.OrderID
// join p in context.Products on od.ProductID equals p.ProductID
// join supplier in context.Suppliers on p.SupplierID equals supplier.SupplierID
// let total = od.Quantity * od.UnitPrice
// select new OrderDescription
// {
// Product = p.ProductName,
// Quantity = od.Quantity,
// ShipperName = s.CompanyName,
// Total = total,
// UnitPrice = od.UnitPrice,
// SupplierName = supplier.CompanyName
// };
using (NorthwindDataContext context = DataContext)
{
IEnumerable <OrderDescription> orderDetails =
(from o in context.Orders
where o.OrderID == orderID
from od in o.OrderDetails
let total = od.Quantity * od.UnitPrice
select new OrderDescription
{
Product = od.Product.ProductName,
Quantity = od.Quantity,
ShipperName = o.Shipper.CompanyName,
Total = total,
UnitPrice = od.UnitPrice,
SupplierName = od.Product.Supplier.CompanyName
}).ToArray <OrderDescription>();
return(orderDetails);
}
}
public override OperationStatus DeleteCustomer(Customer cust)
{
NorthwindDataContext context = null;
try
{
context = DataContext;
context.ap_DeleteCustomer(cust.CustomerID);
}
catch (Exception exp)
{
return(new OperationStatus(false, exp.Message, exp));
}
finally
{
if (context != null)
{
context.Dispose();
}
}
return(new OperationStatus(true, null, null));
}
public virtual OperationStatus InsertCustomer(Customer cust)
{
NorthwindDataContext context = null;
try
{
context = DataContext;
context.Customers.InsertOnSubmit(cust);
context.SubmitChanges();
}
catch (Exception exp)
{
return(new OperationStatus(false, exp.Message, exp));
}
finally
{
if (context != null)
{
context.Dispose();
}
}
return(new OperationStatus(true, null, null));
}