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 decimal? GetTotalIncomes(string supplierName, DateTime? startDate, DateTime? endDate)
{
using (var dbContext = new NorthwindEntities())
{
var totalIncome = dbContext
.usp_GetTotalIncome(supplierName, startDate, endDate)
.First();
return totalIncome;
}
}
public static void Main(string[] args)
{
using (var dbContext = new NorthwindEntities())
{
var totalIncome = GetTotalIncomes(
"Pavlova, Ltd.",
new DateTime(1990, 1, 1),
new DateTime(2000, 1, 1));
Console.WriteLine(totalIncome);
}
}
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 ICollection<Customer> FilterCustomersByOrders(int year, string shippedTo)
{
using (var dbContext = new NorthwindEntities())
{
var customers = dbContext
.Orders
.Where(x => x.OrderDate.Value.Year == 1997)
.Where(x => x.ShipCountry == "Canada")
.Select(x => x.Customer);
return customers.ToList();
}
}
public static void Main()
{
using (var dbContext = new NorthwindEntities())
{
var createQuery = "CREATE DATABASE NorthwindTwin";
dbContext.Database.ExecuteSqlCommand(createQuery);
var generatedScript = ((IObjectContextAdapter)dbContext)
.ObjectContext.CreateDatabaseScript();
var fillQuery = "USE NorthwindTwin " + generatedScript;
dbContext.Database.ExecuteSqlCommand(fillQuery);
}
}
public static void Main()
{
// Check EmployeeExtension class in Models project
using (var context = new NorthwindEntities())
{
var employee = context.Employees.Find(5);
foreach (var territory in employee.CorrespondingTerritories)
{
Console.WriteLine(
"Corresponding territories - {0}",
territory.TerritoryDescription);
}
}
}
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);
}
}
}
public static ICollection<Customer> FilterCustomersByOrders(int year, string shippedTo)
{
using (var dbContext = new NorthwindEntities())
{
object[] param = { year, shippedTo };
string query = "SELECT * " +
"FROM Orders o " +
"JOIN Customers c " +
"ON o.CustomerID = c.CustomerID " +
"WHERE YEAR(o.OrderDate) = {0} " +
"AND o.ShipCountry = {1}";
var customers = dbContext.Database.SqlQuery<Customer>(query, param);
return customers.ToList();
}
}
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 Main()
{
using (var dbContext = new NorthwindEntities())
{
var customer = new Customer()
{
CustomerID = "ZAZA",
CompanyName = "Mad House LTD",
ContactName = "Mad Man",
ContactTitle = "Owner",
};
DataAccessUtils.InsertCustomer(customer);
customer.Country = "Bulgaria";
DataAccessUtils.ModifyCustomer(customer);
DataAccessUtils.DeleteCustomer(customer.CustomerID);
}
}
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.");
}
}
public static ICollection<Order> FilterOrdersByRegionAndPeriod(string region, DateTime periodFrom, DateTime periodTo)
{
using (var dbContext = new NorthwindEntities())
{
var orders = dbContext
.Orders
.Where(x => x.OrderDate >= periodFrom)
.Where(x => x.OrderDate <= periodTo);
if (region == null)
{
orders = orders
.Where(x => x.ShipRegion == null);
}
else
{
orders = orders
.Where(x => x.ShipRegion == region);
}
return orders.ToList();
}
}
