public static void ModifyContactName(string customerId, string contactName)
{
var northwind = new NorthwindEntities();
var customer = GetCustomerById(northwind, customerId);
customer.ContactName = contactName;
northwind.SaveChanges();
}
public static Customer GetCustomerById(NorthwindEntities northwindEntities, string customerID)
{
var customer = northwindEntities.Customers
.FirstOrDefault(c => c.CustomerID == customerID);
return customer;
}
public static void Main()
{
var firstConection = new NorthwindEntities();
var secondConection = new NorthwindEntities();
Console.WriteLine();
var firstCustomer = firstConection.Customers.First();
var secondCustomer = secondConection.Customers.First();
Console.WriteLine("Name from first connection: " + firstCustomer.CompanyName);
Console.WriteLine("Name from second connection: " + secondCustomer.CompanyName);
firstCustomer.CompanyName = "Google";
secondCustomer.CompanyName = "Microsoft";
//// What will happen at SaveChanges()? - second company name change will be implemented
//// How to deal with it? - either by using a single connection, or if not applicable - by introduction of transactions isolation levels
firstConection.SaveChanges();
secondConection.SaveChanges();
var result = new NorthwindEntities().Customers.First();
Console.WriteLine("Final company name {0}", result.CompanyName);
Console.WriteLine();
}
static void Main(string[] args)
{
// task - 7 - https://msdn.microsoft.com/en-us/data/jj592904.aspx
for (int i = 0; i < 3; i++)
{
var firstConection = new NorthwindEntities();
var secondConection = new NorthwindEntities();
Console.WriteLine();
var firstCustomer = firstConection.Customers.Find("TELER");
var secondCustomer = secondConection.Customers.Find("TELER");
Console.WriteLine("Name from first connection: " + firstCustomer.CompanyName);
Console.WriteLine("Name from second connection: " + secondCustomer.CompanyName);
firstCustomer.CompanyName = "TELERIK 1";
secondCustomer.CompanyName = "TELERIK 2";
secondConection.SaveChanges();
firstConection.SaveChanges();
var result = new NorthwindEntities().Customers.Find("TELER");
Console.WriteLine("Final company name {0}", result.CompanyName);
Console.WriteLine();
}
}
public static void DeleteCustomer(string customerId)
{
var northwind = new NorthwindEntities();
var customer = GetCustomerById(northwind, customerId);
northwind.Customers.Remove(customer);
northwind.SaveChanges();
}
public static void Main()
{
var northwindDb = new NorthwindEntities();
var twin = northwindDb.Database.CreateIfNotExists();
// will create the Northwind DB if it doesn't exist, otherwise it will not
Console.WriteLine("Was a Northwind twin created: {0}", twin ? "Yes" : "No");
}
// task 3 - Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
private static void FindCustomersByOrdersYearAndCountry(NorthwindEntities northwind, int year, string country)
{
northwind.Orders
.Where(o => o.OrderDate.Value.Year == year && o.ShipCountry == country)
.Select(ord => ord.Customer)
.GroupBy(c => c.CompanyName)
.ToList().ForEach(cust => Console.WriteLine("- " + cust.Key));
}
static void Main()
{
var db = new NorthwindEntities();
Employee emp = db.Employees.First();
var territories = emp.TerritoriesSet;
}
public static void DeleteCustomer(string customerID)
{
using (var northwindEntities = new NorthwindEntities())
{
Customer customer = GetCustomerById(northwindEntities, customerID);
northwindEntities.Customers.Remove(customer);
northwindEntities.SaveChanges();
}
}
public static void ModifyCustomerCompanyName(string customerID, string newCompanyName)
{
using (var northwindEntities = new NorthwindEntities())
{
var customer = GetCustomerById(northwindEntities, customerID);
customer.CompanyName = newCompanyName;
northwindEntities.SaveChanges();
}
}
public static void Main()
{
var db = new NorthwindEntities();
using (db)
{
Console.WriteLine("All categories: ");
db.Categories
.Select(c => c.CategoryName)
.ToList()
.ForEach(c => System.Console.WriteLine("* " + c));
}
}
public static string InsertNewCustomer(Customer customer)
{
if (customer == null)
{
throw new ArgumentNullException("Customer cannot be null");
}
using (var northwindEntities = new NorthwindEntities())
{
northwindEntities.Customers.Add(customer);
northwindEntities.SaveChanges();
return customer.CustomerID;
}
}
public static void Main()
{
var northwind = new NorthwindEntities();
using (northwind)
{
Console.WriteLine("Showing all products in Northwind DB: ");
Console.WriteLine();
northwind.Products
.Select(p => p.ProductName)
.ToList()
.ForEach(p => Console.WriteLine("* " + p));
}
}
// Task 5 - Write a method that finds all the sales by specified region and period (start / end dates).
private static void FindSalesByRegionAndTimePeriod(
NorthwindEntities northwind,
string region,
DateTime startDate,
DateTime endDate)
{
northwind.Orders
.Where(o => o.ShipRegion == region &&
o.OrderDate >= startDate &&
o.OrderDate <= endDate)
.Select(o => new { o.OrderID, o.ShipCity })
.ToList()
.ForEach(o => Console.WriteLine(string.Format("Order #{0} to {1}", o.OrderID, o.ShipCity)));
}
public static void Main()
{
using (var northwindEntities = new NorthwindEntities())
{
var employee = northwindEntities.Employees.First();
//// this is refering to the EmployeeExtended.cs file in the 01. CreateNorthwindContext Project
var territories = employee.TerritoriesSet;
Console.WriteLine("Employee {0} {1} has the following teritories:", employee.FirstName, employee.LastName);
Console.WriteLine(new string('-', 30));
foreach (var territory in territories)
{
Console.WriteLine(territory.TerritoryDescription);
}
}
}
// task 4 - Implement previous by using native SQL query and executing it through the DbContext.
private static void FindCustomersByOrdersYearAndCountryWithNativeSql(NorthwindEntities northwind, int year, string country)
{
string query = "SELECT c.CompanyName FROM Customers AS c " +
"JOIN Orders AS o " +
"ON c.CustomerId = o.CustomerId " +
"WHERE Country = '{0}' " +
"AND YEAR(OrderDate) = {1}" +
"GROUP BY c.CompanyName ";
object[] parameters = { country, year };
var customers = northwind.Database.SqlQuery<string>(string.Format(query, parameters));
foreach (var customer in customers)
{
Console.WriteLine("- " + customer);
}
}
public static void Main()
{
using (var northwindEntities = new NorthwindEntities())
{
var employee = northwindEntities.Employees.First();
// The new model for the Employee is in Task01.CreateDbContextForNorthwind
// in the file EmployeeExtended.cs
EntitySet territories = employee.TerritoriesSet;
Console.WriteLine("All territories for employee {0} are:", employee.FirstName);
foreach (var territory in territories)
{
Console.WriteLine(territory.TerritoryDescription);
}
}
}
public static void Main()
{
var northwind = new NorthwindEntities();
CustomerController.AddCustomer("TELER", "Telerik", "Qsdcds Fvdvsdv", "eqrwrqwer",
"asfasfsdff", "Qsadsad", "dsf", "1234", "Qwerwer", "+12345678", "+12345678");
CustomerController.ModifyCompanyName("TELER", "Telerik, Qwewer");
CustomerController.DeleteCustomer("TELER");
northwind.SaveChanges();
Console.WriteLine("Customers who shipped to Canada in 1997: ");
FindCustomersByOrdersYearAndCountry(northwind, 1997, "Canada");
FindCustomersByOrdersYearAndCountryWithNativeSql(northwind, 1997, "Canada");
Console.WriteLine("Orders shipped to Sao Paulo region between 18 and 19 years ago.");
FindSalesByRegionAndTimePeriod(northwind, "SP", DateTime.Now.AddYears(-19), DateTime.Now.AddYears(-18));
}
public static void Main()
{
var testCustomer = new Customer
{
CustomerID = "aaa",
CompanyName = "Telerik"
};
Console.WriteLine("Inserting a new Customer with Id {0} : ", testCustomer.CustomerID);
Console.WriteLine(new string('-', 30));
TestInsertingCustomer(testCustomer);
Console.WriteLine();
Console.WriteLine("Modifying Customer's Company {0} : ", testCustomer.CompanyName);
Console.WriteLine(new string('-', 30));
TestMoodifyingCustomer(testCustomer.CustomerID);
Console.WriteLine();
Console.WriteLine("Deleting Customer with Id {0} : ", testCustomer.CustomerID);
Console.WriteLine(new string('-', 30));
TestDeleteingCustomer(testCustomer.CustomerID);
Console.WriteLine();
var northwindDb = new NorthwindEntities();
Console.WriteLine("Displaying Customers who shipped to Canada in 1997: ");
Console.WriteLine(new string('-', 30));
FindCustomersByOrdersYearAndCountry(northwindDb, 1997, "Canada");
Console.WriteLine();
Console.WriteLine("Displaying Customers who shipped to Canada in 1997 (with native SQL): ");
Console.WriteLine(new string('-', 30));
FindCustomersByOrdersYearAndCountryWithNativeSql(northwindDb, 1997, "Canada");
Console.WriteLine();
Console.WriteLine("Displaying Orders shipped to Rio de Janeiro between 15 and 20 years ago.");
Console.WriteLine(new string('-', 30));
FindSalesByRegionAndTimePeriod(northwindDb, "RJ", DateTime.Now.AddYears(-20), DateTime.Now.AddYears(-15));
Console.WriteLine();
}
public static string AddCustomer(string customerId, string companyName, string contactName, string contactTitle,
string address, string city, string region, string postalCode, string country, string phone, string fax)
{
var northwind = new NorthwindEntities();
var customer = new Customer()
{
CustomerID = customerId,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax,
};
northwind.Customers.Add(customer);
northwind.SaveChanges();
return customer.CustomerID;
}
public static void Main()
{
var northwind = new NorthwindEntities();
northwind.Database.Create();
}
