private void btnRetrieveById_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities())
{
ctx.ContextOptions.ProxyCreationEnabled = false;
var id = Convert.ToInt32(OrderId.Text);
var order = ctx.Orders.Include("OrderDetails.Product").FirstOrDefault(c => c.OrderId == id);
if (order == null)
{
MessageBox.Show("Order doesn't exist");
}
else
{
OrderId.Tag = order.Version;
cmbCustomers.SelectedItem = cmbCustomers.Items.Cast <Customer>().First(c => c.CompanyId == order.CustomerId);
ShippingAddress.Text = order.ShippingAddress.Address;
ShippingCity.Text = order.ShippingAddress.City;
ShippingCountry.Text = order.ShippingAddress.Country;
ShippingZipCode.Text = order.ShippingAddress.ZipCode;
OrderDate.Text = order.OrderDate.ToString("dd/MM/yyyy");
EstimatedShippingDate.Text = (order.EstimatedShippingDate.HasValue) ? order.EstimatedShippingDate.Value.ToString("dd/MM/yyyy") : String.Empty;
ActualShippindDate.Text = (order.ActualShippingDate.HasValue) ? order.ActualShippingDate.Value.ToString("dd/MM/yyyy") : String.Empty;
Details.Items.Clear();
foreach (var detail in order.OrderDetails)
{
Details.Items.Add(new ListViewItem(new string[] { detail.Product.Name, detail.Quantity.ToString(), detail.UnitPrice.ToString(), detail.Discount.ToString() })
{
Tag = detail
});
}
}
}
}
public void UpdateCustomer(Customer c)
{
using (OrderITEntities ctx = new OrderITEntities()) {
ctx.Companies.ApplyChanges(c);
ctx.SaveChanges();
}
}
public Supplier ReadSupplier(int supplierId)
{
using (OrderITEntities ctx = new OrderITEntities()) {
var su = ctx.Companies.OfType <Supplier>().First(s => s.CompanyId == supplierId);
return(su);
}
}
private void button19_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
Dump(from o in ctx.Orders
select new { o.OrderId, o.OrderDate, o.ShippingAddress, o.OrderDetails });
}
}
private void button34_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
Dump(from p in ctx.Products.OfType <Shoe>()
select p); //returns a list of Shoe
}
}
private void button5_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o WHERE COUNT(SELECT VALUE DISTINCT(d.product.productid) FROM o.OrderDetails AS d) > 1");
Dump(result);
}
}
private void button8_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT c.CompanyId, c.Name FROM OrderITEntities.Companies AS c");
Dump(result);
}
}
private void button22_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY o.ShippingAddress.City, o.ShippingAddress.ZipCode");
Dump(result);
}
}
private void button23_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY Sum(SELECT VALUE (d.UnitPrice - d.Discount) * d.Quantity FROM o.OrderDetails AS d)");
Dump(result);
}
}
private void button16_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, SUM(SELECT VALUE (d.UnitPrice - d.Discount) * d.Quantity FROM o.OrderDetails As d) AS Total FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button20_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.ShippingAddress.City, (SELECT VALUE o2 FROM OrderITEntities.Orders AS o2 WHERE o.ShippingAddress.City = o2.ShippingAddress.City) AS details FROM OrderITEntities.Orders AS o GROUP BY o.ShippingAddress.City HAVING COUNT(o.ShippingAddress.City) > 2");
Dump(result);
}
}
private void button15_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, (SELECT d.OrderDetailId, d.Product.ProductId, d.Quantity FROM o.OrderDetails As d) as Details FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button13_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, o.Customer.CompanyId, o.Customer.Name FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button10_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT (o.ShippingAddress.Address + '-' + o.ShippingAddress.City + '-' + o.ShippingAddress.Country + '-' + o.ShippingAddress.ZipCode) AS ShippingAddress FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button3_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders as o WHERE EXISTS (SELECT d FROM o.OrderDetails as d WHERE d.Product.Brand = 'MyBrand')");
Dump(result);
}
}
private void button24_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate, (Select VALUE d FROM o.OrderDetails AS d ORDER BY d.Quantity) AS Details FROM OrderITEntities.Orders AS o");
Dump(result);
}
}
private void button4_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders as o WHERE SUM(SELECT value d.discount FROM o.OrderDetails as d) = 0 ");
Dump(result);
}
}
private void button25_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY o.Customer.ShippingAddress.City");
Dump(result);
}
}
private void button7_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o ORDER BY o.orderid SKIP 5 LIMIT 5");
Dump(result);
}
}
private void button26_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <DbDataRecord>("SELECT o.OrderId, o.OrderDate FROM OrderITEntities.Orders AS o JOIN OrderITEntities.Companies AS c ON o.Customer.CompanyId = c.CompanyId AND o.ShippingAddress.City = c.ShippingCity");
Dump(result);
}
}
private void button4_Click(object sender, EventArgs e)
{
List <Company> items;
using (OrderITEntities ctx = new OrderITEntities()) {
items = ctx.Companies.Where(c => c.Name.StartsWith("Customer")).ToList();
}
_watch.Start("Update " + items.Count + " customers with ADO.NET");
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["OrderITEntitiesDB"].ConnectionString)) {
conn.Open();
using (SqlTransaction tr = conn.BeginTransaction()) {
try {
for (int i = 0; i < items.Count; i++)
{
using (SqlCommand comm = new SqlCommand("", conn, tr)) {
comm.CommandText = "update [dbo].[Company] set [Name] = @name where companyid = @companyid and version = @version ";
comm.Parameters.AddWithValue("name", items[i].Name + "New");
comm.Parameters.AddWithValue("companyid", items[i].CompanyId);
comm.Parameters.AddWithValue("version", items[i].Version);
comm.ExecuteNonQuery();
}
}
tr.Commit();
}
catch (Exception er) {
tr.Rollback();
}
}
}
_watch.Stop();
}
private void button27_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Customer>("SELECT VALUE c FROM OFTYPE(OrderITEntities.Companies, OrderIT.Model.Customer) AS c");
Dump(result);
}
}
private void button32_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
Dump(from p in ctx.Products
select p);
}
}
private void button29_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.Orders.Where("it.ShippingAddress.City = 'New York'");
Dump(result);
}
}
private void button44_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
Dump(from o in ctx.Orders.Include("OrderDetails")
select o);
}
}
private void button34_Click(object sender, EventArgs e)
{
using (EntityConnection conn = new EntityConnection(Parameters.ConnectionString)) {
using (EntityCommand comm = new EntityCommand("SELECT value o FROM OrderITEntities.OrderDetails AS o", conn)) {
conn.Open();
EntityDataReader reader = comm.ExecuteReader(CommandBehavior.SequentialAccess);
using (var ctx = new OrderITEntities()) {
textConsole.Text = String.Empty;
var x = ctx.Translate <OrderDetail>(reader).ToList();
var t = x[0].Discount;
//while (reader.Read()) {
//textConsole.Text += reader[0].ToString() + "-" + reader[1].ToString() + "-" + reader[2].ToString() + "\r\n";
//}
}
}
}
/*using (EntityConnection conn = new EntityConnection(Parameters.ConnectionString)) {
* using (EntityCommand comm = new EntityCommand("SELECT VALUE o FROM OrderITEntities.Orders AS o", conn)) {
* conn.Open();
* EntityDataReader reader = comm.ExecuteReader(CommandBehavior.SequentialAccess);
* textConsole.Text = String.Empty;
* while (reader.Read()) {
* textConsole.Text += reader[0].ToString() + "-" + reader[1].ToString() + "-" + reader[2].ToString() + "\r\n";
* }
* }
* }*/
}
public void UpdateOrder(Order o)
{
using (OrderITEntities ctx = new OrderITEntities()) {
ctx.Orders.ApplyChanges(o);
ctx.SaveChanges();
}
}
private void button2_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var result = ctx.CreateQuery <Order>("SELECT VALUE o FROM OrderITEntities.Orders AS o WHERE o.Customer.BillingAddress.City = 'New York'");
Dump(result);
}
}
private void button2_Click(object sender, EventArgs e)
{
Customer c = new Customer()
{
BillingAddress = new AddressInfo()
{
Address = "a",
City = "ci",
Country = "co",
ZipCode = "00000"
},
Email = "dddò[email protected]",
Name = "name",
ShippingAddress = new AddressInfo()
{
Address = "a",
City = "ci",
Country = "co",
ZipCode = "00000"
},
WSEnabled = false
};
var notification = new PersistenceNotification("username");
using (var ctx = new OrderITEntities(notification))
{
ctx.Companies.SmartAttach(c);
ctx.SaveChanges();
}
}
private void btnUpdateCustomerConnected_Click(object sender, EventArgs e)
{
using (var ctx = new OrderITEntities()) {
var id = Convert.ToInt32(CustomerId.Text);
var cust = ctx.Companies.OfType <Customer>().First(c => c.CompanyId == id);
cust.Name = CustomerName.Text;
cust.WSEnabled = WSEnabled.Checked;
cust.WSUserName = WSUsername.Text;
cust.WSPassword = WSPassword.Text;
cust.BillingAddress.Address = BillingAddress.Text;
cust.BillingAddress.City = BillingCity.Text;
cust.BillingAddress.Country = BillingCountry.Text;
cust.BillingAddress.ZipCode = BillingZipCode.Text;
cust.ShippingAddress.Address = ShippingAddress.Text;
cust.ShippingAddress.City = ShippingCity.Text;
cust.ShippingAddress.Country = ShippingCountry.Text;
cust.ShippingAddress.ZipCode = ShippingZipCode.Text;
try {
ctx.SaveChanges();
MessageBox.Show("Customer updated");
}
catch (OptimisticConcurrencyException ex) {
var errorEntry = ex.StateEntries.First();
ctx.Refresh(RefreshMode.ClientWins, errorEntry.Entity);
var form = new EntriesComparer(errorEntry);
form.ShowDialog();
if (form.ApplyChanges)
{
ctx.SaveChanges();
MessageBox.Show("Customer updated");
}
}
}
}