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!");
}
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 Insert(string customerID, string companyName, string contactName, string contactTitle,
string address, string city, string region, string postalCode, string country, string phone, string fax)
{
var newCustomer = 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(newCustomer);
northwindEntities.SaveChanges();
}
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
}
/// <summary>
/// Create a new Customer object.
/// </summary>
/// <param name="customerID">Initial value of the CustomerID property.</param>
/// <param name="companyName">Initial value of the CompanyName property.</param>
public static Customer CreateCustomer(global::System.String customerID, global::System.String companyName)
{
Customer customer = new Customer();
customer.CustomerID = customerID;
customer.CompanyName = companyName;
return customer;
}
/// <summary>
/// Deprecated Method for adding a new object to the Customers EntitySet. Consider using the .Add method of the associated ObjectSet<T> property instead.
/// </summary>
public void AddToCustomers(Customer customer)
{
base.AddObject("Customers", customer);
}
public static void AddCustomer(Customer customer, NorthwindEntities context)
{
context.Customers.Add(customer);
context.SaveChanges();
}
public static IEnumerable<Customer> GetCustomers()
{
Customer[] customers = new Customer[] {
Hans,
Marc,
new Customer {
ID = -111,
Name = "Bob",
Orders = new List<Order> {
new Order() {
ShipAddress = "address 0",
Freight = 5
},
new Order() {
ShipAddress = "address 1",
Freight = 123
}}},
new Customer() {
ID = 222,
Name = "Dave",
Orders = new List<Order> {
new Order() {
ShipAddress = "city 0",
Freight = 5
},
new Order() {
ShipAddress = "place 2",
Freight = 199
}
}
} ,
new Customer() {
ID = -3333,
Name = "Abigail",
Orders = new List<Order> {
new Order() {
ShipAddress = "town 0",
Freight = 5
},
new Order() {
ShipAddress = "place 44",
Freight = 199
}
}
}
};
return customers;
}
public void Insert(string CustomerID,string CompanyName,string ContactName,string ContactTitle,string Address,string City,string Region,string PostalCode,string Country,string Phone,string Fax)
{
Customer item = new Customer();
item.CustomerID = CustomerID;
item.CompanyName = CompanyName;
item.ContactName = ContactName;
item.ContactTitle = ContactTitle;
item.Address = Address;
item.City = City;
item.Region = Region;
item.PostalCode = PostalCode;
item.Country = Country;
item.Phone = Phone;
item.Fax = Fax;
item.Save(UserName);
}
private static void Main()
{
consoleMio.Setup();
consoleMio.PrintHeading("Task 2 - Data Access Model Usages And Tests ");
var accessManager = new DataAccessManager();
// Get All Customers
consoleMio.WriteLine("Get All customers", ConsoleColor.DarkBlue);
var getAllTask = accessManager.GetAllCustomersAsync();
consoleMio.WriteLine(KEY_CHECK_MESSAGE, ConsoleColor.DarkGreen);
while (!getAllTask.IsCompleted)
{
// Async activity simulation
Console.WriteLine(NOT_YET);
Console.ReadKey(true);
}
Console.WriteLine(QUERY_SUCCESS_FORMAT, !getAllTask.IsFaulted);
foreach (var cust in getAllTask.Result)
{
Console.WriteLine(cust.City);
}
Console.WriteLine();
// Add Customer
consoleMio.WriteLine("Add new customer", ConsoleColor.DarkBlue);
var customer = new Customer()
{
CustomerID = "DZUAD",
Address = "Kolio Ganchev",
City = "Stara Zagora",
CompanyName = "DZU",
ContactName = "Con tac",
ContactTitle = "Bai",
Country = "Bulgaria",
PostalCode = "6000",
Phone = "(359) 42-877-677",
};
var addCustomerTask = accessManager.AddCustomerAsync(customer);
WaitSqlServerCheck(addCustomerTask.Wait(5000));
// Update Customer
consoleMio.WriteLine("Update existing customer", ConsoleColor.DarkBlue);
customer.Address = "Samara 3";
customer.Fax = "(359) 42-877-578";
var updateCustomerTask = accessManager.UpdateCustomerAsync(customer);
WaitSqlServerCheck(updateCustomerTask.Wait(5000));
// Delete Customer
consoleMio.WriteLine("Delete existing customer", ConsoleColor.DarkBlue);
var deleteCustomerTask = accessManager.DeleteCustomerAsync(customer);
WaitSqlServerCheck(deleteCustomerTask.Wait(5000));
/* 3. Write a method that finds all customers who have orders made in 1997 and shipped to Canada. */
consoleMio.PrintHeading(
"Task 3 Write a method that finds all customers who have orders made in 1997 and shipped to Canada ");
var customersTask = accessManager.GetCustomersWithOrdersBetweenDatesAsync(new DateTime(1997, 1, 1),
new DateTime(1997, 12, 31, 23, 59, 59), shipCountry: "Canada");
consoleMio.WriteLine("Result: ", ConsoleColor.DarkGreen);
if (customersTask.Wait(5000))
{
foreach (var candidate in customersTask.Result)
{
Console.WriteLine(candidate.CompanyName);
}
}
WaitSqlServerCheck(!customersTask.IsFaulted);
/* 4. Implement previous by using native SQL query and executing it through the DbContext. */
consoleMio.PrintHeading(
"Task 4 Implement previous by using native SQL query and executing it through the DbContext. ");
// Try adding 'DROP' statement in the string to test my simple defence
string sqlQuery = "SELECT DISTINCT CompanyName " +
"FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID " +
"WHERE YEAR(o.OrderDate) = 1997 AND o.ShipCountry = 'Canada'";
var customersSqlTask = accessManager.ExecuteNativeSqlQueryAsync(sqlQuery);
consoleMio.WriteLine("Result: ", ConsoleColor.DarkGreen);
while (!customersSqlTask.IsCompleted)
{
// Clumsy task waiter
}
if (!customersSqlTask.IsFaulted)
{
foreach (var item in customersSqlTask.Result)
{
Console.WriteLine(item);
}
}
WaitSqlServerCheck(!customersSqlTask.IsFaulted);
}
