// Решението на задачата е в EntityFramework.Data/Еxtended/Employee.
static void Main()
{
using (var db = new NorthwindEntities())
{
entitySet<T>
}
}
static void Edit(string id, string newContactName)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
customer.ContactName = newContactName;
db.SaveChanges();
}
}
static void Delete(string id)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
db.Customers.Remove(customer);
db.SaveChanges();
}
}
static void FindAllSalesByDateRange(string region, DateTime from, DateTime to)
{
using (var db = new NorthwindEntities())
{
var askedSales = db.Orders.Where(o => o.ShipRegion == region && o.OrderDate > from && o.OrderDate < to)
.Select(o => new { ShipName = o.ShipName, OrderDate = o.OrderDate });
foreach (var item in askedSales)
{
Console.WriteLine(item.ShipName + " - " + item.OrderDate);
}
}
}
private static void CreatingNewDataBase(NorthwindEntities northwindEntities)
{
var script= northwindEntities.Database.ExecuteSqlCommand("CREATE DATABASE [NorthwindTwin] \n GO \nUSE [NorthwindTwin] \n");
//string script = northwindEntities.CreateDatabaseScript();
//script = "CREATE DATABASE [NorthwindTwin] \n GO \nUSE [NorthwindTwin] \n" + script;
StreamWriter northwindTwinFile = new StreamWriter("NorthwindTwin.sql");
using (northwindTwinFile)
{
northwindTwinFile.WriteLine(script);
}
Console.WriteLine(script);
}
static void FindAllCustomers(int orderDate, string shipDestination)
{
using (var db = new NorthwindEntities())
{
var orders = from order in db.Orders
where order.OrderDate.Value.Year == orderDate && order.ShipCountry == shipDestination
select order;
foreach (var order in orders)
{
Console.WriteLine("Customer name: {0} , with CustomerId: {1}", order.Customer.ContactName, order.Customer.CustomerID);
}
}
}
static void FindAllCustomersWithNativeSQL(int orderDate, string country)
{
using (var db = 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 = { orderDate, country };
var sqlQueryResult = db.Database.SqlQuery<string>(sqlQuery, parameters);
foreach (var order in sqlQueryResult)
{
Console.WriteLine(order);
}
}
}
static IEnumerable<int> findSupplierIncome()
{
NorthwindEntities northwindEntities = new NorthwindEntities();
string nativeSQLQuery =
"select sum(od.UnitPrice * Quantity) as SupplierIncome " +
"from dbo.Suppliers as s " +
"inner join dbo.Products as p " +
"on p.SupplierID = s.SupplierID " +
"inner join dbo.[Order Details] as od " +
"on od.ProductID = p.ProductID " +
"inner join dbo.Orders as o " +
"on o.OrderID = od.OrderID " +
"where s.CompanyName = 'Tokyo Traders' and year(o.OrderDate) = 1996";
var income =
northwindEntities.ExecuteStoreQuery<int>(nativeSQLQuery);
return income;
}
static void Add(string name, string id)
{
Customer newCustomer = new Customer()
{
CompanyName = name,
CustomerID = id
};
using (var db = new NorthwindEntities())
{
bool isInDB = IsInDataBase(db, id);
if (!isInDB)
{
db.Customers.Add(newCustomer);
db.SaveChanges();
Console.WriteLine("Added Successful.");
}
else
{
throw new ArgumentException("Such customer already exists");
}
}
}
static bool IsInDataBase(NorthwindEntities db, string id)
{
bool alreadyInDB = db.Customers.Where(a => a.CustomerID == id).Any();
return alreadyInDB;
}
