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
};
}
//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);
}
}
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);
}
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);
}
}
public Customer GetCustomerByID(string id)
{
using (var contexto = new NorthWindDataContext())
{
return
((
from c in contexto.Customers
where c.CustomerID == id
select c
)
.FirstOrDefault());
}
}
public IList <string> GetCountries()
{
using (var contexto = new NorthWindDataContext())
{
return
((
from c in contexto.Customers
select c.Country
)
.Distinct()
.ToList());
}
}
public IList <Customer> GetCustomerByCountry(string country)
{
using (var contexto = new NorthWindDataContext())
{
return
((
from c in contexto.Customers
where c.Country == country
select c
)
.ToList());
}
}
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
};
}
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;
}
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;
}
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;
}
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";
}
// 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);
}
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;
}
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;
}
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;
}
public Uow(NorthWindDataContext context)
{
_context = context;
}
public ProductRepository(NorthWindDataContext context)
{
_context = context;
}
public OrderRepository(NorthWindDataContext context)
{
_context = context;
}
