static void FindCustomers(DateTime year, string country)
{
using (NorthwindEntities northwindDBContext = new NorthwindEntities())
{
//var customers = northwindDBContext.Orders
// .Where(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada")
// .GroupBy(o => o.Customer.CompanyName)
// .ToList();
var customers =
from customer in northwindDBContext.Customers
join order in northwindDBContext.Orders
on customer.CustomerID equals order.CustomerID
where order.ShipCountry == country && order.OrderDate.Value.Year == year.Year
select new
{
CustomerID = customer.CustomerID,
OrderDate = order.OrderDate,
ShipCountry = order.ShipCountry
};
northwindDBContext.SaveChanges();
foreach (var customer in customers)
{
Console.WriteLine("{0} ordered on {1} from {2}", customer.CustomerID, customer.OrderDate, customer.ShipCountry);
}
}
}
public static void InsertCustomer(Customer customer)
{
using (var dbContext = new NorthwindEntities())
{
dbContext.Customers.Add(customer);
dbContext.SaveChanges();
}
}
public static void DeleteCustomer(string customerID)
{
using (NorthwindEntities northwindDBContext = new NorthwindEntities())
{
Customer customer = northwindDBContext.Customers.First(x => x.CustomerID == customerID);
northwindDBContext.Customers.Remove(customer);
northwindDBContext.SaveChanges();
Console.WriteLine("Customer is deleted.");
}
}
public static void InsertCustomer(string customerID, string companyName)
{
using (NorthwindEntities northwindDBContext = new NorthwindEntities())
{
Customer customer = new Customer
{
CustomerID = customerID,
CompanyName = companyName,
};
northwindDBContext.Customers.Add(customer);
northwindDBContext.SaveChanges();
Console.WriteLine("Customer is inserted");
}
}
public static void CustomersWithOrdersIn1997FromCanada_SQLQuery()
{
using (NorthwindEntities northwindDBContext = new NorthwindEntities())
{
var customers = northwindDBContext.Database.SqlQuery<string>(@"SELECT c.CompanyName FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada'
GROUP BY c.CompanyName");
northwindDBContext.SaveChanges();
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
}
private static bool InsertOrders(int retries, Order[] orders)
{
var success = false;
var dbContext = new NorthwindEntities();
// Retry loop.
for (int i = 0; i < retries; i++)
{
using (var transaction = new TransactionScope())
{
try
{
foreach (var order in orders)
{
dbContext.Orders.Add(order);
}
dbContext.SaveChanges();
transaction.Complete();
success = true;
break;
}
catch (Exception ex)
{
if (ex.GetType() != typeof(UpdateException))
{
Console.WriteLine(
"An error occured. The operation cannot be retried. {0}",
ex.Message);
break;
}
}
}
}
if (success)
{
((IObjectContextAdapter)dbContext)
.ObjectContext.AcceptAllChanges();
}
dbContext.Dispose();
return success;
}
public static void FindSalesBySpecifiedRegionAndPeriod(string region = null, string startDate = null, string endDate = null)
{
using (NorthwindEntities northwindDBContext = new NorthwindEntities())
{
DateTime startDateParsed = DateTime.Parse(startDate);
DateTime endDateParsed = DateTime.Parse(endDate);
var customers = northwindDBContext.Orders
.Where(o => (o.OrderDate > startDateParsed && o.OrderDate < endDateParsed) || o.ShipCountry == region)
.GroupBy(o => o.ShipName);
northwindDBContext.SaveChanges();
foreach (var customer in customers)
{
Console.WriteLine(customer.Key);
}
}
}
public static void DeleteCustomer(string customerID)
{
using (var dbContext = new NorthwindEntities())
{
var customer = dbContext
.Customers
.Where(x => x.CustomerID == customerID)
.FirstOrDefault();
if (customer == null)
{
throw new ArgumentNullException(
"No such user in database!");
}
dbContext.Customers.Remove(customer);
dbContext.SaveChanges();
}
}
public static void Main()
{
using (var firstContext = new NorthwindEntities())
using (var secondContext = new NorthwindEntities())
{
firstContext
.Customers
.Find("FOLKO")
.ContactName = "Svetlin";
secondContext
.Customers
.Find("FOLKO")
.ContactName = "Nakov";
firstContext.SaveChanges();
secondContext.SaveChanges();
}
}
public static void UpdateCustomer(string customerID, Customer newCustomer)
{
using (NorthwindEntities northwindDBContext = new NorthwindEntities())
{
Customer customer = northwindDBContext.Customers.First(x => x.CustomerID == customerID);
customer.CompanyName = newCustomer.CompanyName;
customer.ContactName = newCustomer.ContactName;
customer.ContactTitle = newCustomer.ContactTitle;
customer.Address = newCustomer.Address;
customer.City = newCustomer.City;
customer.Region = newCustomer.Region;
customer.PostalCode = newCustomer.PostalCode;
customer.Country = newCustomer.Country;
customer.Phone = newCustomer.Phone;
customer.Fax = newCustomer.Fax;
northwindDBContext.SaveChanges();
Console.WriteLine("Customer is updated.");
}
}