private static void FindCustomersSpecificOrders(string year, string country)
{
northwindEntities context = new northwindEntities();
int yearInt = int.Parse(year);
var orders =
from order in context.Orders
where (int)order.OrderDate.Value.Year == yearInt && order.ShipCountry == country
select order;
Console.WriteLine("Customer OrderedYear ShipCountry");
foreach (var item in orders)
{
Console.WriteLine("{0,-17} {1} {2}",
item.Customer.ContactName, item.OrderDate.Value.Year, item.ShipCountry);
}
}
private static void FindCustomersSpecificOrders_SQL(string year, string country)
{
northwindEntities context = new northwindEntities();
string sqlQuery = @"SELECT c.ContactName from Customers" +
" c INNER JOIN Orders o ON o.CustomerID = c.CustomerID " +
"WHERE (YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1});";
object[] parameters = { year, country };
var sqlQueryResult = context.Database.SqlQuery<string>(sqlQuery, parameters);
foreach (var order in sqlQueryResult)
{
Console.WriteLine(order);
}
}
private static void FindSales(string region, DateTime orderedDate, DateTime shippedDate)
{
northwindEntities context = new northwindEntities();
var sales =
from sale in context.Orders
where sale.ShipRegion == region &&
sale.OrderDate >= orderedDate &&
sale.ShippedDate <= shippedDate
select sale;
foreach (var item in sales)
{
Console.WriteLine(item.ShipName + " --> " + item.OrderDate);
}
}
static void Main(string[] args)
{
IObjectContextAdapter context = new northwindEntities();
string cloneNorthwind = context.ObjectContext.CreateDatabaseScript();
string createNorthwindCloneDB = "CREATE DATABASE NorthwindTwin ON PRIMARY " +
"(NAME = NorthwindTwin, " +
"FILENAME = 'E:\\NorthwindTwin.mdf', " +
"SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = NorthwindTwinLog, " +
"FILENAME = 'E:\\NorthwindTwin.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
SqlConnection dbConForCreatingDB = new SqlConnection(
"Server=LOCALHOST; " +
"Database=master; " +
"Integrated Security=true");
dbConForCreatingDB.Open();
using (dbConForCreatingDB)
{
SqlCommand createDB = new SqlCommand(createNorthwindCloneDB, dbConForCreatingDB);
createDB.ExecuteNonQuery();
}
SqlConnection dbConForCloning = new SqlConnection(
"Server=LOCALHOST; " +
"Database=NorthwindTwin; " +
"Integrated Security=true");
dbConForCloning.Open();
using (dbConForCloning)
{
SqlCommand cloneDB = new SqlCommand(cloneNorthwind, dbConForCloning);
cloneDB.ExecuteNonQuery();
}
}
private static void InsertCustomer(string id, string companyName, string contactName,
string contactTitle, string address, string city, string region,
string postalCode, string country, string phone, string fax)
{
northwindEntities context = new northwindEntities();
Customer customer = new Customer()
{
CustomerID = id,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax
};
context.Customers.Add(customer);
context.SaveChanges();
}
private static Customer GetCustomerById(string customerId, northwindEntities context)
{
Customer customer = context.Customers.Find(customerId);
return customer;
}
private static void DeleteCustomer(string customerId)
{
northwindEntities context = new northwindEntities();
Customer customer = GetCustomerById(customerId, context);
context.Customers.Remove(customer);
context.SaveChanges();
}
private static void ModifyCustomerName(string customerId, string customerName)
{
northwindEntities context = new northwindEntities();
Customer customer = GetCustomerById(customerId, context);
customer.ContactName = customerName;
context.SaveChanges();
}