public static void InsertCustomer(string customerID, 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 db = new NorthwindEntities();
using (db)
{
Customer customer = new Customer()
{
CustomerID = customerID,
CompanyName = companyName,
ContactName = contactName,
ContactTitle = contactTitle,
Address = address,
City = city,
Region = region,
PostalCode = postalCode,
Country = country,
Phone = phone,
Fax = fax
};
db.Customers.Add(customer);
db.SaveChanges();
Console.WriteLine("Customer inserted.");
}
}
public static string GenerateInsertOrderQuery(string customerID, int employeeID, string shipCountry, Order_Detail_No_ID[] details)
{
var db = new NorthwindEntities();
/*SQL
BEGIN TRAN
INSERT INTO ORDERS(CustomerID, EmployeeID, ShipCountry)
VALUES ('VINET', 5, 'Bulgaria');
DECLARE @OrderID int;
SET @OrderID = SCOPE_IDENTITY()
INSERT INTO [Order Details] VALUES(@OrderID, 11, 100, 50, 0)
INSERT INTO [Order Details] VALUES(@OrderID, 22, 50, 40, 0)
COMMIT TRAN
*/
var query = string.Format("BEGIN TRAN " +
" INSERT INTO ORDERS(CustomerID, EmployeeID, ShipCountry) " +
" VALUES ('{0}', {1}, '{2}'); " +
" DECLARE @OrderID int; " +
" SET @OrderID = SCOPE_IDENTITY()", customerID, employeeID, shipCountry);
for (int i = 0; i < details.Length; i++)
{
query += string.Format(" INSERT INTO [Order Details] " +
" VALUES(@OrderID, {0}, {1}, {2}, {3})", details[i].ProductID, details[i].UnitPrice,
details[i].Quantity, details[i].Discount);
}
query += " COMMIT TRAN";
return query;
}
public static void DeleteCustomer(string customerID)
{
var db = new NorthwindEntities();
using (db)
{
var customer = db.Customers.Find(customerID);
db.Customers.Remove(customer);
db.SaveChanges();
Console.WriteLine("Customer deleted.");
}
}
static void Main(string[] args)
{
// 01. Using the Visual Studio Entity Framework designer create a DbContext for the Northwind database
// 02. Create a DAO class with static methods which provide functionality for inserting,
// modifying and deleting customers. Write a testing class.
//Console.WriteLine();
//Console.WriteLine("EXERCISE 2");
//DAO.InsertCustomer("MANU1", "IVAN IVANOV IVANOV");
//DAO.UpdateCustomer("MANU2", "DANON DANON DANON", null, "SIR");
//DAO.DeleteCustomer("MANU2");
// 03. Write a method that finds all customers who have orders made in 1997 and shipped to Canada.
//Console.WriteLine();
//Console.WriteLine("EXERCISE 3");
var db = new NorthwindEntities();
using (db)
{
//////var orders = db.Orders
////// .Where(o => o.ShipCountry == "Canada")
////// .Where(o => o.ShippedDate.Value.Year == 1997)
////// .Select(c =>
////// new
////// {
////// ID = c.CustomerID,
////// Customer = c.Customer.ContactName,
////// ShipDate = c.ShippedDate,
////// Country = c.ShipCountry
////// });
//////foreach (var order in orders)
//////{
////// Console.WriteLine("ID = " + order.ID + ", Customer = " + order.Customer + ", ShipDate = " + order.ShipDate + ", Country = " + order.Country);
//////}
//////// 04. Implement previous by using native SQL query and executing it through the DbContext.
//////Console.WriteLine();
//////Console.WriteLine("EXERCISE 4");
//////string nativeSQLQuery = "SELECT c.* " +
////// "from Customers c " +
////// "JOIN Orders o " +
////// "ON o.CustomerID = c.CustomerID " +
////// "WHERE o.ShipCountry = 'Canada' AND YEAR(o.ShippedDate) = 1997";
//////var customers = db.Database.SqlQuery<Customer>(nativeSQLQuery);
//////foreach (var cust in customers)
//////{
////// Console.WriteLine(cust.CustomerID + " " + cust.CompanyName + " " + cust.ContactName + " " + cust.Region);
//////}
//////// 05. Write a method that finds all the sales by specified region and period (start / end dates).
//////Console.WriteLine();
//////Console.WriteLine("EXERCISE 5");
//////var region = "RJ";
//////var startPeriod = 1997;
//////var endPeriod = 1997;
//////var ordersByRegion = db.Orders
////// .Where(o => o.OrderDate.Value.Year >= startPeriod)
////// .Where(o => o.OrderDate.Value.Year <= endPeriod)
////// .Where(o => o.ShipRegion == region)
////// .Select(c =>
////// new
////// {
////// ID = c.CustomerID,
////// Customer = c.Customer.ContactName,
////// OrderDate = c.OrderDate,
////// Region = c.ShipRegion,
////// Country = c.ShipCountry
////// });
//////foreach (var order in ordersByRegion)
//////{
////// Console.WriteLine("ID = " + order.ID + ", Customer = " + order.Customer + ", OrderDate = " + order.OrderDate + ", Region = " + order.Region + ", Country = " + order.Country);
//////}
// 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?
//var db2 = new NorthwindEntities();
//using (db2)
//{
// var customer = db.Customers.Find("MANU1");
// customer.CompanyName = "BMW1";
// customer.ContactTitle = "WOW1";
// db.SaveChanges();
// var customer2 = db2.Customers.Find("MANU1");
// customer2.CompanyName = "BMW2";
// customer2.ContactTitle = "WOW2";
// db2.SaveChanges();
//}
// 09. 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.
//var query = DAO.GenerateInsertOrderQuery("VINET", 5, "Bulgaria",
// new Order_Detail_No_ID[] {
// new Order_Detail_No_ID(11, 100, 50, 0),
// new Order_Detail_No_ID(22, 50, 40, 0)});
//var result = db.Database.SqlQuery<Object>(query);
//foreach (var i in result)
//{
// Console.WriteLine(i);
//}
// 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.
// SORRY , no time to implement it :)
/*CREATE PROCEDURE dbo.usp_income (@SuplierID INT, @StartYear INT, @EndYear INT)
AS
SELECT SUM(o.UnitPrice*o.Quantity*o.Discount) AS INCOME
FROM [Order Details] o
JOIN
Products p
ON o.ProductID = p.ProductID
JOIN
Suppliers s
ON s.SupplierID = p.SupplierID
JOIN Orders ord
ON ord.OrderID = o.OrderID
WHERE s.SupplierID = @SuplierID AND (YEAR(ord.OrderDate) > @StartYear AND YEAR(ord.OrderDate) < @EndYear)
GO
EXEC dbo.usp_income 1, 1996, 1998
GO*/
}
}
public static void UpdateCustomer(string customerID, 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 db = new NorthwindEntities();
using (db)
{
var customer = db.Customers.Find(customerID);
customer.CompanyName = companyName;
customer.ContactName = contactName;
customer.ContactTitle = contactTitle;
customer.Address = address;
customer.City = city;
customer.Region = region;
customer.PostalCode = postalCode;
customer.Country = country;
customer.Phone = phone;
customer.Fax = fax;
db.SaveChanges();
Console.WriteLine("Customer updated.");
}
}
