public static void Insert(string customerId, string companyName, string address = null,
string city = null, string contactName = null, string contactTitle = null, string country = null,
string fax = null, string phone = null, string postalCode = null, string region = null)
{
using (NorthwindEntities dbContext = new NorthwindEntities())
{
Customer newCustomer = new Customer()
{
Address = address,
City = city,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Country = country,
CustomerID = customerId,
Fax = fax,
Phone = phone,
PostalCode = postalCode,
Region = region
};
dbContext.Customers.Add(newCustomer);
dbContext.SaveChanges();
}
}
/// <summary>
/// Mains this instance.
/// </summary>
public static void Main()
{
Console.WriteLine("To get this working fix dbPath and db connection string if required!");
// Get DB structure.
string createClone = string.Empty;
using (var northwindDbContext = new NorthwindEntities())
{
createClone = (northwindDbContext as IObjectContextAdapter).ObjectContext.CreateDatabaseScript();
}
string createDBScript = "" +
"IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'NorthwindTwin')"+
"DROP DATABASE NorthwindTwin;" +
"CREATE DATABASE NorthwindTwin ON PRIMARY " +
"(NAME = NorthwindTwin, FILENAME = '" + dbPath + "NorthwindTwin.mdf', SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = NorthwindTwinLog, FILENAME = '" + dbPath + "NorthwindTwin.ldf', SIZE = 1MB, MAXSIZE = 5MB, FILEGROWTH = 10%);";
using (var db = new SqlConnection("Server=.; Database=master; Integrated Security=true"))
{
// Create empty DB
db.Open();
var initializeEmptyDB = new SqlCommand(createDBScript, db);
initializeEmptyDB.ExecuteNonQuery();
db.ChangeDatabase("NorthwindTwin");
// Create twin structure.
SqlCommand createTwin = new SqlCommand(createClone, db);
createTwin.ExecuteNonQuery();
}
Console.WriteLine("Cloning Done!");
}
public static int AddCustomer(Customer newCustomer)
{
using (var dataBase = new NorthwindEntities())
{
dataBase.Customers.Add(newCustomer);
return dataBase.SaveChanges();
}
}
/// <summary>
/// Finds all customers who have orders made in 1997 and shipped to Canada.
/// </summary>
/// <returns>The customers.</returns>
public static IEnumerable AnotherFindCustomersWithOrdersFromCanadaIn1997()
{
using (var northwindDbContext = new NorthwindEntities())
{
var customers = northwindDbContext.Orders.Include("Customer").Where(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada").GroupBy(o => o.Customer.ContactName).Select(c => c.Key).ToList();
return customers;
}
}
/// <summary>
/// Finds all customers who have orders made in 1997 and shipped to Canada.
/// </summary>
/// <returns>The customers.</returns>
public static IEnumerable FindCustomersWithOrdersFromCanadaIn1997()
{
using (var northwindDbContext = new NorthwindEntities())
{
var customers = northwindDbContext.Orders.Where(o => o.OrderDate.Value.Year == 1997 && o.ShipCountry == "Canada").Join(northwindDbContext.Customers, o => o.CustomerID, c => c.CustomerID, (o,c) => c.ContactName).Distinct().ToList();
return customers;
}
}
/// <summary>
/// Finds Total Income.
/// </summary>
/// <param name="supplierName">The supplier name.</param>
/// <param name="startDate">Period start date.</param>
/// <param name="endDate">Period end date.</param>
/// <returns>The total income.</returns>
private static decimal? FindTotalIncome(string supplierName, DateTime startDate, DateTime endDate)
{
using (var northwindDbContext = new NorthwindEntities())
{
var totalIncome = northwindDbContext.usp_FindTotalIncome(supplierName, startDate, endDate).First();
return totalIncome;
}
}
public static int DeleteCustomer(string id)
{
using (var dataBase = new NorthwindEntities())
{
Customer customer = dataBase.Customers.First(x => x.CustomerID == id);
dataBase.Customers.Remove(customer);
return dataBase.SaveChanges();
}
}
/// <summary>
/// Adds a customer to customers table.
/// </summary>
/// <param name="customer">The customer to be added.</param>
public static void AddCustomer(Customer customer)
{
using (var northwindDbContext = new NorthwindEntities())
{
northwindDbContext.Customers.Add(customer);
northwindDbContext.SaveChanges();
Console.WriteLine("Customer successfully added!");
}
}
//2. Create a DAO class with static methods which provide functionality
//for inserting, modifying and deleting customers. Write a testing class.
public static void Delete(string customerId)
{
using (NorthwindEntities dbContext = new NorthwindEntities())
{
Customer customer = dbContext.Customers.Find(customerId);
dbContext.Customers.Remove(customer);
dbContext.SaveChanges();
}
}
public static int UpdateContactNameTitle(Customer customer, string contactName, string contactTitle)
{
using (var northwindEntities = new NorthwindEntities())
{
northwindEntities.Customers.Attach(customer);
customer.CompanyName = contactName;
customer.ContactTitle = contactTitle;
return northwindEntities.SaveChanges();
}
}
public static void FindSaleByDateRegion(string region, DateTime start, DateTime end)
{
using (var dataBase = new NorthwindEntities())
{
foreach (var item in dataBase.Orders.Where(x => x.ShippedDate >= start && x.ShippedDate <= end && x.ShipRegion == region))
{
Console.WriteLine("{0} {1} {2}", item.OrderID, item.ShippedDate.GetValueOrDefault().ToString("dd/MM/yyyy"), item.ShipCity);
}
}
}
private static void Main(string[] args)
{
using (var northwindDbContext = new NorthwindEntities())
{
foreach (var territory in northwindDbContext.Employees.First().TerritoriesSet)
{
Console.WriteLine(territory);
}
}
}
static void Main(string[] args)
{
using(var context = new NorthwindEntities())
{
var result = context.usp_FindTotalIncome(new DateTime(1994, 1, 1), new DateTime(2000, 12, 31), "Exotic Liquids").First();
Console.WriteLine("All Income are {0}",result);
}
}
public static void Modify(string customerId, string companyName /*= null, string address = null,
string city = null, string contactName = null, string contactTitle = null, string country = null,
string fax = null, string phone = null, string postalCode = null, string region = null*/)
{
using (NorthwindEntities dbContext = new NorthwindEntities())
{
Customer customer = dbContext.Customers.Find(customerId);
customer.CompanyName = companyName;
dbContext.SaveChanges();
}
}
public static void FindCustomerByCountryOrderYear(string country, int year)
{
using (var dataBase = new NorthwindEntities())
{
foreach (var item in dataBase.Orders.Join(dataBase.Customers, (o => o.CustomerID), (c => c.CustomerID), (o, c) =>
new { Order = o.OrderDate, Country = o.ShipCountry, Customer = c.CompanyName }).Where(x => x.Country == country && x.Order.Value.Year == year))
{
Console.WriteLine("{0} {1} {2}", item.Customer, item.Order, item.Country);
}
}
}
/* 02. Create a DAO class with static methods which provide functionality for inserting, modifying and deleting customers. Write a testing class.*/
/// <summary>
/// Deletes a customer identified by CustomerID.
/// </summary>
/// <param name="customer">The customer.</param>
public static void DeleteCustomer(Customer customer)
{
using (var northwindDbContext = new NorthwindEntities())
{
var customerForDelete = northwindDbContext.Customers.First(c => c.CustomerID == customer.CustomerID);
northwindDbContext.Customers.Remove(customerForDelete);
northwindDbContext.SaveChanges();
Console.WriteLine("Customer successfully deleted!");
}
}
public static IEnumerable<string> FindAllCustomersInCanadaWithOrdersIn1997()
{
//3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
using (NorthwindEntities dbContext = new NorthwindEntities())
{
var customers = dbContext.Orders
.Where(o => o.OrderDate.Value.Year == 1997)
.Where(o => o.ShipCountry == "Canada")
.Join(dbContext.Customers, (o => o.CustomerID), (c => c.CustomerID),
(o, c) => c.ContactName);
return customers.ToList();
}
}
/// <summary>
/// Finds all customers who have orders made in 1997 and shipped to Canada using plain SQL.
/// </summary>
/// <returns>The customers.</returns>
public static IEnumerable FindCustomersWithOrdersFromCanadaIn1997SQL()
{
using (var northwindDbContext = new NorthwindEntities())
{
var query =
"SELECT "+
"DISTINCT(cust.ContactName) "+
"FROM "+
"dbo.Orders ord "+
"INNER JOIN dbo.Customers cust "+
"ON cust.CustomerID = ord.CustomerID "+
"WHERE DATEPART(YEAR, ord.OrderDate) = 1997 "+
"AND ord.ShipCountry = 'Canada'";
var customers = northwindDbContext.Database.SqlQuery<string>(query).ToList();
return customers;
}
}
public static void FindCustomerByCountryYearSql(string country, int year)
{
using (var dataBase = new NorthwindEntities())
{
//"SELECT CompanyName +' '+ CONVERT(VARCHAR, OrderDate, 104) +' ' + ShipCity FROM Orders "
string query = "SELECT CompanyName , OrderDate, ShipCity FROM Orders " +
"join Customers " +
"on Orders.CustomerID = Customers.CustomerID " +
"WHERE year(OrderDate) = {0} and ShipCountry = {1}";
object[] parameters = { year, country };
var table = dataBase.Database.SqlQuery<CompanyAndDate>(query, parameters);
//var table = dataBase.Database.SqlQuery<string>(query, parameters);
foreach (var info in table)
{
Console.WriteLine("{0,-12} {1, -12} {2}", info.ShipCity, info.OrderDate.ToString("dd/MM/yyyy"), info.CompanyName);
}
}
}
public static int AddOrder(Order newOrder)
{
using (var dataBase = new NorthwindEntities())
{
var customer = dataBase.Customers.First(c => c.CustomerID == newOrder.CustomerID);
if (customer != null)
{
newOrder.ShipAddress = customer.Address;
newOrder.ShipCity = customer.City;
newOrder.ShipPostalCode = customer.PostalCode;
newOrder.ShipCountry = customer.Country;
}
dataBase.Orders.Add(newOrder);
dataBase.Orders.Add(newOrder);
dataBase.Orders.Add(newOrder);
return dataBase.SaveChanges();
}
}
/* 07. 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?*/
// EF executes everything in transactions by default. We don`t need to worry about it.
/// <summary>
/// Mains this instance.
/// </summary>
public static void Main()
{
using (var northwindDbContext = new NorthwindEntities())
{
using (var secondNorthwindDbContext = new NorthwindEntities())
{
var order = northwindDbContext.Orders.Find(10248);
var sameOrder = secondNorthwindDbContext.Orders.Find(10248);
// Mark for delete
northwindDbContext.Orders.Remove(order);
// Update some stuff
sameOrder.ShipCountry = "Bulgaria";
// Make update
try
{
secondNorthwindDbContext.SaveChanges();
Console.WriteLine("Updating complete!");
}
catch (Exception)
{
Console.WriteLine("Updating failed!");
}
// Delete -> Produces an exception because of data integrity.
try
{
northwindDbContext.SaveChanges();
Console.WriteLine("Deleting complete!");
}
catch (Exception)
{
Console.WriteLine("Delete cannot be executed!");
}
}
}
Console.WriteLine("Done!");
}
/// <summary>
/// Adds an order multiple times to the db. Transaction demo.
/// </summary>
/// <param name="order">The order.</param>
private static void AddOrderMultipleTimes(Order order)
{
using (var northwindDbContext = new NorthwindEntities())
{
northwindDbContext.Orders.Add(order);
northwindDbContext.Orders.Add(order);
northwindDbContext.Orders.Add(order);
//// Uncomment to see transaction power!
// order.CustomerID = "PESHO_ZLIA";
northwindDbContext.Orders.Add(order);
northwindDbContext.Orders.Add(order);
try
{
northwindDbContext.SaveChanges();
}
catch (Exception)
{
Console.WriteLine("Error! Orders not confirmed!");
}
}
}
public static IEnumerable<string> FindAllCustomersInCanadaWithOrdersIn1997Native()
{
//4. Implement previous by using native SQL query and executing it through the DbContext.
using (NorthwindEntities dbContext = new NorthwindEntities())
{
var customers = dbContext.Database.SqlQuery<string>(
@"SELECT c.ContactName
FROM Customers AS c JOIN Orders AS o ON c.CustomerID = o.CustomerID
WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry ='Canada'");
return customers.ToList();
}
}
static decimal? GetIncome(string supplierName, DateTime startDate, DateTime endDate)
{
//10. Create a stored procedures in the Northwind database for finding the total incomes
//for given supplier name and period (start date, end date). Implement a C# method that
//calls the stored procedure and returns the retuned record set.
using (NorthwindEntities dbContext = new NorthwindEntities())
{
decimal? totalncome = dbContext.usp_GetTotalIncome(supplierName, startDate, endDate).ToList().FirstOrDefault();
return totalncome;
}
}
private static void PlaceNewOrder(
int orderId, string customerId = null,
int? employeeId = null, DateTime? orderDate = null,
DateTime? requireDate = null, DateTime? shippedDate = null,
int? shipVia = null, decimal? freight = null,
string shipName = null, string shipAddress = null,
string shipCity = null, string shipRegion = null,
string shipPostalCode = null, string shipCountry = null)
{
//9. Create a method that places a new order in the Northwind database. The order
//should contain several order items. Use transaction to ensure the data consistency.
using (NorthwindEntities dbContext = new NorthwindEntities())
{
Order newOrder = new Order()
{
CustomerID = customerId,
EmployeeID = employeeId,
Freight = freight,
OrderDate = orderDate,
OrderID = orderId,
RequiredDate = requireDate,
ShipAddress = shipAddress,
ShipCity = shipCity,
ShipCountry = shipCountry,
ShipName = shipName,
ShippedDate = shippedDate,
ShipPostalCode = shipPostalCode,
ShipRegion = shipRegion,
ShipVia = shipVia,
};
dbContext.Orders.Add(newOrder);
dbContext.SaveChanges();
}
}
private static void MakeConcurencyChanges()
{
//7. 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?
NorthwindEntities dbContext1 = new NorthwindEntities();
Employee employee1 = dbContext1.Employees.Find(10);
//employee1.City = "Burgas";
dbContext1.Employees.Remove(employee1);
//dbContext1.SaveChanges();
NorthwindEntities dbContext2 = new NorthwindEntities();
Employee employee2 = dbContext2.Employees.Find(10);
employee2.City = "Varna";
dbContext1.SaveChanges();
dbContext2.SaveChanges();
dbContext1.Dispose();
dbContext2.Dispose();
}
/// <summary>
/// Finds all customers who have orders made in 1997 and shipped to Canada using plain SQL.
/// </summary>
/// <returns>The customers.</returns>
public static IEnumerable GetOrdersByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
using (var northwindDbContext = new NorthwindEntities())
{
var orders = northwindDbContext.Orders.Where(o => o.ShipRegion == region && o.ShippedDate > startDate && o.ShippedDate < endDate).ToList();
return orders;
}
}
public static IEnumerable<object> FindAllSalesIn(string region, DateTime startDate, DateTime endDate)
{
//5. Write a method that finds all the sales by specified region and period (start / end dates).
using (NorthwindEntities dbContext = new NorthwindEntities())
{
var orders = dbContext.Orders
.Where(o => o.ShippedDate > startDate)
.Where(o => o.ShippedDate < endDate)
.Where(o => o.ShipRegion.ToLower() == region.ToLower())
.Select(o => new { o.ShippedDate, o.ShipName, o.ShipRegion }).ToList();
return orders.ToList();
}
}
public static void CreateNorthwindTwin()
{
//6. 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.
IObjectContextAdapter dbContextAdapter = new NorthwindEntities();
string dbScript =
"DROP DATABASE IF EXISTS NorthwindTwin" +
"CREATE DATABASE NorthwindTwin ON PRIMARY " +
"(NAME = NorthwindTwin, " +
"FILENAME = 'C:\\NorthwindTwin.mdf', " +
"SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = NorthwindTwinLog, " +
"FILENAME = 'C:\\NorthwindTwin.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)" +
dbContextAdapter.ObjectContext.CreateDatabaseScript().Replace("Northwind", "NorthwindTwin");
string connectionString = "Server=LOCALHOST;Database=master;Integrated Security=true";
SqlConnection dbConForCreatingDB = new SqlConnection(connectionString);
dbConForCreatingDB.Open();
using (dbConForCreatingDB)
{
SqlCommand createDB = new SqlCommand(dbScript, dbConForCreatingDB);
createDB.ExecuteNonQuery();
}
}
/// <summary>
/// Updates a customer identified by CustomerID.
/// </summary>
/// <param name="customer">The customer.</param>
public static void UpdateCustomer(Customer customer)
{
using (var northwindDbContext = new NorthwindEntities())
{
var customerForUpdate = northwindDbContext.Customers.First(c => c.CustomerID == customer.CustomerID);
customerForUpdate.CompanyName = customer.CompanyName ?? customerForUpdate.CompanyName;
customerForUpdate.ContactName = customer.ContactName ?? customerForUpdate.ContactName;
customerForUpdate.ContactTitle = customer.ContactTitle ?? customerForUpdate.ContactTitle;
customerForUpdate.Address = customer.Address ?? customerForUpdate.Address;
customerForUpdate.City = customer.City ?? customerForUpdate.City;
customerForUpdate.Region = customer.Region ?? customerForUpdate.Region;
customerForUpdate.PostalCode = customer.PostalCode ?? customerForUpdate.PostalCode;
customerForUpdate.Country = customer.Country ?? customerForUpdate.Country;
customerForUpdate.Phone = customer.Phone ?? customerForUpdate.Phone;
customerForUpdate.Fax = customer.Fax ?? customerForUpdate.Fax;
northwindDbContext.SaveChanges();
Console.WriteLine("The customer is Updated!");
}
}
