Esempio n. 1
0
        private void Form2_Load(object sender, EventArgs e)
        {
            NorthWindDataContext dc = new NorthWindDataContext();

            dataGridView1.DataSource = from product2 in dc.Products
                                       join orderdetail in dc.Order_Details on
                                       product2.ProductID equals orderdetail.ProductID
                                       join order in dc.Orders on
                                       orderdetail.OrderID equals order.OrderID
                                       join customer in dc.Customers on
                                       order.CustomerID equals customer.CustomerID
                                       join employee in dc.Employees on
                                       order.EmployeeID equals employee.EmployeeID
                                       join supplier in dc.Suppliers on
                                       product2.SupplierID equals supplier.SupplierID
                                       join category2 in dc.Categories on
                                       product2.CategoryID equals category2.CategoryID
                                       select new
            {
                product2.ProductName,
                category2.CategoryName,
                Supplier = supplier.CompanyName,
                product2.UnitPrice,
                Order_UnitPrice = orderdetail.UnitPrice,
                orderdetail.Quantity,
                orderdetail.Discount,
                Customer  = customer.CompanyName,
                Employees = employee.FirstName + " " + employee.LastName
            };
        }
Esempio n. 2
0
 //my code
 public List <string> getCountries()
 {
     using (var Context = new NorthWindDataContext())
     {
         List <string> myList = (from data in Context.Customers select data.Country).Distinct().ToList();
         return(myList);
     }
 }
Esempio n. 3
0
        private void Form1_Load(object sender, EventArgs e)
        {
            var db = new NorthWindDataContext();

            dataGridView1.DataSource = from o in db.Orders
                                       join od in db.Order_Details on o.OrderID equals od.OrderID
                                       join p in db.Products on od.ProductID equals p.ProductID
                                       select new { o.OrderDate, p.ProductName , od.Quantity, od.UnitPrice };
            //Procedure
            //dataGridView1.DataSource = db.CustOrdersDetail(10248);
        }
Esempio n. 4
0
        public List <Customer> getAllCustomer(string Country)
        {
            using (var Context = new NorthWindDataContext())
            {
                List <Customer> allCustomers = (from data in Context.Customers
                                                where data.Country == Country
                                                select data).ToList();

                return(allCustomers);
            }
        }
Esempio n. 5
0
 public Customer GetCustomerByID(string id)
 {
     using (var contexto = new NorthWindDataContext())
     {
         return
             ((
                  from c in contexto.Customers
                  where c.CustomerID == id
                  select c
                  )
              .FirstOrDefault());
     }
 }
Esempio n. 6
0
 public IList <string> GetCountries()
 {
     using (var contexto = new NorthWindDataContext())
     {
         return
             ((
                  from c in contexto.Customers
                  select c.Country
                  )
              .Distinct()
              .ToList());
     }
 }
Esempio n. 7
0
        public IList <Customer> GetCustomerByCountry(string country)
        {
            using (var contexto = new NorthWindDataContext())
            {
                return
                    ((
                         from c in contexto.Customers
                         where c.Country == country
                         select c

                         )
                     .ToList());
            }
        }
Esempio n. 8
0
        private void Form1_Load(object sender, EventArgs e)
        {
            NorthWindDataContext dc = new NorthWindDataContext();

            dataGridView1.DataSource = from product in dc.Products
                                       join category in dc.Categories on
                                       product.CategoryID equals category.CategoryID
                                       select new
            {
                product.ProductName,
                category.CategoryName,
                product.UnitPrice,
                product.UnitsInStock
            };
        }
Esempio n. 9
0
        private void FormListSales_Load(object sender, EventArgs e)
        {
            NorthWindDataContext dc = new NorthWindDataContext();

            /* var result = dc.Orders.Where(x => x.EmployeeID == EmployeeId && x.CustomerID==CustomerId
             * && x.ShipVia==ShippingId); */

            /* var result = dc.Orders.Where(x => x.EmployeeID == EmployeeId && x.CustomerID == CustomerId
             * && x.ShipVia == ShippingId).Select(x => new
             * {
             *  x.OrderID,
             *  x.EmployeeID,
             *  x.CustomerID,
             *  x.ShipVia,
             *  x.ShipName
             * }); */


            var result = dc.Orders.Join(
                dc.Employees,
                ord => ord.EmployeeID,
                emp => emp.EmployeeID,
                (order, employee) => new { order, employee }).Join(
                dc.Customers,
                ord => ord.order.CustomerID,
                cus => cus.CustomerID,
                (order, customer) => new
            {
                order.order.OrderID,
                order.order.OrderDate,
                EMployee = order.employee.FirstName + " " + order.employee.LastName,
                customer.CompanyName,
                order.order.CustomerID,
                order.order.EmployeeID,
                //order.order.CustomerID
            }).Where(x => x.EmployeeID == EmployeeId && x.CustomerID == CustomerId).Select(
                x => new
            {
                x.OrderID,
                x.OrderDate,
                x.EMployee,
                x.CompanyName,
                x.CustomerID
            });


            dataGridViewListSales.DataSource = result;
        }
Esempio n. 10
0
        private void Form4_Load(object sender, EventArgs e)
        {
            NorthWindDataContext ctx = new NorthWindDataContext();

            var result = from order in ctx.Orders
                         join employee in ctx.Employees on
                         order.EmployeeID equals employee.EmployeeID
                         group order by employee.FirstName into Group
                         select new
            {
                EMPLOYEE_NAME  = Group.Key,
                TOTAL_QUANTITY = Group.Count()
            };

            dataGridView1.DataSource = result;
        }
Esempio n. 11
0
        private void CustomerSalesPaper_Load(object sender, EventArgs e)
        {
            NorthWindDataContext dc = new NorthWindDataContext();

            var result = (from order in dc.Orders
                          join detail in dc.Order_Details on order.OrderID equals detail.OrderID
                          select new
            {
                order,
                detail
            }).GroupBy(x => x.order.CustomerID).Select(x => new
            {
                CustomerID  = x.Key,
                TotalTaking = x.Sum(y => y.detail.Quantity * y.detail.UnitPrice),
                TotalDeal   = x.Count(),
            }).OrderByDescending(x => x.CustomerID).Take(10);

            dataGridView1.DataSource = result;
        }
Esempio n. 12
0
        private void FormOrder_Load(object sender, EventArgs e)
        {
            NorthWindDataContext dc = new NorthWindDataContext();

            dataGridViewProducts.DataSource = dc.Products;

            comboBoxCustomer.DataSource    = dc.Customers;
            comboBoxCustomer.DisplayMember = "CompanyName";
            comboBoxCustomer.ValueMember   = "CustomerID";

            comboBoxShipping.DataSource    = dc.Shippers;
            comboBoxShipping.DisplayMember = "CompanyName";
            comboBoxShipping.ValueMember   = "ShipperID";

            // Lambda
            comboBoxEmployee.DataSource = dc.Employees.Select(employee => new
            {
                employee.EmployeeID,
                NameSurname = employee.FirstName + " " + employee.LastName
            });
            comboBoxEmployee.DisplayMember = "NameSurname";
            comboBoxEmployee.ValueMember   = "EmployeeID";
        }
Esempio n. 13
0
        // Using ADO.net
        public DataSet FireADOQuery()
        {
            // First, the connection details
            //string source = "server=(local);" + "integrated security=SSPI;" +
            //                "database=northwind";

            NorthWindDataContext dc = new NorthWindDataContext();   // Used the dbml way (Linq to SQL class)
            string connectionString = dc.Connection.ConnectionString;
            string query;

            if (selectAll)
            {
                if (condition != "")
                {
                    query = "SELECT * FROM Products WHERE " + condition;
                }
                else
                {
                    query = "SELECT * FROM Products";
                }
            }
            else
            {
                query = "SELECT " + column1 + ", " + column2 +
                        " FROM Products" +
                        " WHERE " + condition;
            }

            SqlConnection con = new SqlConnection(connectionString);

            // Using SQLDataAdapter to fill the DataSet
            SqlDataAdapter da = new SqlDataAdapter(query, con);
            DataSet        ds = new DataSet();

            da.Fill(ds);
            return(ds);
        }
Esempio n. 14
0
        private void Form3_Load(object sender, EventArgs e)
        {
            NorthWindDataContext dc = new NorthWindDataContext();

            var result = from product in dc.Products
                         join orderdetail in dc.Order_Details on
                         product.ProductID equals orderdetail.ProductID
                         group orderdetail by product.ProductName into Group
                         select new
            {
                ProductName = Group.Key,
                TotalSales  = Group.Sum(x => x.UnitPrice * x.Quantity)
            };

            var result2 = from product in dc.Products
                          select new
            {
                product.ProductName,
                TotalSales = product.Order_Details.Any()?
                             product.Order_Details.Sum(x => x.UnitPrice * x.Quantity):0
            };

            dataGridView1.DataSource = result2;
        }
Esempio n. 15
0
        private void btnApproveOrder_Click(object sender, EventArgs e)
        {
            if (comboBoxCustomer == null || comboBoxEmployee == null || comboBoxShipping == null)
            {
                MessageBox.Show("Missing Information");
                return;
            }

            NorthWindDataContext dc = new NorthWindDataContext();

            Order neworder = new Order();

            neworder.OrderDate  = DateTime.Now;
            neworder.CustomerID = comboBoxCustomer.SelectedValue.ToString();
            neworder.EmployeeID = (int)comboBoxEmployee.SelectedValue;
            neworder.ShipVia    = (int)comboBoxShipping.SelectedValue;

            dc.Orders.InsertOnSubmit(neworder);

            dc.SubmitChanges();

            foreach (ListViewItem item in listViewProducts.Items)
            {
                Order_Detail orderdetail = new Order_Detail();
                orderdetail.OrderID   = neworder.OrderID;
                orderdetail.ProductID = (int)item.Tag;
                orderdetail.UnitPrice = decimal.Parse(item.SubItems[1].Text);
                orderdetail.Quantity  = short.Parse(item.SubItems[2].Text);
                orderdetail.Discount  = float.Parse(item.SubItems[3].Text) / 100;
                dc.Order_Details.InsertOnSubmit(orderdetail);
                dc.SubmitChanges();
            }

            listViewProducts.Items.Clear();
            comboBoxCustomer.SelectedIndex = comboBoxEmployee.SelectedIndex = comboBoxShipping.SelectedIndex = -1;
        }
Esempio n. 16
0
        static IEnumerable GetSalesPersonData(string filter)
        {
            NorthWindDataContext dc = new NorthWindDataContext();

            return(dc.Invoices.Where(i => string.IsNullOrEmpty(filter) || i.Country == filter));
        }
 public CustomerRepository(NorthWindDataContext context)
 {
     _context = context;
 }
Esempio n. 18
0
 public Uow(NorthWindDataContext context)
 {
     _context = context;
 }
Esempio n. 19
0
 public ProductRepository(NorthWindDataContext context)
 {
     _context = context;
 }
Esempio n. 20
0
 public OrderRepository(NorthWindDataContext context)
 {
     _context = context;
 }