/// <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: 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>());
}
/// <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: 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);
}
/// <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>
/// 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>
/// 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;
}
}
}
// 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>());
}
/// <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>());
}
/// <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>());
}