Accessor.cs

///

/// This class defines functions used to
/// select, insert, update, and delete data
/// using LINQ to SQL and the defined
/// data context
///

public class Accessor
{


#region Full Table

// This section contains examples of
// pulling back entire tables from
// the database

///

/// Displays the full Employee table
///

///
public static System.Data.Linq.Table GetEmployeeTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full Shipper table
///

///
public static System.Data.Linq.Table GetShipperTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full Order table
///

///
public static System.Data.Linq.Table GetOrderTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full EmployeeTerritory table
///

///
public static System.Data.Linq.Table GetEmployeeTerritoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays Territory Table
///

///
public static System.Data.Linq.Table GetTerritoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full Region table
///

///
public static System.Data.Linq.Table GetRegionTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full Customer table
///

///
public static System.Data.Linq.Table GetCustomerTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full CustomerCustomerDemo table
///

///
public static System.Data.Linq.Table GetCustomerDemoTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full CustomerDemographic table
///

///
public static System.Data.Linq.Table GetCustomerDemographicTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full Order_Detail table
///

///
public static System.Data.Linq.Table GetOrderDetailsTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full Product table
///

///
public static System.Data.Linq.Table GetProductTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full Supplier table
///

///
public static System.Data.Linq.Table GetSupplierTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


///

/// Displays the full Category table
///

///
public static System.Data.Linq.Table GetCategoryTable()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.GetTable();
}


#endregion



#region Queries

// This region contains examples of some
// of the sorts of queries that can be
// executed using LINQ to SQL

///

/// Example: Where Clause
/// Returns an employee where the
/// employee ID matches the value
/// passed in as empID
///

///
/// The single matching or default value
public static Employee GetEmployeeById(int empId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

return (from e in dc.GetTable()
where (e.EmployeeID == empId)
select e).SingleOrDefault();
}



///

/// Example: Select to a single returned object
/// using a Where Clause
///
/// Returns the first matching order
///

///
/// The single matching or default value
public static Order GetOrderById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

return (from ord in dc.GetTable()
where (ord.OrderID == orderId)
select ord).SingleOrDefault();
}



///

/// Example: Select to a typed List
/// using a Where Clause
///

///
///
public static List GetOrdersById(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

return (from ord in dc.GetTable()
where (ord.OrderID == orderId)
select ord).ToList();
}



///

/// Example: Return an ordered list
///
/// Converts the returned value to a List
/// of type Employee; the list is ordered
/// by hire date
///

///
public static List GetEmployeesByHireDate()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return (from emp in dc.GetTable()
orderby emp.HireDate ascending
select emp).ToList();
}




///

/// This class is used to define the return type
/// for the next function - OrdersAndDetails
///
/// When results are extracted from multiple tables
/// you can either return the results as anonymous
/// or as a type; this class defines the return
/// type used by OrdersAndDetails
///

public class OrdersAndDetailsResult
{
public System.String CustomerID
{ get; set; }
public System.Nullable OrderDate
{ get; set; }
public System.Nullable RequiredDate
{ get; set; }
public System.String ShipAddress
{ get; set; }
public System.String ShipCity
{ get; set; }
public System.String ShipCountry
{ get; set; }
public System.String ShipZip
{ get; set; }
public System.String ShippedTo
{ get; set; }
public System.Int32 OrderID
{ get; set; }
public System.String NameOfProduct
{ get; set; }
public System.String QtyPerUnit
{ get; set; }
public System.Nullable Price
{ get; set; }
public System.Int16 QtyOrdered
{ get; set; }
public System.Single Discount
{ get; set; }
}



///

/// 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
///

///
public static List OrdersAndDetails()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

return (from ords in dc.GetTable()
join dets in dc.GetTable()
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 ();
}


///

/// Defined to support following function:
/// GetOrderAndPricingInformation - this class
/// supplies the return type for that function
///

public class OrderandPricingResult
{
public System.Int32 OrderID
{ get; set; }
public System.String Company
{ get; set; }
public System.String OrderCountry
{ get; set; }
public System.String ProductName
{ get; set; }
public System.Nullable UnitPrice
{ get; set; }
public System.Int16 UnitsOrder
{ get; set; }
public System.String ShipperName
{ get; set; }
public System.String SalesFirstName
{ get; set; }
public System.String SalesLastName
{ get; set; }
public System.String SalesTitle
{ get; set; }
}


///

/// 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
///

///
public static List 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>();
}



///

/// Example: Query across entity ref with Where class
/// Same as previous function with added where clause
///
/// 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
///

///
///
public static List GetOrderAndPricingInformationByOrderId(int orderId)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

return (from ords in dc.Orders // orders table
from dets in ords.Order_Details // entity set in orders table
where ords.OrderID == orderId
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();
}


///

/// 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
///

///
///
public static decimal? GetOrderValueByOrderId(int orderID)
{

NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

var matches =
(from od in dc.GetTable()
where od.OrderID == orderID
select od.Product.UnitPrice * od.Quantity).Sum();

return matches;

}



///

/// 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
///

///
///
public static List GetTopFiveOrdersById(int SkipNumber)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

return (from ord in dc.GetTable()
orderby ord.OrderID ascending
select ord).Skip(SkipNumber).Take(5).ToList();
}



#endregion



#region Inserting, Updating, Deleting Data


///

/// 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
///

///
///
///
///
///
///
///
///
///
///
///
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()
where c.CustomerID == customerId
select c).SingleOrDefault();

if(matchedCustomer == null)
{
try
{
// create new customer record since customer ID
// does not exist
Table 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;
}
}

}


///

/// Delete a customer by customer ID
///

///
public static void DeleteCustomer(string customerID)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

var matchedCustomer = (from c in dc.GetTable()
where c.CustomerID == customerID
select c).SingleOrDefault();

try
{
dc.Customers.DeleteOnSubmit(matchedCustomer);
dc.SubmitChanges();
}
catch (Exception ex)
{
throw ex;
}
}


#endregion



#region Stored Procedures


///

/// Stored Procedure: Sales By Year
///

///
///
///
public static List SalesByYear(DateTime? beginningYear, DateTime? endingYear)
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.Sales_by_Year(beginningYear, endingYear).ToList();
}



///

/// Stored Procedure: Ten Most Expenisve Products
///

///
public static List TenMostExpensiveProducts()
{
NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();
return dc.Ten_Most_Expensive_Products().ToList();

}


#endregion


}