public static void FindCustomers()
{
using(var dataBase = new NORTHWNDEntities())
{
// select the customers which ship country is Canada
// then find these which order date is in 1997
// order them by name
var customers = dataBase.Orders
.Where(ord => ord.ShipCountry == "Canada")
.Select(e => new
{
e.Customer.ContactName,
e.OrderDate
})
.ToList()
.FindAll(e => e.OrderDate >= Convert.ToDateTime("01/01/1997") &&
e.OrderDate < Convert.ToDateTime("01/01/1998"))
.OrderBy(e => e.ContactName);
// print customers name and order date
foreach (var customer in customers)
{
Console.WriteLine("{0,-20} | {1}", customer.ContactName, customer.OrderDate);
}
}
}
public static void FindAllSalesInGivenRegionAndPeriod(DateTime startDate, DateTime endDate, string region)
{
using (var dataBase = new NORTHWNDEntities())
{
var salesInGivenPeriod = dataBase.Order_Details
.Select(pr => new
{
pr.Product.ProductName,
pr.Quantity,
pr.UnitPrice,
pr.Order.OrderDate,
pr.Order.ShipRegion
})
.ToList()
.FindAll(s => s.OrderDate >= startDate && s.OrderDate <= endDate && s.ShipRegion == region);
Console.WriteLine("\nRegion | Product | Quantity | Unit Price | Date\n");
foreach (var sale in salesInGivenPeriod)
{
Console.WriteLine("{0,-3} | {1,-35} | {2,-3} | {3,-5:00:00} | {4}", sale.ShipRegion,
sale.ProductName,
sale.Quantity,
sale.UnitPrice,
sale.OrderDate);
}
}
}
static void Main()
{
using (var dataBase = new NORTHWNDEntities())
{
bool isCreated = dataBase.Database.CreateIfNotExists();
Console.WriteLine("The database was created: {0}", isCreated);
}
}
public static void DeleteCustomer(NORTHWNDEntities dataBase)
{
var customer = dataBase.Customers
.Where(c => c.CustomerID == "ABGRT")
.FirstOrDefault();
dataBase.Customers.Remove(customer);
dataBase.SaveChanges();
}
static void Main()
{
using (var dataBase = new NORTHWNDEntities())
{
InsertNewCustomer(dataBase);
ModifyCustomer(dataBase);
DeleteCustomer(dataBase);
}
}
public static void ModifyCustomer(NORTHWNDEntities dataBase)
{
// selecting the new inserted customer entry to modify it
var customer = dataBase.Customers
.Where(c => c.CustomerID == "ABGRT")
.FirstOrDefault();
customer.ContactName = "Miroslav Ivanov";
dataBase.SaveChanges();
}
static void Main()
{
// test if it works
using (var dataBase = new NORTHWNDEntities())
{
var employees = dataBase.Employees
.Select(e => new
{
Name = e.FirstName + " " + e.LastName
}).ToList();
foreach (var empl in employees)
{
Console.WriteLine(empl.Name);
}
}
}
public static void FindCustomersSqlQuery()
{
string query = "SELECT c.ContactName " +
"FROM Customers c " +
"JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE o.ShipCountry = 'Canada' AND o.OrderDate LIKE '%1997%' " +
"ORDER BY c.ContactName";
using (var dataBase = new NORTHWNDEntities())
{
var customers = dataBase.Database.SqlQuery<string>(query).ToList();
foreach (var customer in customers)
{
Console.WriteLine(customer);
}
}
}
public static void InsertNewCustomer(NORTHWNDEntities dataBase)
{
var newCustomer = new Customer
{
CustomerID = "ABGRT",
CompanyName = "Nike",
ContactName = "Miroslav Andonov",
ContactTitle = "Owner",
Address = "Mladost 4",
City = "Sofia",
Region = "Sofia",
PostalCode = "1612",
Country = "Bulgaria",
Phone = "3-432-213-4",
Fax = "3-324-213-12",
};
dataBase.Customers.Add(newCustomer);
dataBase.SaveChanges();
}
public void CorrespondingTerritories()
{
using (var db = new NORTHWNDEntities())
{
// do this foreach employee and it's territories
foreach (var employee in db.Employees.Include("Territories").ToList()) // i don't know why this work but not (e => e.Territories)
{ // if you can tell me i'll be happy. Thank you.
// select employee name and territory description
var correspondingTerritories = employee.Territories.Select(t => new
{
territories = t.TerritoryDescription,
Name = employee.FirstName + " " + employee.LastName
}).ToList();
// print the result
foreach (var emp in correspondingTerritories)
{
Console.WriteLine("{0} - {1}", emp.Name, emp.territories);
}
}
}
}
static void Main()
{
// creating one of the database contexts used to simulate the multithreading
using (var firstDataBase = new NORTHWNDEntities())
{
// selecting the first employee
var employee = firstDataBase.Employees.FirstOrDefault();
// changing the employee's country to Bulgaria
employee.Country = "Bulgaria";
//creating the second database
using (var secondDataBase = new NORTHWNDEntities())
{
// selecting the first employee again
var employeeSecond = secondDataBase.Employees.FirstOrDefault();
// changing the employee's country to England
employee.Country = "England";
secondDataBase.SaveChanges();
} // here the second connection is closed
firstDataBase.SaveChanges();
}// first connection is closed
// show the actual result
using (var dbToShowResult = new NORTHWNDEntities())
{
var result = dbToShowResult.Employees.FirstOrDefault();
Console.WriteLine("The first emplyee's country is: {0}", result.Country);
}
}
