Esempio n. 1
0
        /// <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>());
        }
Esempio n. 2
0
        /// <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>());
        }
Esempio n. 3
0
        /// <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>());
        }
Esempio n. 4
0
        /// <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>());
        }
Esempio n. 5
0
        // 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>());
        }
Esempio n. 6
0
        /// <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());
     }
 }
Esempio n. 8
0
        /// <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());
            }
        }
Esempio n. 10
0
        /// <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;
            }
        }
Esempio n. 11
0
        /// <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>());
        }
Esempio n. 12
0
        /// <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>());
        }
Esempio n. 13
0
        // 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);
        }
Esempio n. 15
0
        /// <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>());
        }
Esempio n. 16
0
        /// <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>());
        }
Esempio n. 17
0
        /// <summary>
        /// Displays Territory Table
        /// </summary>
        /// <returns></returns>
        public static System.Data.Linq.Table <Territory> GetTerritoryTable()
        {
            NorthWindDataClassesDataContext dc = new NorthWindDataClassesDataContext();

            return(dc.GetTable <Territory>());
        }
Esempio n. 18
0
        /// <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>());
        }
Esempio n. 19
0
        /// <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>());
        }
Esempio n. 20
0
        /// <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;
                }
            }
        }
Esempio n. 21
0
        /// <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>());
        }