public static ICollection<Order> FindSales(ExtendDbContext database, string region, DateTime startDate, DateTime endDate)
{
var sales =
from o in database.Orders
where o.ShipRegion == region && (o.OrderDate >= startDate && o.OrderDate <= endDate)
select o;
ICollection<Order> salesList = sales.ToList();
return salesList;
}
public static void InsertCustomer(ExtendDbContext database, Customer customer)
{
if (!string.IsNullOrWhiteSpace(customer.CompanyName))
{
database.Customers.Add(customer);
database.SaveChanges();
}
else
{
throw new ArgumentNullException("Company name is mandatory");
}
}
public static ICollection<Customer> FindCustomers(ExtendDbContext database, DateTime date, string country)
{
var customers =
from o in database.Orders
join c in database.Customers on o.CustomerID equals c.CustomerID
where o.OrderDate >= date && o.ShipCountry == country
select c;
ICollection<Customer> customerList = customers.ToList();
return customerList;
}
public static ICollection<string> FindCustomersSQL(ExtendDbContext database, DateTime date, string country)
{
string query = "SELECT c.ContactName " +
"FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID " +
"WHERE o.OrderDate >= {0} AND o.ShipCountry = {1}";
object[] parameters = { date, country };
var resultView = database.Database.SqlQuery<string>(query, parameters);
ICollection<string> customersList = resultView.ToList();
return customersList;
}
public static void AddNewOrder(ExtendDbContext db, ICollection<Order> orders)
{
using (TransactionScope transaction = new TransactionScope())
{
foreach (var order in orders)
{
db.Orders.Add(order);
}
db.SaveChanges();
transaction.Complete();
}
}
public static void UpdateCustomer(ExtendDbContext database, string id, string newContactName)
{
var customer = database.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
if (customer != null)
{
customer.ContactName = newContactName;
database.SaveChanges();
}
else
{
throw new InvalidOperationException("This customer doens't exists.");
}
}
public static void Delete(ExtendDbContext database, string id)
{
var customer = database.Customers.Where(x => x.CustomerID == id).FirstOrDefault();
if (customer != null)
{
database.Customers.Remove(customer);
database.SaveChanges();
}
else
{
throw new InvalidOperationException("This customer doens't exists.");
}
}
static void Main(string[] args)
{
Order firstOrder = new Order()
{
CustomerID = "ALFKI",
EmployeeID = 1,
OrderDate = DateTime.Now,
RequiredDate = DateTime.Now.AddDays(10),
ShippedDate = DateTime.Now.AddDays(9),
ShipVia = 1,
Freight = 500.0m,
ShipName = "First Ship",
ShipAddress = "Kralstvo Trqvna",
ShipCity = "Gabrovo",
ShipRegion = "Sitnqkovo",
ShipPostalCode = "it9au93",
ShipCountry = "Bulgaria"
};
Order secondOrder = new Order()
{
CustomerID = "ALFKI",
EmployeeID = 1,
OrderDate = DateTime.Now,
RequiredDate = DateTime.Now.AddDays(10),
ShippedDate = DateTime.Now.AddDays(9),
ShipVia = 1,
Freight = 500.0m,
ShipName = "Second Ship",
ShipAddress = "Kralstvo Trqvna",
ShipCity = "Blabla",
ShipRegion = "BlahBlah",
ShipPostalCode = "itu93",
ShipCountry = "Bulgaria"
};
IList<Order> orders = new List<Order>();
orders.Add(firstOrder);
orders.Add(secondOrder);
using (var database = new ExtendDbContext())
{
AddNewOrder(database, orders);
Console.WriteLine("Order(s) added successfully!");
}
}
static void Main()
{
using (var database = new ExtendDbContext())
{
/* Task 2
Customer customer = new Customer();
customer.CustomerID = "GFDSA";
customer.CompanyName = "NewCompanyName";
customer.ContactName = "NewContactName";
DataAccessObject.InsertCustomer(database, customer);
DataAccessObject.UpdateCustomer(database, "GFDSA", "JustNewContactName");
DataAccessObject.Delete(database, "GFDSA");
*/
var findedCustomers = FindCustomers(database, new DateTime(1995, 01, 01), "Canada");
foreach (var customer in findedCustomers)
{
Console.WriteLine("{0}", customer.ContactName);
}
Console.WriteLine("==========================================================");
var findedCustomersSQL = FindCustomersSQL(database, new DateTime(1995, 01, 01), "Canada");
foreach (var customer in findedCustomersSQL)
{
Console.WriteLine(customer);
}
Console.WriteLine("==========================================================");
var sales = FindSales(database, "RJ", new DateTime(1990, 1, 1), new DateTime(2000, 1, 1));
foreach (var sale in sales)
{
Console.WriteLine("Sale from {0} region from {1}", sale.ShipRegion, sale.OrderDate);
}
}
}
