/// <summary>
/// Example: Joins
/// Joining using the join keyword
///
/// The values are set to each of the
/// properties contained in the
/// OrdersAndDetailsResult class
///
/// The value returned is converted
/// to a list of the specified type
/// </summary>
/// <returns></returns>
public static List <OrdersAndDetailsResult> OrdersAndDetails()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return((from ords in dc.GetTable <Order>()
join dets in dc.GetTable <Order_Detail>()
on ords.OrderID equals dets.OrderID
orderby ords.CustomerID ascending
select new OrdersAndDetailsResult
{
CustomerID = ords.CustomerID,
OrderDate = ords.OrderDate,
RequiredDate = ords.RequiredDate,
ShipAddress = ords.ShipAddress,
ShipCity = ords.ShipCity,
ShipCountry = ords.ShipCountry,
ShipZip = ords.ShipPostalCode,
ShippedTo = ords.ShipName,
OrderID = ords.OrderID,
NameOfProduct = dets.Product.ProductName,
QtyPerUnit = dets.Product.QuantityPerUnit,
Price = dets.Product.UnitPrice,
QtyOrdered = dets.Quantity,
Discount = dets.Discount
}
).ToList <OrdersAndDetailsResult>());
}
/// <summary>
/// Example: Return an ordered list
///
/// Converts the returned value to a List
/// of type Employee; the list is ordered
/// by hire date
/// </summary>
/// <returns></returns>
public static List <Employee> GetEmployeesByHireDate()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return((from emp in dc.GetTable <Employee>()
orderby emp.HireDate ascending
select emp).ToList <Employee>());
}
/// <summary>
/// Example: Select to a typed List
/// using a Where Clause
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public static List <Order> GetOrdersById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return((from ord in dc.GetTable <Order>()
where (ord.OrderID == orderId)
select ord).ToList <Order>());
}
/// <summary>
/// Example: Select to a single returned object
/// using a Where Clause
///
/// Returns the first matching order
/// </summary>
/// <param name="orderId"></param>
/// <returns>The single matching or default value</returns>
public static Order GetOrderById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return((from ord in dc.GetTable <Order>()
where (ord.OrderID == orderId)
select ord).SingleOrDefault <Order>());
}
// This region contains examples of some
// of the sorts of queries that can be
// executed using LINQ to SQL
/// <summary>
/// Example: Where Clause
/// Returns an employee where the
/// employee ID matches the value
/// passed in as empID
/// </summary>
/// <param name="empId"></param>
/// <returns>The single matching or default value</returns>
public static Employee GetEmployeeById(int empId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return((from e in dc.GetTable <Employee>()
where (e.EmployeeID == empId)
select e).SingleOrDefault <Employee>());
}
/// <summary>
/// Example: Using Take to get a limited
/// number of returned values for display and
/// using Skip to sequence to a different
/// starting point within the returned values -
/// can be used to navigate through a large
/// list
/// </summary>
/// <param name="SkipNumber"></param>
/// <returns></returns>
public static List <Order> GetTopFiveOrdersById(int SkipNumber)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return((from ord in dc.GetTable <Order>()
orderby ord.OrderID ascending
select ord).Skip(SkipNumber).Take(5).ToList <Order>());
}
public IEnumerable <Order> GetOrdersOfCustomer(int customerId)
{
using (NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext())
{
return((from o in dc.Orders
where o.CustomerID == customerId
select o).ToList());
}
}
/// <summary>
/// Example: Aggregation
///
/// Returns the total sum of the order
/// selected by order ID by selecting
/// unit price multiplied by quantity
/// ordered and then calling sum for
/// the total
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public static decimal?GetOrderValueByOrderId(int orderID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matches =
(from od in dc.GetTable <Order_Detail>()
where od.OrderID == orderID
select od.Product.UnitPrice * od.Quantity).Sum();
return(matches);
}
public Customer GetCustomer(int customerId)
{
using (NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext())
{
DataLoadOptions options = new DataLoadOptions();
options.LoadWith <Customer>(c => c.Orders);
dc.LoadOptions = options;
return((from e in dc.Customers
where (e.CustomerID == customerId)
select e).SingleOrDefault());
}
}
/// <summary>
/// Delete a customer by customer ID
/// </summary>
/// <param name="customerID"></param>
public static void DeleteCustomer(string customerID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dc.GetTable <Customer>()
where c.CustomerID == customerID
select c).SingleOrDefault();
try
{
dc.Customers.DeleteOnSubmit(matchedCustomer);
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Example: Query across an entity ref
/// This example collections information from the orders table
/// and the order_details table through the orders table
/// entity association to the orders_details table.
///
/// An entity is a representation in the model of a table
/// in the database, foreign key relationships are maintained
/// as entity references to the related tables in the model.
/// It is possible to query across tables through this
/// relationship in LINQ to SQL
/// </summary>
/// <returns></returns>
public static List <OrderandPricingResult> GetOrderAndPricingInformation()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return((from ords in dc.Orders // orders table
from dets in ords.Order_Details // entity set in orders table
select new OrderandPricingResult
{
OrderID = ords.OrderID,
Company = ords.Customer.CompanyName,
OrderCountry = ords.Customer.Country,
ProductName = dets.Product.ProductName,
UnitPrice = dets.Product.UnitPrice,
UnitsOrder = dets.Quantity,
ShipperName = ords.Shipper.CompanyName,
SalesFirstName = ords.Employee.FirstName,
SalesLastName = ords.Employee.LastName,
SalesTitle = ords.Employee.Title
}).ToList <OrderandPricingResult>());
}
/// <summary>
/// Displays the full Order_Detail table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table <Order_Detail> GetOrderDetailsTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.GetTable <Order_Detail>());
}
// This section contains examples of
// pulling back entire tables from
// the database
/// <summary>
/// Displays the full Employee table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table <Employee> GetEmployeeTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.GetTable <Employee>());
}
public IEnumerable <Customer> GetCustomers()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.Customers);
}
/// <summary>
/// Displays the full Customer table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table <Customer> GetCustomerTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.GetTable <Customer>());
}
/// <summary>
/// Displays the full Region table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table <Region> GetRegionTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.GetTable <Region>());
}
/// <summary>
/// Displays Territory Table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table <Territory> GetTerritoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.GetTable <Territory>());
}
/// <summary>
/// Stored Procedure: Ten Most Expenisve Products
/// </summary>
/// <returns></returns>
public static List <Ten_Most_Expensive_ProductsResult> TenMostExpensiveProducts()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.Ten_Most_Expensive_Products().ToList <Ten_Most_Expensive_ProductsResult>());
}
/// <summary>
/// Stored Procedure: Sales By Year
/// </summary>
/// <param name="beginningYear"></param>
/// <param name="endingYear"></param>
/// <returns></returns>
public static List <Sales_by_YearResult> SalesByYear(DateTime?beginningYear, DateTime?endingYear)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.Sales_by_Year(beginningYear, endingYear).ToList <Sales_by_YearResult>());
}
/// <summary>
/// Insert or Update a Customer Record
///
/// If the customer ID exists, the existing
/// customer record is updated.
///
/// If the customer ID does not exist, the
/// new customer record is inserted into
/// the database
/// </summary>
/// <param name="customerId"></param>
/// <param name="companyName"></param>
/// <param name="contactName"></param>
/// <param name="contactTitle"></param>
/// <param name="address"></param>
/// <param name="city"></param>
/// <param name="region"></param>
/// <param name="postalCode"></param>
/// <param name="country"></param>
/// <param name="phone"></param>
/// <param name="fax"></param>
public static void InsertOrUpdateCustomer(string customerId, string companyName,
string contactName, string contactTitle, string address, string city,
string region, string postalCode, string country, string phone, string fax)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
var matchedCustomer = (from c in dc.GetTable <Customer>()
where c.CustomerID == customerId
select c).SingleOrDefault();
if (matchedCustomer == null)
{
try
{
// create new customer record since customer ID
// does not exist
Table <Customer> customers = Accessor.GetCustomerTable();
Customer cust = new Customer();
cust.CustomerID = customerId;
cust.CompanyName = companyName;
cust.ContactName = contactName;
cust.ContactTitle = contactTitle;
cust.Address = address;
cust.City = city;
cust.Region = region;
cust.PostalCode = postalCode;
cust.Country = country;
cust.Phone = phone;
cust.Fax = fax;
customers.InsertOnSubmit(cust);
customers.Context.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
else
{
try
{
matchedCustomer.CompanyName = companyName;
matchedCustomer.ContactName = contactName;
matchedCustomer.ContactTitle = contactTitle;
matchedCustomer.Address = address;
matchedCustomer.City = city;
matchedCustomer.Region = region;
matchedCustomer.PostalCode = postalCode;
matchedCustomer.Country = country;
matchedCustomer.Phone = phone;
matchedCustomer.Fax = fax;
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}
}
/// <summary>
/// Displays the full Product table
/// </summary>
/// <returns></returns>
public static System.Data.Linq.Table <Product> GetProductTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return(dc.GetTable <Product>());
}
