public static void GetCustomerSql(int orderedYear, string destination)
{
using (var context = new NorthwindEntities())
{
var searchQuery =
@"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[] queryParams = { orderedYear, destination };
var searchResult = context.Database.SqlQuery<string>(searchQuery, queryParams);
foreach (var customer in searchResult)
{
Console.WriteLine("Customer: {0}", customer);
}
//var query = (from customer in context.Customers
// join order in context.Orders on customer.CustomerID equals order.CustomerID
// where order.OrderDate.Value.Year == 1994 && order.ShipCountry == "Canada"
// select customer).ToList();
}
}
public void AddCustomer(Customer customer)
{
using (var context = new NorthwindEntities())
{
context.Customers.Add(customer);
context.SaveChanges();
}
}
public void DeleteCustomerById(int id)
{
using (var context = new NorthwindEntities())
{
var customer = context.Customers.FirstOrDefault(c => c.CustomerID == id);
context.Customers.Remove(customer);
context.SaveChanges();
}
}
public Customer RemoveCustomer(Customer customer)
{
using (var context = new NorthwindEntities())
{
context.Customers.Remove(customer);
context.SaveChanges();
}
return customer;
}
public static void CustomersWithOrders(int orderedDate, string destination)
{
using (var context = new NorthwindEntities())
{
var query =
context.Orders.Where(o => o.OrderDate.Value.Year == orderedDate && o.ShipAddress == destination)
.Select(o => o.Customer);
foreach (var customer in query)
{
Console.WriteLine(customer.ContactName);
}
}
}
public static IQueryable<Order> FindSales(string region, DateTime start, DateTime end)
{
using (var context = new NorthwindEntities())
{
var query = context.Orders
.Where(c => c.ShipRegion == region && c.OrderDate >= start && c.OrderDate <= end).Select(o => o);
return query;
//foreach (var order in query)
//{
// Console.WriteLine("Ship name: {0}, Ship region: {1}", order.ShipName, order.ShipRegion);
//}
}
}
static void Main(string[] args)
{
using (var firstContext = new NorthwindEntities())
{
using (var secondContext = new NorthwindEntities())
{
firstContext.Customers.Add(new Customer { CustomerID = 5000, CompanyName = "CONFLICTTEST" });
secondContext.Customers.Add(new Customer {CustomerID = 6000, CompanyName = "CONFLICTTEST2"});
secondContext.SaveChanges();
firstContext.SaveChanges();
secondContext.SaveChanges();
}
}
}
public Customer ModifyCostomer(int id, string companyName)
{
using (var context = new NorthwindEntities())
{
var customer = context.Customers.SingleOrDefault(x => x.CustomerID == id);
if (customer!=null)
{
customer.CompanyName = companyName;
}
context.SaveChanges();
return customer;
}
}
public static void AddOrder(string shipName, short amount, int productID, decimal unitPrice, float discount)
{
using (NorthwindEntities dbContent = new NorthwindEntities())
{
using (TransactionScope scope = new TransactionScope())
{
try
{
Order newOrder = new Order { ShipName = shipName };
dbContent.Orders.Add(newOrder);
dbContent.SaveChanges();
var result = dbContent.Orders.Where(x => x.ShipName == shipName);
using (NorthwindEntities dbContext = new NorthwindEntities())
{
foreach (var item in result)
{
Order_Detail newDetail = new Order_Detail
{
OrderID = item.OrderID,
ProductID = productID,
UnitPrice = unitPrice,
Discount = discount,
Quantity = amount,
};
dbContext.Order_Details.Add(newDetail);
dbContext.SaveChanges();
}
}
scope.Complete();
}
catch (Exception e)
{
throw e;
}
}
}
}
static void Main(string[] args)
{
NorthwindEntities dbContent = new NorthwindEntities();
/*
* Steps to reproduse the DB:
* 1. Open SorceModel.edmx
* 2. Right button click on the empty space between the tables
* 3. Click Generate Database from model - it will generate a SQL script
* 4. Edit the USE [Northwind]; to the name of the database you want to clone the current DB to.
* 5. Run the script;
*/
// The connection string in App.config is changed
Console.Write("Loading...");
using (var northwindEntities = new NorthwindEntities())
{
northwindEntities.Database.CreateIfNotExists();
Console.WriteLine("\rNumbers of customers: " + northwindEntities.Customers.Count());
}
}
