public static void Main()
{
string regionName = "RJ";
DateTime startDate = new DateTime(1997, 1, 1);
DateTime endDate = new DateTime(1998, 1, 1);
using (var northwindEntities = new NorthwindEntities())
{
var sales =
from finishedOrder in northwindEntities.Orders.Include("Order Details").Include("Products")
where ((finishedOrder.ShipRegion == regionName) && (finishedOrder.ShippedDate != null) &&
(DateTime.Compare(startDate, finishedOrder.ShippedDate.Value) <= 0)
&& (DateTime.Compare(finishedOrder.ShippedDate.Value, endDate) <= 0))
select new
{
OrderDate = finishedOrder.OrderDate,
ShippedDate = finishedOrder.ShippedDate,
CompanyName = finishedOrder.Customer.CompanyName,
Details = finishedOrder.Order_Details,
};
int counter = 0;
foreach (var order in sales)
{
counter++;
Console.WriteLine(GetOrderString(order));
}
Console.WriteLine(Environment.NewLine + counter + " finished sales found.");
}
}
public static void Main()
{
// The script for generating the stored procedure is in the main folder for this project - 10.CreateStoredProcedure.
// Warning: you must map the procedure to the name GetSupplierIncome via the database model: right-click on the model -
// update, right-click again - add new - function import. I am enclosing (just in case) a script with my current version
// of the database with the procedure in it in the project folder - you must still map though.
using (NorthwindEntities northwindEntites = new NorthwindEntities())
{
Console.WriteLine((double)northwindEntites.GetSupplierIncome(1, new DateTime(1996, 7, 15), new DateTime(1996, 8, 24)).FirstOrDefault());
}
}
static void Main()
{
using (var northwindEntities = new NorthwindEntities())
{
StringBuilder scriptToExecute = new StringBuilder();
scriptToExecute.AppendLine("Use NorthwindTwin");
scriptToExecute.AppendLine((northwindEntities as IObjectContextAdapter).ObjectContext.CreateDatabaseScript());
northwindEntities.Database.ExecuteSqlCommand("CREATE DATABASE NorthwindTwin");
northwindEntities.Database.ExecuteSqlCommand(scriptToExecute.ToString());
}
}
public static void DeleteCustomer(string customerId)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
Customer customerToDelete = northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerId);
if (customerToDelete == null)
{
throw new System.Data.EntityException("There is no such customer.");
}
northwindEntities.Customers.Remove(customerToDelete);
northwindEntities.SaveChanges();
}
public static void Main(string[] args)
{
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
foreach (var entity in northwindEntities.Employees)
{
foreach (var item in entity.TerritoriesEntitySet)
{
Console.WriteLine(item.TerritoryDescription);
}
}
}
}
public static void Main()
{
NorthwindEntities northwindEntities = new NorthwindEntities();
string nativeSqlQuery = "SELECT DISTINCT c.CompanyName + ' represented by ' + c.ContactName " +
"FROM dbo.Customers c JOIN dbo.Orders o ON c.CustomerID = o.CustomerID " +
"WHERE o.ShipCountry = {0} AND o.OrderDate IS NOT NULL AND YEAR(o.OrderDate) = {1}";
object[] parameters = { "Canada", 1997 };
var customers = northwindEntities.Database.SqlQuery<string>(nativeSqlQuery, parameters);
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
public static void Main()
{
NorthwindEntities firstContext = new NorthwindEntities();
NorthwindEntities secondContext = new NorthwindEntities();
ModifyCustomer(firstContext, "ALFKI", city: "Moscow");
ModifyCustomer(secondContext, "ALFKI", city: "London");
// Since database is not locked, the last modification will be the one to remain. According to MSDN:
// http://msdn.microsoft.com/en-us/library/bb739065.aspx SaveChanges(boolean) is obsolete and should
// not be used.
firstContext.SaveChanges();
secondContext.SaveChanges();
firstContext.Dispose();
secondContext.Dispose();
}
public static void ModifyCustomer(NorthwindEntities northwindEntities, string customerId, string companyName = null, string contactName = null, string address = null,
string city = null, string region = null, string postalCode = null, string country = null, string phone = null,
string fax = null)
{
Customer customerToModify = northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerId);
if (customerToModify == null)
{
throw new System.Data.EntityException("No such customer exists.");
}
customerToModify.CompanyName = string.IsNullOrEmpty(companyName) ? customerToModify.CompanyName : companyName;
customerToModify.ContactName = string.IsNullOrEmpty(contactName) ? customerToModify.ContactName : contactName;
customerToModify.Address = string.IsNullOrEmpty(address) ? customerToModify.Address : address;
customerToModify.City = string.IsNullOrEmpty(city) ? customerToModify.City : city;
customerToModify.Region = string.IsNullOrEmpty(region) ? customerToModify.Region : region;
customerToModify.PostalCode = string.IsNullOrEmpty(postalCode) ? customerToModify.PostalCode : postalCode;
customerToModify.Country = string.IsNullOrEmpty(country) ? customerToModify.Country : country;
customerToModify.Phone = string.IsNullOrEmpty(phone) ? customerToModify.Phone : phone;
customerToModify.Fax = string.IsNullOrEmpty(fax) ? customerToModify.Fax : fax;
}
public static void InsertCustomer(string customerId, string companyName, string contactName = null, string address = null,
string city = null, string region = null, string postalCode = null, string country = null, string phone = null,
string fax = null)
{
NorthwindEntities northwindEntities = new NorthwindEntities();
int numberOfCustomersBefore =
(from customer in northwindEntities.Customers
select customer).Count();
Customer customerToInsert = new Customer
{
CustomerID = customerId,
CompanyName = companyName,
Address = address,
City = city,
PostalCode = postalCode,
Region = region,
Country = country,
Phone = phone,
Fax = fax
};
northwindEntities.Customers.Add(customerToInsert);
northwindEntities.SaveChanges();
int numberOfCustomersAfter =
(from customer in northwindEntities.Customers
select customer).Count();
if (numberOfCustomersAfter - numberOfCustomersBefore == 1)
{
Console.WriteLine("Customer added successfully.");
}
else
{
throw new System.Data.EntityException("Incorrect insertion result.");
}
}
static void Main()
{
NorthwindEntities northwindEntities = new NorthwindEntities();
DateTime date = DateTime.Now;
var customers =
from order in northwindEntities.Orders
where ((order.OrderDate != null) && (order.OrderDate.Value.Year == 1997)
&& (order.ShipCountry == "Canada"))
select new
{
CompanyName = order.Customer.CompanyName,
ContactName = order.Customer.ContactName
};
// This is done so that the same names are not listed more than once.
customers = customers.Distinct();
foreach (var customer in customers)
{
Console.WriteLine("Company name is {0} and contact name is {1}", customer.CompanyName, customer.ContactName);
}
}
public static void InsertOrder(int shipperId, string customerId, int employeeId, string destionation)
{
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
try
{
using (TransactionScope scope = new TransactionScope())
{
Order orderToInsert = new Order
{
CustomerID = customerId,
EmployeeID = employeeId,
OrderDate = DateTime.Now,
RequiredDate = DateTime.Now.Add(new TimeSpan(100, 0, 0, 0)),
ShipVia = shipperId,
Freight = 3000000,
ShipCountry = destionation,
Customer = northwindEntities.Customers.FirstOrDefault(c => c.CustomerID == customerId),
Employee = northwindEntities.Employees.FirstOrDefault(e => e.EmployeeID == employeeId),
Shipper = northwindEntities.Shippers.FirstOrDefault(s => s.ShipperID == shipperId),
Order_Details = new HashSet<Order_Detail>()
};
orderToInsert.Order_Details.Add(new Order_Detail
{
ProductID = 2,
Quantity = 34,
Discount = 1,
Order = orderToInsert
});
orderToInsert.Order_Details.Add(new Order_Detail
{
ProductID = 3,
Quantity = 70,
Discount = 1,
Order = orderToInsert
});
orderToInsert.Order_Details.Add(new Order_Detail
{
ProductID = 4,
Quantity = 110,
Discount = 1,
Order = orderToInsert
});
northwindEntities.Orders.Add(orderToInsert);
northwindEntities.SaveChanges();
scope.Complete();
//This ensures that the transaction has been completed.
int orderId = orderToInsert.OrderID;
Console.WriteLine(orderId);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
