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