public static void AddOrderInTransactionScope()
{
Console.WriteLine("Adding two orders using TransactionScope...");
using (NorthwindEntities northwindEntities = new NorthwindEntities())
{
Order firstOrder = new Order();
firstOrder.CustomerID = "TOMSP";
firstOrder.ShipName = "First Order Ship Name";
Order secondOrder = new Order();
secondOrder.CustomerID = "OCEAN";
secondOrder.ShipName = "Second Order Ship Name";
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope())
{
northwindEntities.Orders.Add(firstOrder);
northwindEntities.SaveChanges();
northwindEntities.Orders.Add(secondOrder);
northwindEntities.SaveChanges();
scope.Complete();
}
firstOrder = northwindEntities.Orders.Find(firstOrder.OrderID);
secondOrder = northwindEntities.Orders.Find(secondOrder.OrderID);
Console.WriteLine("FirstOrder.ShipName={0}, SecondOrder.ShipName={1}", firstOrder.ShipName, secondOrder.ShipName);
}
}
public static void CopyDatabaseSchema()
{
Console.WriteLine("Creating copy of Northwind database in the MS SQL server...(wait a moment)");
IObjectContextAdapter initialDBSchema = new NorthwindEntities();
string dbScript = initialDBSchema.ObjectContext.CreateDatabaseScript();
DbContext newDBSchema = new DbContext("Server=localhost;Database=NorthwindTwin;Integrated Security=True;");
newDBSchema.Database.CreateIfNotExists();
(newDBSchema as IObjectContextAdapter).ObjectContext.ExecuteStoreCommand(dbScript);
}
public static void CalculateTotalIncomes()
{
Console.WriteLine("Calculates total incomes of Pavlova, Ltd. for year 1997...");
DateTime startDate = new DateTime(1997, 1, 1);
DateTime endDate = new DateTime(1998, 1, 1);
string supplierName = "Pavlova, Ltd.";
using (var northwindEntities = new NorthwindEntities())
{
var totalIncomes = northwindEntities.TotalIncomesForGivenSupplier(supplierName, startDate, endDate).ToList();
Console.WriteLine("Incomes: {0:N2}", totalIncomes[0]);
}
}
public static bool Delete(string customerID)
{
using (var ctx = new NorthwindEntities())
{
var theCustomer = ctx.Customers.Find(customerID);
if (theCustomer == null) return false;
ctx.Customers.Remove(theCustomer);
if (ctx.SaveChanges() == 0) return false;
}
return true;
}
public static Customer Insert(string companyName,
string contactName = null,
string contactTitle = null,
string address = null,
string city = null,
string region = null,
string postalCode = null,
string country = null,
string phone = null,
string fax = null)
{
var newCustomer = new Customer();
newCustomer.CompanyName = companyName;
newCustomer.ContactName = contactName;
newCustomer.ContactTitle = contactTitle;
newCustomer.Address = address;
newCustomer.City = city;
newCustomer.Region = region;
newCustomer.PostalCode = postalCode;
newCustomer.Country = country;
newCustomer.Phone = phone;
newCustomer.Fax = fax;
string[] getWords = companyName.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
newCustomer.CustomerID = getWords[0].Substring(0, 3) +
((getWords.Length > 1) ? getWords[1].Substring(0, 2) : getWords[0].Substring(0, 2));
newCustomer.CustomerID = newCustomer.CustomerID.ToUpperInvariant();
using (var ctx = new NorthwindEntities())
{
while (ctx.Customers.Find(newCustomer.CustomerID) != null && newCustomer.CustomerID != LastPossibleCustomerId)
{
for (int i = newCustomer.CustomerID.Length-1; i >= 0 ; i--)
{
if (newCustomer.CustomerID[i] < 'Z')
{
char[] letters = newCustomer.CustomerID.ToCharArray();
letters[i]++;
newCustomer.CustomerID = string.Join("", letters);
break;
}
}
}
ctx.Customers.Add(newCustomer);
ctx.SaveChanges();
}
return newCustomer;
}
public static void ConcurentChanges()
{
using (var ctx1 = new NorthwindEntities())
{
Employee employeeFromCtx1 = ctx1.Employees.First(e => e.EmployeeID == 1);
employeeFromCtx1.FirstName = "Pesho";
// Modify and Save the same employee in another context
// i.e. mimicking concurrent access.
using (var ctx2 = new NorthwindEntities())
{
Employee employeeFromCtx2 = ctx2.Employees.First(e => e.EmployeeID == 1);
employeeFromCtx2.FirstName = "Gosho";
ctx2.SaveChanges();
}
// Console.ReadLine();
// Save the changes... This should result in an Exception, but NOT due to EF 5 Optimistic Concurrency
ctx1.SaveChanges();
}
}
public static Customer Modify(string customerID,
string companyName = null,
string contactName = null,
string contactTitle = null,
string address = null,
string city = null,
string region = null,
string postalCode = null,
string country = null,
string phone = null,
string fax = null)
{
using (var ctx = new NorthwindEntities())
{
var theCustomer = ctx.Customers.Find(customerID);
if (theCustomer == null) return null;
if (companyName != null) theCustomer.CompanyName = companyName;
if (contactName != null) theCustomer.ContactName = contactName;
if (contactTitle != null) theCustomer.ContactTitle = contactTitle;
if (address != null) theCustomer.Address = address;
if (city != null) theCustomer.City = city;
if (region != null) theCustomer.Region = region;
if (postalCode != null) theCustomer.PostalCode = postalCode;
if (country != null) theCustomer.Country = country;
if (phone != null) theCustomer.Phone = phone;
if (fax != null) theCustomer.Fax = fax;
ctx.SaveChanges();
return theCustomer;
}
}
public static void CreateUserInGroup()
{
Console.WriteLine("Adding new user 'mancho' and attach it to the 'Admins' group...");
string username = "******";
using (var northwindEntities = new NorthwindEntities())
{
using (var scope = new System.Transactions.TransactionScope())
{
User someUser = new User();
someUser.Username = username;
someUser.FullName = "Chicho Mancho";
someUser.Password = "******";
string adminGroup = "Admins";
var group = northwindEntities.Groups.Where(a => a.Name == adminGroup).ToList();
if (group.Count == 0)
{
Group newGroup = new Group();
newGroup.Name = adminGroup;
northwindEntities.Groups.Add(newGroup);
northwindEntities.SaveChanges();
group.Add(newGroup);
}
someUser.GroupId = group[0].GroupId;
northwindEntities.Users.Add(someUser);
northwindEntities.SaveChanges();
scope.Complete();
}
}
}
public static void ShowOrdersByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
var ctx = new NorthwindEntities();
var query = from ordersInPeriod in ctx.Sales_by_Year(startDate, endDate)
join orders in ctx.Orders
on ordersInPeriod.OrderID equals orders.OrderID
where (orders.ShipRegion == region)
select orders;
int count = 1;
foreach (var item in query)
{
Console.WriteLine("Order: {0}, Date: {1:dd/MM}, To: {2}, Price: {3:N2}",
item.OrderID, item.ShippedDate, item.ShipName, item.Freight);
count++;
}
Console.WriteLine("\nTotal {0} records selected.\n", count - 1);
}
public static void ShowFirstEmployeeWithTerritories()
{
Console.WriteLine("Showing Employee[1] with its territories using EntitySet");
using (var ctx = new NorthwindEntities())
{
var employee = ctx.Employees.First(e => e.EmployeeID == 1);
Console.WriteLine("Employee[1] name=" + employee.FirstName + " " + employee.LastName);
foreach (var item in employee.EntitySetTerritories)
{
Console.WriteLine("Territory: " + item.TerritoryDescription);
}
}
}
public static void ShowCustomersByOrderYearAndCountrySQL(int year, string country)
{
var sqlCommand = "SELECT * FROM Customers AS c" +
" JOIN Orders AS o" +
" ON c.CustomerID = o.CustomerID " +
"WHERE YEAR(o.OrderDate) = {0} AND o.ShipCountry = {1}";
var ctx = new NorthwindEntities();
var query = ctx.Database.SqlQuery<Customer>(sqlCommand, year, country);
int count = 1;
foreach (var item in query)
{
Console.WriteLine("{0} - CustomerID: {1}, Company Name: {2}", count++, item.CustomerID, item.CompanyName);
}
Console.WriteLine("\nTotal {0} records selected.\n", count - 1);
}
public static void ShowCustomersByOrderYearAndCountry(int year, string country)
{
var ctx = new NorthwindEntities();
var query = from customer in ctx.Customers
join order in ctx.Orders
on customer.CustomerID equals order.CustomerID
where (order.OrderDate.Value.Year == year && order.ShipCountry == country)
select customer;
int count = 1;
foreach (var item in query)
{
Console.WriteLine("{0} - CustomerID: {1}, Company Name: {2}", count++, item.CustomerID, item.CompanyName);
}
Console.WriteLine("\nTotal {0} records selected.\n", count - 1);
}
