public static Customer GetCustomerByID(string customerID)
{
using (var db = new NorthwindEntities())
{
return db.Customers.FirstOrDefault(c => c.CustomerID == customerID);
}
}
private static void PrintWhatSecondUserSees(NorthwindEntities secondConnection)
{
Console.WriteLine("User2 see: {0}\n", secondConnection.Employees.First().FirstName);
var firstEmployee2 = secondConnection.Employees.First();
firstEmployee2.FirstName = "2";
Console.WriteLine("User2 changes the name with new value: {0}\n", secondConnection.Employees.First().FirstName);
}
private static void PrintActualResult()
{
using (var northwindEntities = new NorthwindEntities())
{
Console.WriteLine("Actual result: {0}\n", northwindEntities.Employees.First().FirstName);
}
}
private static void PrintWhatFirstUserSees(NorthwindEntities firstConnection)
{
Console.WriteLine("\rUser1 see: {0}\n", firstConnection.Employees.First().FirstName);
var firstEmployee1 = firstConnection.Employees.First();
firstEmployee1.FirstName = "1";
Console.WriteLine("User1 changes the name with new value: {0}\n", firstConnection.Employees.First().FirstName);
}
public static void Main()
{
using (var firstDbContext = new NorthwindEntities())
{
var firstEmployeeFromFirstDbContext = firstDbContext.Employees.First();
Console.WriteLine("First user see: {0}", firstEmployeeFromFirstDbContext.FirstName);
firstEmployeeFromFirstDbContext.FirstName = "First";
Console.WriteLine("First user changes the name with new value: {0}\n", firstEmployeeFromFirstDbContext.FirstName);
firstDbContext.SaveChanges();
using (var secondDbContext = new NorthwindEntities())
{
var firstEmployeeFromSecondDbContext = secondDbContext.Employees.First();
Console.WriteLine("Second user see: {0}", firstEmployeeFromSecondDbContext.FirstName);
firstEmployeeFromSecondDbContext.FirstName = "Second";
Console.WriteLine("Second user changes the name with new value: {0}\n", firstEmployeeFromSecondDbContext.FirstName);
secondDbContext.SaveChanges();
}
Console.WriteLine("After all changes:");
Console.WriteLine("First user see: {0}\n", firstEmployeeFromFirstDbContext.FirstName);
}
using (var northwindEntities = new NorthwindEntities())
{
Console.WriteLine("Actual result: {0}", northwindEntities.Employees.First().FirstName);
}
}
private static void StartFirstConnection()
{
using (var firstConnection = new NorthwindEntities())
{
PrintWhatFirstUserSees(firstConnection);
StartSecondConnection(firstConnection);
PrintWhatFirstUserSeesAfterChanges(firstConnection);
}
}
public static void Main()
{
Console.Write("Loading...");
using (var northwindEntities = new NorthwindEntities())
{
var contactNames = northwindEntities.Customers.Select(c => c.ContactName); // Projection
Console.WriteLine("\r" + string.Join(Environment.NewLine, contactNames));
}
}
private static void StartSecondConnection(NorthwindEntities firstConnection)
{
using (var secondConnection = new NorthwindEntities())
{
PrintWhatSecondUserSees(secondConnection);
firstConnection.SaveChanges();
secondConnection.SaveChanges();
PrintWhatSecondUserSeesAfterChanges(secondConnection);
}
}
public static int ModifyCustomerCompanyName(string customerId, string newCompanyName)
{
using (var dbContext = new NorthwindEntities())
{
var customer = dbContext.Customers.Find(customerId);
customer.CompanyName = newCompanyName;
int affectedRows = dbContext.SaveChanges();
return affectedRows;
}
}
public static int InsertCustomer(Customer customer)
{
var affectedRows = 0;
using (var db = new NorthwindEntities())
{
db.Customers.Add(customer);
affectedRows = db.SaveChanges();
}
return affectedRows;
}
public static int DeleteCustomer(string customerId)
{
using (var dbContext = new NorthwindEntities())
{
var customer = dbContext.Customers.Find(customerId);
dbContext.Customers.Remove(customer);
int affectedRows = dbContext.SaveChanges();
return affectedRows;
}
}
public static void Main()
{
// 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());
}
}
public static int ModifyCustomerAddress(string customerID, string newAddress)
{
var affectedRows = 0;
using (var db = new NorthwindEntities())
{
var targetCustomer = db.Customers.Find(customerID);
targetCustomer.Address = newAddress;
affectedRows = db.SaveChanges();
}
return affectedRows;
}
// 03. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
public static IEnumerable<IGrouping<string, Customer>> FindCustomersByOrderYearAndCountry(int orderYear, string country)
{
using (var dbContext = new NorthwindEntities())
{
var targetedCustomers = dbContext.Orders
.Where(o => o.OrderDate.Value.Year == orderYear && o.ShipCountry == country)
.Select(o => o.Customer)
.GroupBy(c => c.ContactName)
.ToList();
return targetedCustomers;
}
}
public static void Main()
{
var supplierName = "Exotic Liquids";
var startDate = new DateTime(1996, 1, 1);
var endDate = new DateTime(2000, 12, 31);
using (var northwindEntities = new NorthwindEntities())
{
var totalIncomes = northwindEntities.usp_FindTotalIncomesOfSupplier(supplierName, startDate, endDate).Single();
Console.WriteLine("Total incomes: {0:C}", totalIncomes);
}
}
public static void Main(string[] args)
{
using (var dbContext = new NorthwindEntities())
{
string generatedScript = ((IObjectContextAdapter) dbContext).ObjectContext.CreateDatabaseScript();
StringBuilder dbScript = new StringBuilder();
dbScript.Append("USE NorthwindTwin ");
dbScript.Append(generatedScript);
dbContext.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "CREATE DATABASE NorthwindTwin");
dbContext.Database.ExecuteSqlCommand(dbScript.ToString());
}
}
public static void Main()
{
// See file -> Northwind.Models -> `Employee.cs
using (var dbContext = new NorthwindEntities())
{
foreach (var employee in dbContext.Employees.Include("Territories"))
{
var correspondingTerritories = employee.CorrespondingTerritories.Select(c => c.TerritoryID);
var correspondingTerritoriesAsString = string.Join(", ", correspondingTerritories);
Console.WriteLine("{0} -> Territory IDs: {1}", employee.FirstName, correspondingTerritoriesAsString);
}
}
}
private static int TestWithTransaction()
{
var affectedRows = 0;
var customerId = "RATTC";
var employeeId = 5;
var invalidEmployeeId = 5000;
using (var dbContext = new NorthwindEntities())
{
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
// To test with invalid data and see what happens, change employeeId with invalidEmployeeId
// then the transaction will rollback and there will be no added orders
var firstOrder = new Order()
{
CustomerID = customerId,
EmployeeID = employeeId
};
dbContext.Orders.Add(firstOrder);
var secondOrder = new Order()
{
CustomerID = customerId,
EmployeeID = employeeId
};
dbContext.Orders.Add(secondOrder);
affectedRows = dbContext.SaveChanges();
transaction.Commit();
Console.WriteLine("- Finish successfully => Commit transaction");
}
catch (Exception)
{
transaction.Rollback();
Console.WriteLine("- Exception: Finish Unsuccessfully => Rollback transaction");
}
}
}
return affectedRows;
}
public static void Main()
{
using (var northwindEntities = new NorthwindEntities())
{
var customerNames = northwindEntities.Customers.Select(c => new
{
Name = c.ContactName
});
foreach (var customer in customerNames)
{
Console.WriteLine(customer.Name);
}
}
}
public static void Main()
{
// IMPORTANT: At first you must execute "Create-Stored-Procedure.sql" in Northwind database.
var supplierName = "Exotic Liquids";
var startDate = new DateTime(1996, 1, 1);
var endDate = new DateTime(1997, 12, 31);
using (var northwindEntities = new NorthwindEntities())
{
var totalIncomes = northwindEntities.usp_TotalIncomesOfSupplier(supplierName, startDate, endDate).Single();
Console.WriteLine("Total incomes: {0:C}", totalIncomes);
}
}
public static int DeleteCustomerById(string customerID)
{
var affectedRows = 0;
using (var db = new NorthwindEntities())
{
Customer customerToDelete = db.Customers.Find(customerID);
if (customerToDelete != null)
{
db.Customers.Remove(customerToDelete);
affectedRows = db.SaveChanges();
}
}
return affectedRows;
}
public static IEnumerable<string> GetCustomersByOrderSQL(int year, string shipCountry)
{
var northwind = new NorthwindEntities();
string query =
@"SELECT DISTINCT c.ContactName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1}
ORDER BY c.ContactName";
object[] parameters = { year, shipCountry };
var customers = northwind.Database.SqlQuery<string>(query, parameters);
return customers;
}
/// <summary>
/// Finds all customers who have orders made in 1997 and shipped to Canada.
/// </summary>
/// <returns>Enumerated collection</returns>
public static IEnumerable<Customer> Customers_With_Orders_In_1997_Shipped_To_Canada_View()
{
var entities = new List<Customer>();
var orderDateYear = 1997;
var shipCountry = "Canada";
using (var dbContext = new NorthwindEntities())
{
entities = (from customer in dbContext.Customers
join order in dbContext.Orders on customer.CustomerID equals order.CustomerID
where order.OrderDate.Value.Year == orderDateYear && order.ShipCountry == shipCountry
select customer).ToList();
}
return entities;
}
public static int DeleteCustomer(string customerId)
{
var affectedRows = 0;
using (var dbContext = new NorthwindEntities())
{
var customersToDelete = dbContext.Customers.Where(c => c.CustomerID == customerId);
if (customersToDelete.Count() > 0)
{
foreach (var customer in customersToDelete)
{
dbContext.Customers.Remove(customer);
}
}
affectedRows = dbContext.SaveChanges();
}
return affectedRows;
}
public static int InsertCustomer(
string customerID,
string companyName,
string contactName = null,
string city = null,
string contactTitle = null,
string address = null,
string region = null,
string postalCode = null,
string country = null,
string phone = null,
string fax = null)
{
var newCustomer = new Customer
{
CustomerID = customerID,
CompanyName = companyName,
City = city,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax,
};
using (var dbContext = new NorthwindEntities())
{
dbContext.Customers.Add(newCustomer);
int affectedRows = dbContext.SaveChanges();
return affectedRows;
}
}
public CategoryController()
{
_entity = new NorthwindEntities();
_entity.Configuration.ProxyCreationEnabled = false;
}
private static void PrintWhatSecondUserSeesAfterChanges(NorthwindEntities secondConnection)
{
Console.WriteLine("After all changes:");
Console.WriteLine("User2 see: {0}\n", secondConnection.Employees.First().FirstName);
}
/// <summary>
/// Started the transaction implicitly.
/// </summary>
private static int TestOnImplicitlyStartedTransaction()
{
var affectedRows = 0;
var customerId = "RATTC";
var employeeId = 6;
var invalidEmployeeId = int.MaxValue;
using (var dbContext = new NorthwindEntities())
{
try
{
#region [Add Orders]
// This cause an error
var firstOrder = new Order()
{
CustomerID = customerId,
EmployeeID = invalidEmployeeId // employeeId
};
dbContext.Orders.Add(firstOrder);
var secondOrder = new Order()
{
CustomerID = customerId,
EmployeeID = employeeId
};
secondOrder.Order_Details.Add(new Order_Detail()
{
OrderID = secondOrder.OrderID,
ProductID = 5,
UnitPrice = 12.34m,
Quantity = 100,
Discount = 0.2f
});
dbContext.Orders.Add(secondOrder);
#endregion
affectedRows = dbContext.SaveChanges();
Console.WriteLine("- Finish successfully => Commit transaction");
}
catch (Exception)
{
Console.WriteLine("- Exception: Finish Unsuccessfully => Rollback transaction");
}
}
return affectedRows;
}
/// <summary>
/// Started the transaction explicitly. We have options to commit / roll-back transactions.
/// </summary>
private static int TestOnExplicitlyStartedTransaction()
{
var affectedRows = 0;
var customerId = "VINET";
var employeeId = 5;
var invalidEmployeeId = int.MaxValue;
using (var dbContext = new NorthwindEntities())
{
using (var transaction = dbContext.Database.BeginTransaction())
{
try
{
#region [Add Orders]
// This cause an error
var firstOrder = new Order()
{
CustomerID = customerId,
EmployeeID = invalidEmployeeId // employeeId
};
dbContext.Orders.Add(firstOrder);
var secondOrder = new Order()
{
CustomerID = customerId,
EmployeeID = employeeId
};
secondOrder.Order_Details.Add(new Order_Detail()
{
OrderID = secondOrder.OrderID,
ProductID = 5,
UnitPrice = 12.34m,
Quantity = 100,
Discount = 0.2f
});
dbContext.Orders.Add(secondOrder);
#endregion
affectedRows = dbContext.SaveChanges();
// Finish successfully => Commit transaction
transaction.Commit();
// Test to Rollback() insted of Commit() and you will see that the changes are rolled-back
//transaction.Rollback();
Console.WriteLine("- Finish successfully => Commit transaction");
}
catch (Exception)
{
// Finish Unsuccessfully => Rollback transaction
transaction.Rollback();
Console.WriteLine("- Exception: Finish Unsuccessfully => Rollback transaction");
}
}
}
return affectedRows;
}
/// <summary>
/// Finds all the sales by specified region and period (start/end dates).
/// </summary>
/// <returns>Enumerated collection</returns>
public static IEnumerable<Order> Sales_By_Specified_Region_And_Date_Period_View(
string region,
DateTime? startDate,
DateTime? endDate)
{
var entities = new List<Order>();
using (var dbContext = new NorthwindEntities())
{
entities = (from order in dbContext.Orders
where order.ShipRegion == region &&
order.OrderDate >= startDate &&
order.OrderDate <= endDate
select order).ToList();
}
return entities;
}
