public static void UpdateCustomer(string customerID, Customer newCustomer)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers
.Where(cus => cus.CustomerID == customerID)
.FirstOrDefault();
customer.Address = newCustomer.Address ?? customer.Address;
customer.City = newCustomer.City ?? customer.City;
customer.CompanyName = newCustomer.CompanyName ?? customer.CompanyName;
customer.ContactName = newCustomer.ContactName ?? customer.ContactName;
customer.ContactTitle = newCustomer.ContactTitle ?? customer.ContactTitle;
customer.Country = newCustomer.Country ?? customer.Country;
customer.CustomerID = newCustomer.CustomerID ?? customer.CustomerID;
customer.Fax = newCustomer.Fax ?? customer.Fax;
customer.Phone = newCustomer.Phone ?? customer.Phone;
customer.PostalCode = newCustomer.PostalCode ?? customer.PostalCode;
customer.Region = newCustomer.Region ?? customer.Region;
db.SaveChanges();
}
Console.WriteLine("Customer updated!");
}
private static void InsertOrder(string shipName = null, string shipAddress = null, string shipCity = null, string shipRegionm = null,
string shipPostalCode = null, string shipCountry = null, string customerID = null, int? employeeID = null, DateTime? orderDate = null,
DateTime? requiredDate = null, DateTime? shippedDate = null, int? shipVia = null, decimal? freight = null)
{
using (var context = new NorthwindEntities())
{
Order newOrder = new Order
{
ShipAddress = shipAddress,
ShipCity = shipCity,
ShipCountry = shipCountry,
ShipName = shipName,
ShippedDate = shippedDate,
ShipPostalCode = shipPostalCode,
ShipRegion = shipRegionm,
ShipVia = shipVia,
EmployeeID = employeeID,
OrderDate = orderDate,
RequiredDate = requiredDate,
Freight = freight,
CustomerID = customerID
};
context.Orders.Add(newOrder);
context.SaveChanges();
Console.WriteLine("Row is inserted.");
}
}
public static void InsertCustomer(Customer customer)
{
using(var db = new NorthwindEntities())
{
db.Customers.Add(customer);
db.SaveChanges();
}
Console.WriteLine("New customer added!");
}
public static void DeleteCustomer(string customerID)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers
.Where(cus => cus.CustomerID == customerID)
.FirstOrDefault();
db.Customers.Remove(customer);
db.SaveChanges();
}
Console.WriteLine("The customer is removed!");
}
/// <summary>
/// Creates and inserts a new customer to Northwind database.
/// </summary>
public static void InsertCustomer(string id, string companyName)
{
using (var db = new NorthwindEntities())
{
var newCustomer = new Customer()
{
CustomerID = id,
CompanyName = companyName
};
db.Customers.Add(newCustomer);
db.SaveChanges();
Console.WriteLine("Customer with id={0} created!", id);
}
}
public static void Update(NorthwindEntities northwindEntities, string id, 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)
{
Customer customer = FindById(northwindEntities, id);
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;
northwindEntities.SaveChanges();
}
/// <summary>
/// Deletes a customer by given id from Northwind database.
/// </summary>
public static void DeleteCustomer(string id)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers
.FirstOrDefault(c => c.CustomerID == id);
if (customer == null)
{
Console.WriteLine("No such customer to delete!");
return;
}
db.Customers.Remove(customer);
db.SaveChanges();
Console.WriteLine("Customer with id={0} deleted!", id);
}
}
public static Northwind.usp_GetTotalIncome_Result GetAllIncome(string companyName, DateTime startDate, DateTime endDate, NorthwindEntities context)
{
//NOTE!!!
//Make sure you execute this script only once and update your C# database model with the new prcedure
context.Database.ExecuteSqlCommand("CREATE PROCEDURE [usp_GetTotalIncome] " +
"(@companyName nvarchar(40), @dateStart datetime, @dateEnd datetime) " +
"AS " +
"SELECT s.CompanyName, " +
"SUM(p.UnitsInStock * p.UnitPrice) AS Reveniew " +
"FROM Suppliers s " +
"INNER JOIN Products p " +
"ON s.SupplierID = p.SupplierID " +
"WHERE s.CompanyName = @companyName " +
"GROUP BY s.SupplierID, s.CompanyName");
context.SaveChanges();
var result = context.usp_GetTotalIncome(companyName, startDate, endDate).FirstOrDefault();
return result;
}
public static void Insert(NorthwindEntities northwindEntities, 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)
{
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
};
northwindEntities.Customers.Add(customer);
northwindEntities.SaveChanges();
}
/// <summary>
/// Updates the Northwind database customer information by given customer id.
/// </summary>
public static void UpdateCustomer(string id, string companyName, string address, string city, string country)
{
using (var db = new NorthwindEntities())
{
var customer = db.Customers
.FirstOrDefault(c => c.CustomerID == id);
if (customer == null)
{
Console.WriteLine("No such customer to update!");
return;
}
customer.CompanyName = companyName;
customer.Address = address;
customer.City = city;
customer.Country = country;
db.SaveChanges();
Console.WriteLine("Customer with id={0} updated!", id);
}
}
public static void InsertOrders(NorthwindEntities context, params Order[] orders)
{
using (var transaction = context.Database.BeginTransaction())
{
try
{
int numberOfOrders = orders.Length;
for (int i = 0; i < numberOfOrders; i++)
{
context.Orders.Add(orders[i]);
}
context.SaveChanges();
transaction.Commit();
Console.WriteLine("Orders added.");
}
catch (Exception)
{
transaction.Rollback();
Console.WriteLine("Orders NOT added.");
}
}
}
private static void Main()
{
// 2.
var customer = new Customer
{
Address = "Perla 3",
City = "Sofia",
CompanyName = "PeshoEOOD",
ContactName = "Pesho",
ContactTitle = "Pesho",
Country = "Bulgaria",
CustomerID = "AAAAA",
Fax = "222/ 222222",
Phone = "089999999",
PostalCode = "1367",
Region = null,
};
DAO.InsertCustomer(customer);
// The last one in database
//DAO.DeleteCustomer("WOLZA");
var newCust = new Customer();
newCust.Address = "Pirotska 12";
// It must update only Address in concrete CustomerID
DAO.UpdateCustomer("AAAAA", newCust);
Console.WriteLine();
// 3.
var customers = FindAllCustomersByOrdersIn1997AndShippedToCanada();
foreach (var cust in customers)
{
Console.WriteLine(cust.ContactName);
}
Console.WriteLine();
// 4.
FindAllCustomersByOrdersIn1997AndShippedToCanadaNative();
Console.WriteLine();
// 5.
string region = "Lara";
DateTime startDate = new DateTime(1996, 10, 16);
DateTime endDate = new DateTime(1996, 11, 13);
var orders = FindAllOrdersRegionAndPeriod(region, startDate, endDate);
foreach (var order in orders)
{
Console.WriteLine(order.OrderDate + "->" + order.RequiredDate + "->" + order.ShipRegion);
}
Console.WriteLine();
// 6. Use SQL Server Management Objects (SMO) library installed with NuGet Packages
// Change server name for your computer
var server = new Server(@"DESKTOP-CTR5RI6\SQLEXPRESS");
Database newdb = new Database(server, "NorthwindTwin");
newdb.Create();
Transfer transfer = new Transfer(server.Databases["Northwind"]);
transfer.CopyAllObjects = true;
transfer.CopyAllUsers = true;
transfer.Options.WithDependencies = true;
transfer.DestinationDatabase = newdb.Name;
transfer.DestinationServer = server.Name;
transfer.DestinationLoginSecure = true;
transfer.CopySchema = true;
transfer.CopyData = true;
transfer.Options.ContinueScriptingOnError = true;
transfer.TransferData();
// 7.
var db1 = new NorthwindEntities();
var db2 = new NorthwindEntities();
var customer1 = db1.Customers.Where(cust => cust.ContactName == "Ana Trujillo").FirstOrDefault();
var customer2 = db1.Customers.Where(cust => cust.ContactName == "Ana Trujillo").FirstOrDefault();
customer1.ContactName = "Pesho";
customer2.ContactName = "Gosho";
db1.SaveChanges();
db2.SaveChanges();
db1.Dispose();
db2.Dispose();
//Concurrency control
//Entity Framework uses optimistic concurrency control (no locking by default)
//Provides automatic concurrency conflict detection and means for conflicts resolution
// 8. EmployeeExtension
}
static void Main()
{
var northwind = new NorthwindEntities();
using (northwind)
{
DataWorker.UpdateCustomerName("ALFKI", "Telerik", northwind);
Console.WriteLine(northwind.Customers.Where(customer => customer.CustomerID == "ALFKI").FirstOrDefault().CompanyName);
DataWorker.AddCustomer(new Customer() { CustomerID = "DDDDD", CompanyName = "Monsters Inc" }, northwind);
Console.WriteLine(northwind.Customers.Where(customer => customer.CustomerID == "DDDDD").FirstOrDefault().CompanyName);
DataWorker.DeleteCustomer("DDDDD", northwind);
Console.WriteLine(northwind.Customers.Any(customer => customer.CustomerID == "DDDDD"));
//All custemers from Canada and made orders in 1997
var selectedCustomers = DataWorker.GetCustomersByYearAndDestinationOfOrder("1997", "Canada", northwind);
Console.WriteLine();
Console.WriteLine("Customers from Canada, made roders in 1997:");
foreach (var customer in selectedCustomers)
{
Console.WriteLine(customer.CompanyName);
}
Console.WriteLine();
//---------------------------------------------------
//Using native query to get customers
var nativeQueryCustomers = northwind.Database.SqlQuery<Customer>("SELECT c.CompanyName, c.Address, c.City, c.ContactName, c.ContactTitle, c.Country, c.CustomerID, c.Fax, c.Phone, c.PostalCode, c.Region FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID WHERE o.ShipCountry = 'Canada' AND o.OrderDate BETWEEN Convert(datetime, '1996-12-31') AND Convert(datetime, '1998-01-01' ) GROUP BY c.CompanyName, c.Address, c.City, c.ContactName, c.ContactTitle, c.Country, c.CustomerID, c.Fax, c.Phone, c.PostalCode, c.Region").ToList();
Console.WriteLine();
Console.WriteLine("Customers from Canada, made roders in 1997:");
foreach (var customer in selectedCustomers)
{
Console.WriteLine(customer.CompanyName);
}
Console.WriteLine();
//---------------------------------------------------
//Getting orders by date and region
var selectedOrders = DataWorker.GetSalesByReagionAndPeriod("AK", DateTime.Parse("1996.01.01"), DateTime.Parse("1998.01.01"), northwind);
Console.WriteLine();
Console.WriteLine("Orders with region AK and between 01.01.1996 and 01.01.1998:");
foreach (var order in selectedOrders)
{
Console.WriteLine(order.OrderID);
}
Console.WriteLine();
//---------------------------------------------------
//Inserting several orders
DataWorker.InsertOrders(northwind, new Order(), new Order(), new Order());
//Get all income of Company
var setOfData = DataWorker.GetAllIncome("Exotic Liquids", DateTime.Parse("1990.01.01"), DateTime.Parse("2000.01.01"), northwind);
Console.WriteLine("{0} : {1}", setOfData.CompanyName, setOfData.Reveniew);
Console.WriteLine();
//---------------------------------------------------
}
//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?
//Well, it happens whatever is expected to happen - the updates are executed in the order they have in the C# code.
// First the name turns to Piglet and then to Bunny.
using (var northwindEntities1 = new NorthwindEntities())
{
using (var northwindEntities2 = new NorthwindEntities())
{
DataWorker.UpdateCustomerName("ALFKI", "Piglet", northwindEntities2);
DataWorker.UpdateCustomerName("ALFKI", "Bunny", northwindEntities1);
northwindEntities1.SaveChanges();
northwindEntities2.SaveChanges();
}
}
}
public static void UpdateCustomerName(string customerID, string customerName, NorthwindEntities context)
{
var customerToUpdate = context.Customers.Where(customer => customer.CustomerID.Equals(customerID)).FirstOrDefault();
customerToUpdate.CompanyName = customerName;
context.SaveChanges();
}
public static void DeleteCustomer(string customerID, NorthwindEntities context)
{
var customerToDelete = context.Customers.Where(customer => customer.CustomerID == customerID).FirstOrDefault();
context.Customers.Remove(customerToDelete);
context.SaveChanges();
}
public static void AddCustomer(Customer customer, NorthwindEntities context)
{
context.Customers.Add(customer);
context.SaveChanges();
}