public static void AddCustomer(string id, string name, string title, string phone,
string fax, string address, string postalCode, string city, string region,
string country, string company)
{
using (NorthwindEntities northwind = new NorthwindEntities())
{
Customer customer = new Customer();
customer.CustomerID = id;
customer.ContactName = name;
customer.ContactTitle = title;
customer.Phone = phone;
customer.Fax = fax;
customer.Address = address;
customer.PostalCode = postalCode;
customer.City = city;
customer.Region = region;
customer.Country = country;
customer.CompanyName = company;
if (northwind.Customers.Find(id) == null)
{
northwind.Customers.Add(customer);
}
northwind.SaveChanges();
}
}
public static void DeleteCustomer(string id)
{
using (NorthwindEntities northwind = new NorthwindEntities())
{
Customer customerToDelete = northwind.Customers.Find(id);
northwind.Customers.Remove(customerToDelete);
northwind.SaveChanges();
}
}
public static string GetCustomerNameById(string id)
{
using (NorthwindEntities northwind = new NorthwindEntities())
{
var customer = northwind.Customers.Find(id);
if (customer != null)
{
return customer.ContactName;
}
else
{
return string.Empty;
}
}
}
public static void ModifyCustomer(string id, string name, string title, string phone,
string fax, string address, string postalCode, string city, string region,
string country, string company)
{
using (NorthwindEntities northwind = new NorthwindEntities())
{
Customer customerToEdit = northwind.Customers.Find(id);
customerToEdit.ContactName = name;
customerToEdit.ContactTitle = title;
customerToEdit.Phone = phone;
customerToEdit.Fax = fax;
customerToEdit.Address = address;
customerToEdit.PostalCode = postalCode;
customerToEdit.City = city;
customerToEdit.Region = region;
customerToEdit.Country = country;
customerToEdit.CompanyName = company;
northwind.Entry(customerToEdit).State = System.Data.EntityState.Modified;
northwind.SaveChanges();
}
}
public static void Main()
{
#region Problem 2 - Add, Modify and Delete
NorthwindDAO.AddCustomer("ALABA", "Pesho Peshov", "Owner",
"555-123123", "555-123124", "Dolno uino", "1000", "Sofia",
"Sofia-grad", "Bulgaria", "McDonalds");
var customerName = NorthwindDAO.GetCustomerNameById("ALABA");
Console.WriteLine("Customer name will be blank if no entry found.");
Console.WriteLine("Name for customer with id ALABA: {0}", customerName);
NorthwindDAO.ModifyCustomer("ALABA", "Gosho Goshov", "Employee",
"555-123123", "555-123124", "Gorno uino", "9999", "Pleven",
"Plovdiv", "Bulgaria", "KFC");
customerName = NorthwindDAO.GetCustomerNameById("ALABA");
Console.WriteLine("Name for customer with id ALABA: {0}", customerName);
NorthwindDAO.DeleteCustomer("ALABA");
customerName = NorthwindDAO.GetCustomerNameById("ALABA") ?? "";
Console.WriteLine("Name for customer with id ALABA: {0}", customerName);
#endregion
#region Problem 3 - Get by year and location
var customerNames = NorthwindDAO.GetCustomersByYearAndLocation("Canada", 1997);
foreach (var name in customerNames)
{
Console.WriteLine(name);
}
#endregion
#region Problem 4 - Native SQL query
var queryResult = NorthwindDAO.ExecuteQuery("SELECT c.ContactName FROM Customers c " +
"INNER JOIN Orders o ON o.CustomerId = c.CustomerId " +
"WHERE (YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1})", new object[] { 1997, "Canada" }).Distinct();
foreach (var result in queryResult)
{
Console.WriteLine(result);
}
#endregion
#region Problem 5 - All sales by region and period
var orders = NorthwindDAO.SalesByRegionAndDate("CA",
new DateTime(1000, 1, 1), new DateTime(2000, 1, 1));
foreach (var order in orders)
{
Console.WriteLine(order);
}
#endregion
#region Problem 6 - Twin database
string createNorthwindCloneDBCommand = @"CREATE DATABASE NorthwindTwin ON PRIMARY
(NAME = NorthwindTwin, FILENAME = 'D:\NorthwindTwin.mdf', SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%)
LOG ON (NAME = NorthwindTwinLog, FILENAME = 'D:\NorthwindTwin.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%)";
SqlConnection dbConnectionForCreatingDB =
new SqlConnection("Server=localhost;Database=master;Integrated Security=true");
dbConnectionForCreatingDB.Open();
using (dbConnectionForCreatingDB)
{
SqlCommand createDBCommand =
new SqlCommand(createNorthwindCloneDBCommand, dbConnectionForCreatingDB);
createDBCommand.ExecuteNonQuery();
}
IObjectContextAdapter northwindContext = new NorthwindEntities();
string cloneNorthwindScript = northwindContext.ObjectContext.CreateDatabaseScript();
SqlConnection dbConnectionForCloningDB = new SqlConnection("Server=localhost;Database=NorthwindTwin;Integrated Security=true");
dbConnectionForCloningDB.Open();
using (dbConnectionForCloningDB)
{
SqlCommand cloneDBCommand = new SqlCommand(cloneNorthwindScript, dbConnectionForCloningDB);
cloneDBCommand.ExecuteNonQuery();
}
Console.WriteLine("Cloning done!");
#endregion
#region Problem 7 - Two dbcontext - solved using Singleton pattern
Console.WriteLine("Using double context...");
NorthwindDAO.DoubleContext();
Console.WriteLine("Done");
#endregion
#region Problem 8 - Employee inheritance
NorthwindDAO.Inheritance();
#endregion
#region Problem 9 -
NorthwindDAO.CreateOrder();
Console.WriteLine("Order added");
#endregion
}
public static IEnumerable<string> GetCustomersByYearAndLocation(string country, int year)
{
using (NorthwindEntities northwind = new NorthwindEntities())
{
var customers = northwind.Customers.Where(c =>
c.Orders.Where(o =>
o.OrderDate.Value.Year == year &&
o.ShipCountry == country).Any()
);
IEnumerable<string> names = customers.Select(x => x.ContactName).ToList();
return names;
}
}
public static void CreateOrder()
{
NorthwindEntities northwind = new NorthwindEntities();
using (northwind)
{
var order = new Order()
{
CustomerID = "BOTTM",
EmployeeID = 6,
ShipCity = "Sofia",
ShipCountry = "Bulgaria",
ShippedDate = DateTime.Now,
ShipPostalCode = "1000",
ShipVia = 2
};
var orderDetail1 = new Order_Detail()
{
Order = order,
ProductID = 23,
Quantity = 10,
UnitPrice = 15
};
var orderDetail2 = new Order_Detail()
{
Order = order,
ProductID = 27,
Quantity = 11,
UnitPrice = 19
};
northwind.Orders.Add(order);
northwind.Order_Details.Add(orderDetail1);
northwind.Order_Details.Add(orderDetail2);
northwind.SaveChanges();
}
}
public static void Inheritance()
{
NorthwindEntities northwindEntities = new NorthwindEntities();
int employeeID = 1;
Employee employee = northwindEntities.Employees.Find(employeeID);
// Property added at Northwind.Data ExtendedEmployee file
EntitySet<Territory> territories = employee.EntityTerritories;
Console.WriteLine("All territories for employee with ID {0} are:", employeeID);
foreach (var territory in territories)
{
Console.WriteLine(territory.TerritoryDescription);
}
}
public static void DoubleContext()
{
using (NorthwindEntities northwindEntities1 = new NorthwindEntities())
{
using (NorthwindEntities northwindEntities2 = new NorthwindEntities())
{
Customer customerByFirstDataContext = northwindEntities1.Customers.Find("CHOPS");
customerByFirstDataContext.Region = "SW";
Customer customerBySecondDataContext = northwindEntities2.Customers.Find("CHOPS");
customerBySecondDataContext.Region = "SSWW";
northwindEntities1.SaveChanges();
northwindEntities2.SaveChanges();
}
}
}
public static void CreateNewDatabase(NorthwindEntities northwind)
{
// TODO
}
public static IEnumerable<string> SalesByRegionAndDate(string region, DateTime start, DateTime end)
{
using (NorthwindEntities northwind = new NorthwindEntities())
{
var result = northwind.Orders.Where(o =>
o.ShipRegion == region &&
o.OrderDate.Value > start &&
o.OrderDate.Value < end).Select(x =>
"Contact name: " + x.Customer.ContactName + " Ship address: " +
x.ShipAddress).ToList();
return result;
}
}
public static IEnumerable<string> ExecuteQuery(string query, object[] parameters)
{
using (NorthwindEntities northwind = new NorthwindEntities())
{
var queryResult = northwind.Database.SqlQuery<string>(query, parameters).ToList();
return queryResult;
}
}