public static string AddCustomer(NorthwindEntities northwind, Customer customer)
{
northwind.Customers.Add(customer);
northwind.SaveChanges();
return "Customer added";
}
/// <summary>
/// Problem solved by only using 1 db connection or introducing transactions isolation levels
/// </summary>
public static void Main()
{
using (var db1 = new NorthwindEntities())
{
using (var db2 = new NorthwindEntities())
{
Console.WriteLine("First connection altering");
Console.WriteLine("Original -> " + db1.Employees.First().City);
db1.Employees.First().City = "Changed city";
Console.WriteLine("Changed -> " + db1.Employees.First().City);
Console.WriteLine("Second connection altering");
Console.WriteLine("Original -> " + db2.Employees.First().City);
db2.Employees.First().City = "Altered city";
Console.WriteLine("Changed -> " + db2.Employees.First().City);
db1.SaveChanges();
db2.SaveChanges();
}
}
using (var db3 = new NorthwindEntities())
{
Console.WriteLine("Final connection altering");
Console.WriteLine(db3.Employees.First().City);
}
}
public static void Main()
{
IObjectContextAdapter dataBase = new NorthwindEntities();
string cloneNorthwind = dataBase.ObjectContext.CreateDatabaseScript();
SqlConnection connection = new SqlConnection("Server=.\\; " +
"Database=master; Integrated Security=true");
connection.Open();
using (connection)
{
SqlCommand createDatabase = new SqlCommand(
"USE master; " +
"CREATE DATABASE NorthwindTwin; " +
"SELECT name, size, size*1.0/128 AS [Size in MBs] " +
"FROM sys.master_files " +
"WHERE name = N'NorthwindTwin'; ",
connection);
createDatabase.ExecuteNonQuery();
string changeDB = "USE NorthwindTwin; ";
SqlCommand useNewDatabase = new SqlCommand(changeDB, connection);
useNewDatabase.ExecuteNonQuery();
SqlCommand cloneDatabase = new SqlCommand(cloneNorthwind, connection);
cloneDatabase.ExecuteNonQuery();
}
}
public static void Main()
{
using (var northwind1 = new NorthwindEntities())
{
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = IsolationLevel.Serializable;
using (var scope = new TransactionScope(TransactionScopeOption.Required, options))
{
using (var northwind2 = new NorthwindEntities())
{
Customer customer = northwind1.Customers.FirstOrDefault();
customer.Country = "NewCountry";
Customer customer2 = northwind2.Customers.FirstOrDefault();
northwind1.SaveChanges();
Console.WriteLine("First connection country ->" + customer.Country);
Console.WriteLine("Second connection country ->" + customer2.Country);
Console.WriteLine("The second connection works with the old data - dirty read.");
scope.Complete();
}
}
}
}
static void Main()
{
using (NorthwindEntities db = new NorthwindEntities())
{
int year = 1997;
string country = "Canada";
object[] parameters = {year, country};
string queryString = @"SELECT DISTINCT cust.ContactName AS [ContactName],
cust.CompanyName AS [CompanyName]
FROM Orders AS ord
INNER JOIN Customers AS cust
ON ord.CustomerID = cust.CustomerID
WHERE YEAR(ord.OrderDate) = {0} AND
cust.Country = {1}";
Type outputType = typeof(Result);
var customers = db.Database.SqlQuery(outputType, queryString, parameters);
foreach (Result item in customers)
{
Console.WriteLine("{0,-30} | {1,-30}", item.CompanyName, item.ContactName);
}
}
}
public static void Main()
{
using (var northwind = new NorthwindEntities())
{
Customer customer = new Customer();
customer.Address = "155, Madison Avenue";
customer.City = "New York";
customer.CompanyName = "Company";
customer.Country = "United States";
customer.CustomerID = "US123";
customer.ContactName = "James Peterson";
Console.WriteLine(InsertModifyDeleteCustomers.AddCustomer(northwind, customer));
Customer anotherCustomer = new Customer();
anotherCustomer.CustomerID = "US123";
anotherCustomer.Address = "253, Madison Avenue";
anotherCustomer.City = "New York";
anotherCustomer.CompanyName = "Another company";
anotherCustomer.Country = "United States";
anotherCustomer.ContactName = "Peter Jameson";
Console.WriteLine(InsertModifyDeleteCustomers.ModifyCustomerById(northwind, "COMPA", anotherCustomer));
Console.WriteLine(InsertModifyDeleteCustomers.DeleteCustomer(northwind, anotherCustomer));
}
}
public static string DeleteCustomer(NorthwindEntities northwind, Customer newCustomer)
{
var selectedCustomer = northwind.Customers.Where(c => c.CustomerID == newCustomer.CustomerID).FirstOrDefault();
northwind.Customers.Remove(selectedCustomer);
northwind.SaveChanges();
return "Customer deleted";
}
/// <summary>
/// EF works with transactions by default - calling SaveChanges() starts a transaction by default.
/// However we can use transactions to unify many calling of SaveChanges() as one and if one fails - all fail.
/// </summary>
public static void Main()
{
using (var db = new NorthwindEntities())
{
using (var transaction = db.Database.BeginTransaction())
{
var order = new Order()
{
CustomerID = "WELLI",
EmployeeID = 4
};
db.Orders.Add(order);
var orderDetails1 = new Order_Detail()
{
OrderID = order.OrderID,
ProductID = 5,
UnitPrice = 12.34m,
Quantity = 100,
Discount = 0.2f
};
var orderDetails2 = new Order_Detail()
{
OrderID = order.OrderID,
ProductID = 3,
UnitPrice = 12.34m,
Quantity = 100,
Discount = 0.2f
};
var orderDetails3 = new Order_Detail()
{
OrderID = order.OrderID,
ProductID = 4,
UnitPrice = 12.34m,
Quantity = 100,
Discount = 0.2f
};
db.Order_Details.AddRange(new[] { orderDetails1, orderDetails2, orderDetails3 });
try
{
db.SaveChanges();
}
catch (System.Exception ex)
{
System.Console.WriteLine(ex);
transaction.Rollback();
}
transaction.Commit();
}
}
}
public static void Main()
{
using (var northwind = new NorthwindEntities())
{
var a = northwind.Orders.Select(o => o.ShipRegion).ToList();
List<string> sales = FindSales(northwind, 1997, "RJ");
Console.WriteLine(string.Join(Environment.NewLine, sales));
}
}
public static void Main()
{
using (var northwind = new NorthwindEntities())
{
var orders = northwind.Orders.Where(o => o.ShipCountry == "Canada" && o.RequiredDate.Value.Year == 1997).ToList();
foreach (var order in orders)
{
Console.WriteLine("{0} | {1} | {2} | {3}", order.CustomerID, order.Freight, order.ShipAddress, order.ShipRegion);
}
}
}
public static void Main()
{
using (var northwind = new NorthwindEntities())
{
// To see the TerritoriesSet property, look in the model for the ExtendedEmployee class
EntitySet<Territory> territories = northwind.Employees.OrderBy(t => t.EmployeeID).Skip(1).FirstOrDefault().TerritoriesSet;
Console.WriteLine("Territory IDs:");
Console.WriteLine(string.Join(Environment.NewLine, territories.Select(t => t.TerritoryDescription)));
}
}
public static int ModifyCustomer(string contactName, string companyName)
{
using (NorthwindEntities db = new NorthwindEntities())
{
string id = GetCustomerId(companyName);
var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
if (customer == null) throw new ArgumentException("The record you are trying to modify doesn't exist!!!");
customer.CompanyName = companyName;
customer.ContactName = contactName;
return db.SaveChanges();
}
}
public static List<string> FindSales(NorthwindEntities northwind, int year, string region)
{
List<string> sales = northwind.Orders.Join(northwind.Customers, o => o.CustomerID, c => c.CustomerID, (o, c) => new
{
CompanyName = c.CompanyName,
OrderDate = o.OrderDate,
ShipCountry = o.ShipCountry,
ShipRegion = o.ShipRegion
}).Where(x => x.ShipRegion == region && year.CompareTo(x.OrderDate.Value.Year) == 0).Select(y => y.CompanyName).
ToList();
return sales;
}
private static void Main(string[] args)
{
using (NorthwindEntities dbContext = new NorthwindEntities())
{
IEnumerable<Employee> employees = (from empl in dbContext.Employees
select empl);
foreach (var employee in employees)
{
Console.WriteLine("{0} {1} {2}", employee.EmployeeID, employee.FirstName, employee.LastName );
foreach (var teritory in employee.Territories)
{
Console.WriteLine("{0} | {1}", teritory.TerritoryID, teritory.TerritoryDescription);
}
}
}
}
public static int DeleteCustomer(string companyName)
{
using (NorthwindEntities db = new NorthwindEntities())
{
string id = GetCustomerId(companyName);
if (IsCustomerExists(db, id))
{
Customer customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
db.Customers.Remove(customer);
return db.SaveChanges();
}
else
{
return 0;
}
}
}
//TODO: Create a database called NorthwindTwin with the same structure as
// Northwind using the features from DbContext. Find for the API for
// schema generation in MSDN or in Google.
static void Main()
{
IObjectContextAdapter context = new NorthwindEntities();
string cloneNorthwind = context.ObjectContext.CreateDatabaseScript();
string createNorthwindCloneDB =
"CREATE DATABASE NorthwindTwin ON PRIMARY " +
"(NAME = NorthwindTwin, " +
"FILENAME = 'D:\\NorthwindTwin.mdf', " +
"SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = NorthwindTwin_Log, " +
"FILENAME = 'D:\\NorthwindTwinLog.ldf', " +
"SIZE = 5MB, " +
"MAXSIZE = 10MB, " +
"FILEGROWTH = 10%)";
SqlConnection dbConForCreatingDB = new SqlConnection(
@"Data Source=S_MANHATTAN;
Persist Security Info=True;
User ID=Manhattan;
Password=123;
Initial Catalog=master");
dbConForCreatingDB.Open();
using (dbConForCreatingDB)
{
SqlCommand createDB = new SqlCommand(createNorthwindCloneDB, dbConForCreatingDB);
createDB.ExecuteNonQuery();
}
SqlConnection dbConForCloning = new SqlConnection(
@"Data Source=S_MANHATTAN;
Persist Security Info=True;
User ID=Manhattan;
Password=123;
Initial Catalog=NorthwindTwin");
dbConForCloning.Open();
using (dbConForCloning)
{
SqlCommand cloneDB = new SqlCommand(cloneNorthwind, dbConForCloning);
cloneDB.ExecuteNonQuery();
}
}
public static void Main()
{
using (var northwind = new NorthwindEntities())
{
string command = @"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 = { 1997, "Canada" };
var sqlQueryResult = northwind.Database.SqlQuery<string>(command, parameters);
foreach (var order in sqlQueryResult)
{
Console.WriteLine(order);
}
}
}
// TODO: Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
static void Main()
{
using (NorthwindEntities db = new NorthwindEntities())
{
DateTime dateStart = new DateTime(1997, 1, 1);
DateTime dateEnd = new DateTime(1997, 12, 31);
var result = (from order in db.Orders
join customer in db.Customers on order.CustomerID equals customer.CustomerID
where (order.OrderDate >= dateStart && order.OrderDate <= dateEnd) && (customer.Country == "Canada")
select customer).Distinct();
foreach (var customer in result)
{
Console.WriteLine("{0,-30} | {1,-30}",customer.CompanyName, customer.ContactName);
}
}
}
protected void Page_Load(object sender, EventArgs e)
{
NorthwindModel.NorthwindEntities objContext = new NorthwindModel.NorthwindEntities();
//var result = from item in objContext.Categories
// where item.CategoryID == 65985
// select item;
//outer join
var result = from cat in objContext.Categories
join prod in objContext.Products
on cat.CategoryID equals prod.CategoryID
into groupedItems
//group cat by cat.CategoryID into groupedItems
select new { CategoryID = cat.CategoryID };
GridView1.DataSource = result;
GridView1.DataBind();
Label1.Text = ((ObjectQuery)result).ToTraceString();
}
// TODO: Write a method that finds all the sales by specified region and period (start / end dates).
static void Main()
{
using(NorthwindEntities db = new NorthwindEntities())
{
DateTime startDate = new DateTime(1997,01,01);
DateTime endDate = new DateTime(2001,12,31);
string region = "ID";
var orders = from ord in db.Orders
where ord.OrderDate >= startDate && ord.OrderDate <= endDate && ord.ShipRegion == region
select new { ord.OrderDate, ord.ShipRegion, ord.OrderID };
foreach (var ord in orders)
{
Console.WriteLine("{0,-30} | {1,-10} | {2,-20}", ord.OrderDate, ord.ShipRegion, ord.OrderID);
}
}
}
public static void Main()
{
using (var northwind = new NorthwindEntities())
{
using (var scope = new TransactionScope())
{
for (int i = 0; i < 10; i++)
{
northwind.Orders.Add(new Order() { ShipAddress = "455, Metropolitan Avenue" });
}
northwind.SaveChanges();
scope.Complete();
}
Console.WriteLine("Number of added orders: {0}", northwind.Orders.Where(o => o.ShipAddress == "455, Metropolitan Avenue").Count());
}
}
public static void Main()
{
using (var db = new NorthwindEntities())
{
try
{
Console.WriteLine("Creating stored procedure");
db.Database.ExecuteSqlCommand(GetStoreProcedureCreationQuery(), null);
}
catch (Exception)
{
Console.WriteLine("Store procedure already exists");
}
var income = CallGetSupplierIncomeProcedure(db, "Pavlova, Ltd.", new DateTime(1996, 1, 1), new DateTime(1999, 1, 1));
Console.WriteLine(income);
}
}
public static int InsertCustomer(string contactName, string companyName)
{
using (NorthwindEntities db = new NorthwindEntities())
{
Customer customer = new Customer()
{
ContactName = contactName,
CompanyName = companyName,
CustomerID = GetCustomerId(companyName),
};
if (IsCustomerExists(db, GetCustomerId(companyName)))
{
throw new ArgumentException("Customer already exist!!!");
}
db.Customers.Add(customer);
return db.SaveChanges();
}
}
public static string ModifyCustomerById(NorthwindEntities northwind, string customerId, Customer newCustomer)
{
var selectedCustomer = northwind.Customers.Where(c => c.CustomerID == customerId).FirstOrDefault();
selectedCustomer.Address = newCustomer.Address;
selectedCustomer.City = newCustomer.City;
selectedCustomer.CompanyName = newCustomer.CompanyName;
selectedCustomer.ContactName = newCustomer.ContactName;
selectedCustomer.ContactTitle = newCustomer.ContactTitle;
selectedCustomer.Country = newCustomer.Country;
selectedCustomer.CustomerDemographics = newCustomer.CustomerDemographics;
selectedCustomer.Fax = newCustomer.Fax;
selectedCustomer.Orders = newCustomer.Orders;
selectedCustomer.Phone = newCustomer.Phone;
selectedCustomer.PostalCode = newCustomer.PostalCode;
selectedCustomer.Region = newCustomer.Region;
northwind.SaveChanges();
return "Customer modified";
}
//TODO: Try to open two different data contexts and perform concurrent changes on the same records.
// What will happen at SaveChanges()? How to deal with it?
static void Main()
{
string companyName = "Concurency Test";
string contactName = "Concurent1";
string contactName2 = "Concurent2";
using (NorthwindEntities db = new NorthwindEntities())
{
using(NorthwindEntities db2 = new NorthwindEntities())
{
string id = GetCustomerId(companyName);
var customer = db.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
var customer2 = db2.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
if (customer == null) throw new ArgumentException("The record you are trying to modify doesn't exist!!!");
customer.CompanyName = companyName;
customer.ContactName = contactName;
customer2.ContactName= contactName2;
db.SaveChanges();
db2.SaveChanges();
}
}
}
private static void CreateOrder(Order newOrder, int numberOfRepeatsUntillSuccess)
{
bool isTransactionSucceed = false;
using (NorthwindEntities dbContext = new NorthwindEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
for (int i = 0; i < numberOfRepeatsUntillSuccess; i++)
{
try
{
dbContext.Orders.Add(newOrder);
dbContext.SaveChanges();
transaction.Complete();
isTransactionSucceed = true;
break;
}
catch (UpdateException ex)
{
if (i == numberOfRepeatsUntillSuccess - 1)
{
throw new InvalidOperationException("Cannot complete order creation", ex);
}
}
}
if (isTransactionSucceed)
{
// Reset the context since the operation succeeded.
Console.WriteLine("Transaction sompited");
}
else
{
Console.WriteLine("The operation could not be completed in "
+ numberOfRepeatsUntillSuccess + " tries.");
}
}
}
}
public static void Main()
{
using (var northwind = new NorthwindEntities())
{
// This procedure is saved in the database and the model now contains it
//northwind.Database.ExecuteSqlCommand(
// "CREATE PROCEDURE usp_FindTotalIncome (@startDate INT, @endDate INT, @companyName NVARCHAR(100)) " +
// "AS " +
// "SELECT SUM(od.Quantity * od.UnitPrice) AS [Income] " +
// "FROM Suppliers s JOIN Products p " +
// "ON s.SupplierID = p.SupplierID " +
// "JOIN [Order Details] od " +
// "ON od.ProductID = p.ProductID " +
// "JOIN Orders o " +
// "ON od.OrderID = o.OrderID " +
// "WHERE s.CompanyName = @companyName AND (YEAR(o.OrderDate) >= @startDate AND YEAR(o.OrderDate) <= @endDate)");
//northwind.SaveChanges();
var income = northwind.usp_FindTotalIncome(1997, 1998, "Exotic Liquids");
Console.WriteLine("Total income: {0:F2}", income.FirstOrDefault());
}
}
public void RefineCombinedSpecifications()
{
Specification<Customer> german_customer_spec =
new Specification<Customer>(c => c.Country == "Germany");
Specification<Customer> us_customer_spec =
new Specification<Customer>(c => c.Country == "Usa");
Specification<Product> ords_with_discontinued_prods =
new Specification<Product>(p => p is DiscontinuedProduct);
var comb_country_spec = (german_customer_spec | us_customer_spec);
using (var ctx = new NorthwindEntities())
{
var discontinued_products_on_order =
(ObjectQuery<Product>)
(from p in ctx.Products
.Where(ords_with_discontinued_prods.EvalPredicate)
from od in ctx.OrderDetails
from o in ctx.Orders
from c in ctx.Customers
.Where(comb_country_spec.EvalPredicate)
where od.ProductID == p.ProductID &&
o.OrderID == od.OrderID &&
c.CustomerID == o.Customer.CustomerID
select p).Distinct();
Console.WriteLine(discontinued_products_on_order.ToTraceString());
foreach (var product in discontinued_products_on_order)
{
Console.WriteLine("The product ID is {0} with a name of {1}",
product.ProductID, product.ProductName);
}
}
}
protected void serverModeDataSource_Selecting(object sender, DevExpress.Data.Linq.LinqServerModeDataSourceSelectEventArgs e)
{
NorthwindModel.NorthwindEntities dataContext = new NorthwindModel.NorthwindEntities();
e.QueryableSource = dataContext.Products;
e.KeyExpression = "ProductID";
}
public void CombinedOrSpecExample()
{
Specification<Order> country_customer_spec =
new Specification<Order>
(o => o.Customer.Country == "Germany" ||
o.Customer.Country == "Usa");
Specification<Order> german_customer_spec =
new Specification<Order>
(o => o.Customer.Country == "Germany");
Specification<Order> us_customer_spec =
new Specification<Order>
(o => o.Customer.Country == "Usa");
Specification<Order> orderid_spec =
new Specification<Order>(o => o.OrderID != 10279);
Specification<Order> ords_with_discontinued_prods =
new Specification<Order>(o => o.Order_Details.All
(od => od.Product is DiscontinuedProduct));
var orSpec = ords_with_discontinued_prods &
orderid_spec & (german_customer_spec | us_customer_spec);
using (var ctx = new NorthwindEntities())
{
//note the eager loading here
//and still further down we have
//to lazy load the same data
//as eager loading has not worked
//as expected.
var discontinued_products_on_order =
(ObjectQuery<Order_Detail>)
from o in ctx.Orders
.Include("Order_Details")
.Include("Customer")
.Where(orSpec.EvalPredicate)
from od in ctx.OrderDetails
where od.OrderID == o.OrderID
select od;
Console.WriteLine(discontinued_products_on_order.ToTraceString());
foreach (var ord_detail in discontinued_products_on_order)
{
if (!ord_detail.OrderReference.IsLoaded)
{
ord_detail.OrderReference.Load();
}
if (!ord_detail.ProductReference.IsLoaded)
{
ord_detail.ProductReference.Load();
}
if (!ord_detail.Order.CustomerReference.IsLoaded)
{
ord_detail.Order.CustomerReference.Load();
}
Console.WriteLine("The OrderID: {0} the product name: {1} the customers location {2}",
ord_detail.OrderID, ord_detail.Product.ProductName, ord_detail.Order.Customer.Country);
}
Assert.IsTrue(discontinued_products_on_order
.Count(p => p.Order.Customer.Country != "Germany" &&
p.Order.Customer.Country != "USA") == 0);
}
}
private static bool IsCustomerExists(NorthwindEntities db, string customerId)
{
bool alreadyInDB = db.Customers.Where(customer => customer.CustomerID == customerId).Any();
return alreadyInDB;
}
public void SimpleSpecificationExample()
{
Specification<Order> order_spec =
new Specification<Order>(o => o.Order_Details.All
(od => od.Product is DiscontinuedProduct));
using (var ctx = new NorthwindEntities())
{
ObjectQuery<Order> orders =
(ObjectQuery<Order>)
from o in ctx.Orders.Include("Customer")
.Include("Order_Details")
.Include("Order_Details.Product")
.Where(order_spec.EvalPredicate)
select o;
Console.WriteLine(orders.ToTraceString());
foreach (var order in orders)
{
if (order.Order_Details.Count() > 0)
{
var sumup =
order.Order_Details.Sum
(od => od.Quantity * od.UnitPrice);
Console.WriteLine("The sum of orders for order ID: " +
"{0} is {1} and Country is {2}",
order.OrderID.ToString(), sumup.ToString(),
order.Customer.Country);
}
}
//test if all the products retrieved are indeed discontinued
var discontinued = orders.Count(o => o.Order_Details.Count
(od => od.Product as DiscontinuedProduct == null) > 1);
Assert.AreEqual(discontinued, 0);
}
}
