/// <summary>
/// Exemplo: Aggregation
/// Retorna o total sa soma dos pedidos
/// selecionado por ID
/// computando o preco unitário multiplicado pela quantidade
/// </summary>
/// <param name="orderID"></param>
/// <returns></returns>
public static decimal?GetOrderValueByOrderId(int orderID)
{
NorthwindDataContext dc = new
NorthwindDataContext();
var matches =
(from od in dc.GetTable <Order_Detail>()
where od.OrderID == orderID
select od.Product.UnitPrice * od.Quantity).Sum();
return(matches);
}
private void gettableButton_Click(object sender, EventArgs e)
{
System.Data.Linq.Table <Employee> emp = db.GetTable <Employee>();
dataGridView1.DataSource = emp;
//System.Data.Linq.Table<Shipper> ship = db.GetTable<Shipper>();
//dataGridView1.DataSource = ship;
//System.Data.Linq.Table<Order> orders = db.GetTable<Order>();
//dataGridView1.DataSource = orders;
//...
}
private void cascadeButton_Click(object sender, EventArgs e)
{
using (NorthwindDataContext dc = new NorthwindDataContext())
{
var q =
(from c in dc.GetTable <Customer>()
where c.CustomerID == "AAAAA"
select c).Single <Customer>();
foreach (Order ord in q.Orders)
{
dc.GetTable <Order>().DeleteOnSubmit(ord);
foreach (Order_Detail od in ord.Order_Details)
{
dc.GetTable <Order_Detail>().DeleteOnSubmit(od);
}
}
dc.GetTable <Customer>().DeleteOnSubmit(q);
dc.SubmitChanges();
}
}
static void CreateTable(NorthwindDataContext db)
{
db.CreateTableIfNotExists <Person>();
db.DeleteAll <Person>();
db.InsertWithInt32Identity(new Person {
FirstName = "John", LastName = "Smith", Age = 30
});
db.InsertWithInt32Identity(new Person {
FirstName = "Jim", LastName = "Johnson"
});
db.DumpQuery(db.GetTable <Person>());
}
/// <summary>
/// Deleta um registro da tabela usando o ID do cliente
/// </summary>
/// <param name="customerID"></param>
public static void DeleteCustomer(string customerID)
{
NorthwindDataContext dc = new NorthwindDataContext();
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;
}
}
private void createButton_Click(object sender, EventArgs e)
{
//Creating a new Customer
Customer c1 = new Customer();
c1.CustomerID = "AAAAA";
c1.Address = "554 Westwind Avenue";
c1.City = "Wichita";
c1.CompanyName = "Holy Toledo";
c1.ContactName = "Frederick Flintstone";
c1.ContactTitle = "Boss";
c1.Country = "USA";
c1.Fax = "316-335-5933";
c1.Phone = "316-225-4934";
c1.PostalCode = "67214";
c1.Region = "EA";
Order_Detail od = new Order_Detail();
od.Discount = .25f;
od.ProductID = 1;
od.Quantity = 25;
od.UnitPrice = 25.00M;
Order o = new Order();
o.Order_Details.Add(od);
o.Freight = 25.50M;
o.EmployeeID = 1;
o.CustomerID = "AAAAA";
c1.Orders.Add(o);
using (NorthwindDataContext dc = new NorthwindDataContext())
{
var table = dc.GetTable <Customer>();
table.InsertOnSubmit(c1);
dc.SubmitChanges();
}
}
/// <summary>
/// Inserir ou Atualizar um registro da tabela Customer
/// Se o ID do Cliente existir então o registro é atualizado
/// caso contrário um novo registro será inserido na tabela
/// </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)
{
NorthwindDataContext dc = new NorthwindDataContext();
var matchedCustomer = (from c in dc.GetTable <Customer>()
where c.CustomerID == customerId
select c).SingleOrDefault();
if (matchedCustomer == null)
{
try
{
// cria um novo registro customer pois o ID não existe
Table <Customer> customers = acessoLinqTabelas.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;
}
}
}