/// <summary>
/// 3.Write a method that finds all customers who have
/// orders made in 1997 and shipped to Canada.
/// </summary>
static void FindCustomers(string country, int year)
{
NorthwndEntities context = new NorthwndEntities();
using (context)
{
var customers =
context.Customers.Join(context.Orders,
(c => c.CustomerID),
(o => o.CustomerID),
(c, o) => new
{
Customer = c.ContactName,
ShipCountry = o.ShipCountry,
OrderDate = o.OrderDate
}).Where(o => o.ShipCountry == country && o.OrderDate.Value.Year == year);
//var customers =
// from customer in context.Customers
// join order in context.Orders
// on customer.CustomerID equals order.CustomerID
// where order.ShipCountry == country && order.OrderDate.Value.Year == year
// select customer.ContactName;
foreach (var customer in customers)
{
Console.WriteLine("Name: {0}", customer.Customer);
}
}
}
// localhost:2631/Product/FilterProducts/?PriceFilter=15
// localhost:2631/Product/FilterProducts/?SearchString=en&PriceFilter=15
public JsonResult FilterProducts(string SearchString, decimal?priceFilter = 0)
{
//if (priceFilter == null)
//{
// Response.StatusCode = 400;
//}
using (var db = new NorthwndEntities())
{
var products = db.Products.Where(p => p.UnitPrice >= priceFilter && p.Discontinued == false)
.OrderBy(pn => pn.ProductName)
.Select(p => new
{
p.ProductID,
p.ProductName,
p.QuantityPerUnit,
p.UnitPrice,
p.UnitsInStock
});
if (!string.IsNullOrEmpty(SearchString))
{
products.Where(p => p.ProductName.Contains(SearchString));
}
var productDTO = products.ToList();
return(Json(productDTO, JsonRequestBehavior.AllowGet));
}
//return Json(new { }, JsonRequestBehavior.AllowGet);
}
/// <summary>
/// 3.Write a method that finds all customers who have
/// orders made in 1997 and shipped to Canada.
/// </summary>
static void FindCustomers(string country, int year)
{
NorthwndEntities context = new NorthwndEntities();
using (context)
{
var customers =
context.Customers.Join(context.Orders,
(c => c.CustomerID),
(o => o.CustomerID),
(c, o) => new
{
Customer = c.ContactName,
ShipCountry = o.ShipCountry,
OrderDate = o.OrderDate
}).Where(o => o.ShipCountry == country && o.OrderDate.Value.Year == year);
//var customers =
// from customer in context.Customers
// join order in context.Orders
// on customer.CustomerID equals order.CustomerID
// where order.ShipCountry == country && order.OrderDate.Value.Year == year
// select customer.ContactName;
foreach (var customer in customers)
{
Console.WriteLine("Name: {0}", customer.Customer);
}
}
}
public ActionResult Account()
{
//ViewBag.CustomerID = UserAccount.GetUserID();
using (NorthwndEntities db = new NorthwndEntities())
{
// find customer using CustomerID (stored in authentication ticket)
Customer customer = db.Customers.Find(UserAccount.GetUserID());
// display original values in textboxes when customer is editing data
CustomerEdit EditCustomer = new CustomerEdit()
{
CompanyName = customer.CompanyName,
ContactName = customer.ContactName,
ContactTitle = customer.ContactTitle,
Address = customer.Address,
City = customer.City,
Region = customer.Region,
PostalCode = customer.PostalCode,
Country = customer.Country,
Phone = customer.Phone,
Fax = customer.Fax,
Email = customer.Email
};
return(View(EditCustomer));
}
}
public ActionResult Products()
{
using (var db = new NorthwndEntities())
{
return(View(db.Products.Where(p => !p.Discontinued).ToList()));
}
}
public JsonResult FilterProducts(int?id, string SearchString, decimal?PriceFilter)
{
// if there is no PriceFilter, return Http Bad Request
if (PriceFilter == null)
{
Response.StatusCode = 400;
return(Json(new { }, JsonRequestBehavior.AllowGet));
}
using (NorthwndEntities db = new NorthwndEntities())
{
var Products = db.Products.Where(p => p.Discontinued == false).ToList();
if (id != null)
{
Products = Products.Where(p => p.CategoryID == id).ToList();
}
if (!String.IsNullOrEmpty(SearchString))
{
Products = Products.Where(p => p.ProductName.Contains(SearchString)).ToList();
}
var ProductDTOs = (from p in Products.Where(p => p.UnitPrice >= PriceFilter)
orderby p.ProductName
select new {
p.ProductID,
p.ProductName,
p.QuantityPerUnit,
p.UnitPrice,
p.UnitsInStock
}).ToList();
return(Json(ProductDTOs, JsonRequestBehavior.AllowGet));
}
}
public JsonResult AddToCart(CartDTO cartDTO)
{
if (!ModelState.IsValid)
{
Response.StatusCode = 400;
return(Json(new { }, JsonRequestBehavior.AllowGet));
}
Cart sc = new Cart();
sc.ProductID = cartDTO.ProductID;
sc.CustomerID = cartDTO.CustomerID;
using (NorthwndEntities db = new NorthwndEntities())
{
Cart cart = db.Carts.SingleOrDefault(c => c.ProductID == cartDTO.ProductID &&
c.CustomerID == cartDTO.CustomerID);
if (cart != null)
{
// cart exists
cart.Quantity += cartDTO.Quantity;
}
else
{
// cart does not exist
sc.Quantity = cartDTO.Quantity;
db.Carts.Add(sc);
}
db.SaveChanges();
return(Json(sc, JsonRequestBehavior.AllowGet));
}
}
public JsonResult ClearCart(CartDTO cartDTO)
{
if (!ModelState.IsValid)
{
Response.StatusCode = 400;
return(Json(new { }, JsonRequestBehavior.AllowGet));
}
Cart sc = new Cart();
sc.CustomerID = cartDTO.CustomerID;
using (NorthwndEntities db = new NorthwndEntities())
{
List <Cart> carts = db.Carts.Where(c => c.CustomerID == cartDTO.CustomerID).ToList();
if (carts != null)
{
foreach (Cart c in carts)
{
db.Carts.Remove(c);
}
}
db.SaveChanges();
return(Json(sc, JsonRequestBehavior.AllowGet));
}
}
public ActionResult Account()
{
if (Request.Cookies["role"].Value != "customer")
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
DataLayer.Customer customer;
using (var db = new NorthwndEntities())
{
customer = db.Customers.Find(UserAccount.GetUserId());
}
//ViewBag.CustomerId = customer.CustomerID;
var customerEdit = Mapper.Map <CustomerEdit>(customer);
//CustomerEdit customerEdit = new CustomerEdit
//{
// CompanyName = customer.CompanyName,
// ContactName = customer.ContactName,
// ContactTitle = customer.ContactTitle,
// Address = customer.Address,
// City = customer.City,
// Region = customer.Region,
// PostalCode = customer.PostalCode,
// Country = customer.Country,
// Phone = customer.Phone,
// Fax = customer.Fax,
// Email = customer.Email
//};
return(View(customerEdit));
}
public ActionResult Register(Customer customer)
{
using (NorthwndEntities db = new NorthwndEntities())
{
// verify and not to allow duplicate company names
// var result = db.Customers.Any(c => c.CompanyName == customer.CompanyName);
if (db.Customers.Any(c => c.CompanyName == customer.CompanyName))
{
return(View());
}
//encrypt psw
customer.UserGuid = System.Guid.NewGuid();
customer.Password = UserAccount.HashSHA1(customer.Password + customer.UserGuid);
// saving data to DB
db.Customers.Add(customer);
db.SaveChanges();
return(RedirectToAction("Index", "Home"));
}
//return View(); instead of this return we do return redirectToAction
}
public List <Customer> GetCustomer()
{
NorthwndEntities db = new NorthwndEntities();
List <Customer> Custos = (from c in db.Customers select c).ToList();
return(Custos);
}
public List <Product> GetProductByName(string id)
{
NorthwndEntities db = new NorthwndEntities();
List <Product> prod = (from p in db.Products where p.ProductName.Contains(id) select p).ToList();
return(prod);
}
public Product GetProduct(int id)
{
NorthwndEntities db = new NorthwndEntities();
Product prod = (from p in db.Products where p.ProductID == id select p).Single();
return(prod);
}
//http://localhost:2631/product/filterproducts/?PriceFilter=15
public JsonResult FilterProducts(decimal?PriceFilter, string SearchString)
{
using (var db = new NorthwndEntities())
{
var products = db.Products
.Where(p => p.UnitPrice >= PriceFilter && p.Discontinued == false)
.OrderBy(pn => pn.ProductName)
.Select(p => new
{
p.ProductID,
p.ProductName,
p.UnitPrice,
p.UnitsInStock,
p.QuantityPerUnit,
p.Supplier.SupplierID
});
if (!string.IsNullOrEmpty(SearchString))
{
products.Where(p => p.ProductName.Contains(SearchString));
}
var productDTO = products.ToList();
return(Json(productDTO, JsonRequestBehavior.AllowGet));
}
}
public ActionResult Account()
{
if (Request.Cookies["role"].Value != "customer")
{
return(new HttpStatusCodeResult(HttpStatusCode.BadRequest));
}
var customerID = UserAccount.GetUserID();
using (NorthwndEntities db = new NorthwndEntities())
{
Customer customer = db.Customers.Find(customerID);
CustomerEdit customerEdit = new CustomerEdit
{//inline array
CompanyName = customer.CompanyName,
ContactName = customer.ContactName,
ContactTitle = customer.ContactTitle,
Address = customer.Address,
City = customer.City,
Region = customer.Region,
PostalCode = customer.PostalCode,
Country = customer.Country,
Phone = customer.Phone,
Fax = customer.Fax,
Email = customer.Email
};
return(View(customerEdit));
}
//return View();
}
static void Main(string[] args)
{
NorthwndEntities context = new NorthwndEntities();
foreach (var item in context.Employees)
{
Console.WriteLine(item.Teritories.First());
}
}
public List <Product> GetCatalog()
{
NorthwndEntities db = new NorthwndEntities();
List <Product> products = db.Products.ToList();
db.P
return(products);
}
public static void InsertCustomer(Customer customer)
{
NorthwndEntities database = new NorthwndEntities();
using (database)
{
database.Customers.Add(customer);
database.SaveChanges();
}
}
public List <Customer> GetCustomerByLastName(string name)
{
NorthwndEntities db = new NorthwndEntities();
List <Customer> Custos = (from c in db.Customers
where c.ContactName.Split(' ').Contains(name)
select c).ToList();
return(Custos);
}
public Customer GetCustomerByID(string ID)
{
NorthwndEntities db = new NorthwndEntities();
Customer Custo = (from c in db.Customers
where c.CustomerID == ID
select c).Single();
return(Custo);
}
public List <Customer> GetCustomerByCountry(string country)
{
NorthwndEntities db = new NorthwndEntities();
List <Customer> Custo = (from c in db.Customers
where c.Country.Contains(country)
select c).ToList();
return(Custo);
}
static void Main(string[] args)
{
NorthwndEntities context = new NorthwndEntities();
foreach (var item in context.Employees)
{
Console.WriteLine(item.Teritories.First());
}
}
// GET: Customer/SignIn
public ActionResult SignIn()
{
using (NorthwndEntities db = new NorthwndEntities())
{
// create drop-down list box for company name
ViewBag.CustomerID = new SelectList(db.Customers.OrderBy(c => c.CompanyName), "CustomerID", "CompanyName").ToList();
}
return(View());
}
public static void DeleteCustomer(Customer customer)
{
NorthwndEntities database = new NorthwndEntities();
using (database)
{
var cust = database.Customers.Find(customer.CustomerID);
database.Customers.Remove(cust);
database.SaveChanges();
}
}
// GET: Product/Discount
public ActionResult Index()
{
// retrieve a list of discounts
using (NorthwndEntities db = new NorthwndEntities())
{
// Filter by date
DateTime now = DateTime.Now;
return(View(db.Discounts.Where(s => s.StartTime <= now && s.EndTime > now).ToList()));
}
}
public ActionResult SearchResults(FormCollection Form)
{
string SearchString = Form["SearchString"];
ViewBag.Filter = "Product";
using (NorthwndEntities db = new NorthwndEntities())
{
return(View("Product", db.Products.Where(p => p.ProductName.Contains(SearchString) && p.Discontinued == false).OrderBy(p => p.ProductName).ToList()));
}
}
private static void AddOrder(Order order, Order_Detail orderDetail)
{
using (NorthwndEntities context = new NorthwndEntities())
{
using (TransactionScope transaction = new TransactionScope())
{
context.Orders.Add(order);
context.SaveChanges();
orderDetail.OrderID = context.Orders.Where(x => x.ShipName == "Last ship").First().OrderID;
context.Order_Details.Add(orderDetail);
context.SaveChanges();
transaction.Complete();
}
}
}
/// <summary>
/// 4.Implement previous by using native SQL query
/// and executing it through the DbContext.
/// </summary>
static void FindCustomersSqlQuery(string country, int year)
{
NorthwndEntities context = new NorthwndEntities();
using (context)
{
string query =
"SELECT ContactName FROM Customers c " +
"JOIN Orders o ON c.CustomerId = o.CustomerId " +
"WHERE o.ShipCountry = {0} AND YEAR(o.OrderDate) = {1}";
object[] parameters = { country, year };
var customers = context.Database.SqlQuery<string>(query, parameters);
foreach (var customer in customers)
{
Console.WriteLine("Name: {0}", customer);
}
}
}
public static void UpdateCustomer(Customer oldCustomer, Customer newCustomer)
{
NorthwndEntities database = new NorthwndEntities();
using (database)
{
var customer = database.Customers.Find(oldCustomer.CustomerID);
customer.Address = newCustomer.Address;
customer.City = newCustomer.City;
customer.CompanyName = newCustomer.CompanyName;
customer.ContactName = newCustomer.ContactName;
customer.ContactTitle = newCustomer.ContactTitle;
customer.Country = newCustomer.Country;
customer.CustomerDemographics = newCustomer.CustomerDemographics;
customer.Fax = newCustomer.Fax;
customer.Orders = newCustomer.Orders;
customer.Phone = newCustomer.Phone;
customer.PostalCode = newCustomer.PostalCode;
customer.Region = newCustomer.Region;
database.SaveChanges();
}
}
static void Main(string[] args)
{
NorthwndEntities context = new NorthwndEntities();
NorthwndEntities context2 = new NorthwndEntities();
//One time the the city of customer is Sofia, one time is Kaspichan! This is not correct!
using (context)
{
using (context2)
{
Customer customer1 = context.Customers.Find("BLAUS");
Customer customer2 = context2.Customers.Find("BLAUS");
customer1.City = "Sofia";
customer2.City = "Kaspichan";
context2.SaveChanges();
context.SaveChanges();
}
}
}
/// <summary>
/// Write a method that finds all the sales by specified region
/// and period (start / end dates).
/// </summary>
static void FindSalesByRegionAndPeriod(string region, DateTime startDate, DateTime endDate)
{
NorthwndEntities context = new NorthwndEntities();
using (context)
{
var orders = context.Orders
.Where(x => x.ShipRegion == region && x.OrderDate > startDate && x.OrderDate < endDate)
.Select(x => x.ShipName);
foreach (var order in orders)
{
Console.WriteLine(order);
}
}
}
