private static void FindAllCustomersByOrdersIn1997AndShippedToCanadaNative()
{
NorthwindEntities context = new NorthwindEntities();
string nativeSQLQuery =
"SELECT * FROM Customers c " +
"INNER JOIN Orders o " +
"ON c.CustomerID = o.CustomerID " +
"WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada' ";
var customersNative = context.Database.SqlQuery<Customer>(nativeSQLQuery);
foreach (var custNative in customersNative)
{
Console.WriteLine(custNative.ContactName);
}
context.Dispose();
}
private static void Main()
{
// 2.
var customer = new Customer
{
Address = "Perla 3",
City = "Sofia",
CompanyName = "PeshoEOOD",
ContactName = "Pesho",
ContactTitle = "Pesho",
Country = "Bulgaria",
CustomerID = "AAAAA",
Fax = "222/ 222222",
Phone = "089999999",
PostalCode = "1367",
Region = null,
};
DAO.InsertCustomer(customer);
// The last one in database
//DAO.DeleteCustomer("WOLZA");
var newCust = new Customer();
newCust.Address = "Pirotska 12";
// It must update only Address in concrete CustomerID
DAO.UpdateCustomer("AAAAA", newCust);
Console.WriteLine();
// 3.
var customers = FindAllCustomersByOrdersIn1997AndShippedToCanada();
foreach (var cust in customers)
{
Console.WriteLine(cust.ContactName);
}
Console.WriteLine();
// 4.
FindAllCustomersByOrdersIn1997AndShippedToCanadaNative();
Console.WriteLine();
// 5.
string region = "Lara";
DateTime startDate = new DateTime(1996, 10, 16);
DateTime endDate = new DateTime(1996, 11, 13);
var orders = FindAllOrdersRegionAndPeriod(region, startDate, endDate);
foreach (var order in orders)
{
Console.WriteLine(order.OrderDate + "->" + order.RequiredDate + "->" + order.ShipRegion);
}
Console.WriteLine();
// 6. Use SQL Server Management Objects (SMO) library installed with NuGet Packages
// Change server name for your computer
var server = new Server(@"DESKTOP-CTR5RI6\SQLEXPRESS");
Database newdb = new Database(server, "NorthwindTwin");
newdb.Create();
Transfer transfer = new Transfer(server.Databases["Northwind"]);
transfer.CopyAllObjects = true;
transfer.CopyAllUsers = true;
transfer.Options.WithDependencies = true;
transfer.DestinationDatabase = newdb.Name;
transfer.DestinationServer = server.Name;
transfer.DestinationLoginSecure = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.TransferData();
// 7.
var db1 = new NorthwindEntities();
var db2 = new NorthwindEntities();
var customer1 = db1.Customers.Where(cust => cust.ContactName == "Ana Trujillo").FirstOrDefault();
var customer2 = db1.Customers.Where(cust => cust.ContactName == "Ana Trujillo").FirstOrDefault();
customer1.ContactName = "Pesho";
customer2.ContactName = "Gosho";
db1.SaveChanges();
db2.SaveChanges();
db1.Dispose();
db2.Dispose();
//Concurrency control
//Entity Framework uses optimistic concurrency control (no locking by default)
//Provides automatic concurrency conflict detection and means for conflicts resolution
// 8. EmployeeExtension
}