protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
// GET: Customers
public ActionResult Index()
{
//nimiavaruuden määrittäminen, lisää automaattisesti using-määreen tiedoston alkuun, jolloin voidaan hyödyntää entiteettimallia.
NorthwindDataEntities entities = new NorthwindDataEntities();
//haetaan lista asiakasolioista = tietokantakysely
List <CustomerViewModel> model = new List <CustomerViewModel>();
try
{
List <Customers> customer = entities.Customers.OrderBy(Customers => Customers.CustomerID).ToList();
// muodostetaan näkymämalli tietokannan rivien pohjalta
foreach (Customers customers in customer)
{
CustomerViewModel view = new CustomerViewModel();
view.CustomerID = customers.CustomerID;
view.CompanyName = customers.CompanyName;
view.ContactName = customers.ContactName;
view.ContactTitle = customers.ContactTitle;
view.Address = customers.Address;
view.City = customers.City;
view.Region = customers.Region;
view.PostalCode = customers.PostalCode;
view.Country = customers.Country;
view.Phone = customers.Phone;
view.Fax = customers.Fax;
model.Add(view);
}
}
finally
{
//Dispose metodi vapauttaa tietokantakyselyn, ei välttämätön, mutta hyvää ohjelmointitapaa
entities.Dispose();
}
//malliolion välittäminen (=lista asiakkaita) näkymälle
return(View(model)); //annetaan return lauseelle yksi parametri
#region
//Index
//List<Customers> model = new List<Customers>();
//try
//{
// NorthwindDataEntities entities = new NorthwindDataEntities();
// model = entities.Customers.ToList();
// entities.Dispose();
//}
//catch (Exception ex)
//{
// ViewBag.ErrorMessage = ex.GetType() + ": " + ex.Message;
//}
//return View(model);
#endregion
}
// GET: CustomerDynamic
public ActionResult IndexCustomer()
{
ViewBag.OmaTieto = "ABC123";
NorthwindDataEntities entities = new NorthwindDataEntities();
List <Customers> model = entities.Customers.ToList();
entities.Dispose();
return(View(model));
}
// GET: Sales
public ActionResult Index()
{
NorthwindDataEntities entities = new NorthwindDataEntities();
try
{
List <Customers> model = entities.Customers.ToList();
return(View(model));
}
finally
{
entities.Dispose();
}
}
// GET: Orders
public ActionResult Index()
{
List <OrderViewModel> model = new List <OrderViewModel>();
NorthwindDataEntities entities = new NorthwindDataEntities();
try
{
List <Orders> orders = entities.Orders.OrderBy(Orders => Orders.OrderDate).ToList();
// muodostetaan näkymämalli tietokannan rivien pohjalta
foreach (Orders order in orders)
{
OrderViewModel view = new OrderViewModel();
view.OrderID = order.OrderID;
view.OrderDate = order.OrderDate;
view.RequiredDate = order.RequiredDate;
view.ShippedDate = order.ShippedDate;
view.ShipVia = order.ShipVia;
view.Freight = order.Freight;
view.ShipName = order.ShipName;
view.ShipAddress = order.ShipAddress;
view.ShipCity = order.ShipCity;
view.ShipRegion = order.ShipRegion;
view.ShipPostalCode = order.ShipPostalCode;
view.ShipCountry = order.ShipCountry;
view.EmployeeID = order.EmployeeID;
view.LastName = order.Employees?.LastName;
view.CustomerID = order.CustomerID;
view.CompanyName = order.Customers?.CompanyName;
ViewBag.CompanyName = new SelectList((from c in db.Customers select new { CustomerID = c.CustomerID, CompanyName = c.CompanyName }), "CustomerID", "CompanyName", null);
ViewBag.LastName = new SelectList((from e in db.Employees select new { EmployeeID = e.EmployeeID, LastName = e.LastName }), "EmployeeID", "LastName", null);
ViewBag.ShipVia = new SelectList((from o in db.Orders select new { OrderID = o.OrderID, ShipVia = o.ShipVia }), "OrderID ", "ShipVia", null);
model.Add(view);
}
}
finally
{
entities.Dispose();
}
return(View(model));
}//Index
}//Index
// GET: Orders/Details/5
public ActionResult Details(int?id)
{
OrderViewModel model = new OrderViewModel();
NorthwindDataEntities entities = new NorthwindDataEntities();
try
{
Orders order = entities.Orders.Find(id);
if (order == null)
{
return(HttpNotFound());
}
// muodostetaan näkymämalli tietokannan rivien pohjalta
OrderViewModel view = new OrderViewModel();
view.OrderID = order.OrderID;
view.OrderDate = order.OrderDate;
view.RequiredDate = order.RequiredDate;
view.ShippedDate = order.ShippedDate;
view.ShipVia = order.ShipVia;
view.Freight = order.Freight;
view.ShipName = order.ShipName;
view.ShipAddress = order.ShipAddress;
view.ShipCity = order.ShipCity;
view.ShipRegion = order.ShipRegion;
view.ShipPostalCode = order.ShipPostalCode;
view.ShipCountry = order.ShipCountry;
view.EmployeeID = order.EmployeeID;
view.LastName = order.Employees?.LastName;
view.CustomerID = order.CustomerID;
view.CompanyName = order.Customers?.CompanyName;
ViewBag.CompanyName = new SelectList((from c in db.Customers select new { CustomerID = c.CustomerID, CompanyName = c.CompanyName }), "CustomerID", "CompanyName", null);
ViewBag.LastName = new SelectList((from e in db.Employees select new { EmployeeID = e.EmployeeID, LastName = e.LastName }), "EmployeeID", "LastName", null);
ViewBag.ShipVia = new SelectList((from o in db.Orders select new { OrderID = o.OrderID, ShipVia = o.ShipVia }), "OrderID ", "ShipVia", null);
model = view;
}
finally
{
entities.Dispose();
}
return(View(model));
}//details
public ActionResult Update(Customers cust)
{
NorthwindDataEntities entities = new NorthwindDataEntities();
string id = cust.CustomerID;
bool OK = false;
// onko kyseessä muokkaus vai uuden lisääminen?
if (id == "(uusi)")
{
// kyseessä on uuden asiakkaan lisääminen, kopioidaan kentät
Customers dbItem = new Customers()
{
CustomerID = cust.CompanyName.Substring(0, 5).Trim().ToUpper(), //luodaan CustomerID CompanyNamen 5:stä ensimmäisesta kirjaimesta isoilla kirjaimilla
CompanyName = cust.CompanyName,
Address = cust.Address,
City = cust.City
};
// tallennus tietokantaan
entities.Customers.Add(dbItem);
entities.SaveChanges();
OK = true;
}
else
{
// muokkaus, haetaan id:n perusteella riviä tietokannasta
Customers dbItem = (from c in entities.Customers
where c.CustomerID == id
select c).FirstOrDefault();
if (dbItem != null)
{
dbItem.CompanyName = cust.CompanyName;
dbItem.Address = cust.Address;
dbItem.City = cust.City;
// tallennus tietokantaan
entities.SaveChanges();
OK = true;
}
}
entities.Dispose();
return(Json(OK, JsonRequestBehavior.AllowGet));
}
// GET: Employees/Details/5
public ActionResult Details(int?id)
{
EmployeesViewModel model = new EmployeesViewModel();
NorthwindDataEntities entities = new NorthwindDataEntities();
try
{
Employees employee = entities.Employees.Find(id);
if (employee == null)
{
return(HttpNotFound());
}
// muodostetaan näkymämalli tietokannan rivien pohjalta
EmployeesViewModel view = new EmployeesViewModel();
view.EmployeeID = employee.EmployeeID;
view.LastName = employee.LastName;
view.FirstName = employee.FirstName;
view.Title = employee.Title;
view.TitleOfCourtesy = employee.TitleOfCourtesy;
view.BirthDate = employee.BirthDate;
view.HireDate = employee.HireDate;
view.Address = employee.Address;
view.City = employee.City;
view.Region = employee.Region;
view.PostalCode = employee.PostalCode;
view.Country = employee.Country;
view.HomePhone = employee.HomePhone;
view.Extension = employee.Extension;
view.Photo = employee.Photo;
view.Notes = employee.Notes;
view.ReportsTo = employee.ReportsTo;
view.PhotoPath = employee.PhotoPath;
ViewBag.ReportsTo = new SelectList((from e in db.Employees select new { EmployeeID = e.EmployeeID, ReportsTo = e.ReportsTo }), "EmployeeID", "ReportsTo", null);
model = view;
}
finally
{
entities.Dispose();
}
return(View(model));
}//details
// GET: Employees
public ActionResult Index()
{
List <EmployeesViewModel> model = new List <EmployeesViewModel>();
NorthwindDataEntities entities = new NorthwindDataEntities();
try
{
List <Employees> employees = entities.Employees.OrderBy(Employees => Employees.LastName).ToList();
// muodostetaan näkymämalli tietokannan rivien pohjalta
foreach (Employees employee in employees)
{
EmployeesViewModel view = new EmployeesViewModel();
view.EmployeeID = employee.EmployeeID;
view.LastName = employee.LastName;
view.FirstName = employee.FirstName;
view.Title = employee.Title;
view.TitleOfCourtesy = employee.TitleOfCourtesy;
view.BirthDate = employee.BirthDate;
view.HireDate = employee.HireDate;
view.Address = employee.Address;
view.City = employee.City;
view.Region = employee.Region;
view.PostalCode = employee.PostalCode;
view.Country = employee.Country;
view.HomePhone = employee.HomePhone;
view.Extension = employee.Extension;
//view.Photo = employee.Photo;
view.Notes = employee.Notes;
view.ReportsTo = employee.ReportsTo;
//view.PhotoPath = employee.PhotoPath;
ViewBag.ReportsTo = new SelectList((from e in db.Employees select new { EmployeeID = e.EmployeeID, ReportsTo = e.ReportsTo }), "EmployeeID", "ReportsTo", null);
model.Add(view);
}
}
finally
{
entities.Dispose();
}
return(View(model));
}//Index
public JsonResult GetSingleCustomer(string id)
{
NorthwindDataEntities entities = new NorthwindDataEntities();
var model = (from c in entities.Customers
where c.CustomerID == id
select new
{
CustomerID = c.CustomerID,
CompanyName = c.CompanyName,
Address = c.Address,
City = c.City
}).FirstOrDefault();
string json = JsonConvert.SerializeObject(model);
entities.Dispose();
return(Json(json, JsonRequestBehavior.AllowGet));
}
public ActionResult DeleteCustomer(string id)
{
NorthwindDataEntities entities = new NorthwindDataEntities();
// etsitään id:n perusteella asiakasrivi kannasta
bool OK = false;
Customers dbItem = (from c in entities.Customers
where c.CustomerID == id
select c).FirstOrDefault();
if (dbItem != null)
{
// tietokannasta poisto
entities.Customers.Remove(dbItem);
entities.SaveChanges();
OK = true;
}
entities.Dispose();
return(Json(OK, JsonRequestBehavior.AllowGet));
}
// GET: Customers/Details/5
public ActionResult Details(string id)
{
CustomerViewModel model = new CustomerViewModel();
NorthwindDataEntities entities = new NorthwindDataEntities();
try
{
Customers customer = entities.Customers.Find(id);
if (customer == null)
{
return(HttpNotFound());
}
// muodostetaan näkymämalli tietokannan rivien pohjalta
CustomerViewModel view = new CustomerViewModel();
view.CustomerID = customer.CustomerID;
view.CompanyName = customer.CompanyName;
view.ContactName = customer.ContactName;
view.ContactTitle = customer.ContactTitle;
view.Address = customer.Address;
view.City = customer.City;
view.Region = customer.Region;
view.PostalCode = customer.PostalCode;
view.Country = customer.Country;
view.Phone = customer.Phone;
view.Fax = customer.Fax;
//ViewBag.ReportsTo = new SelectList((from e in db.Employees select new { EmployeeID = e.EmployeeID, ReportsTo = e.ReportsTo }), "EmployeeID", "ReportsTo", null);
model = view;
}
finally
{
entities.Dispose();
}
return(View(model));
}//details
public ActionResult GetOrders(string id)
{
NorthwindDataEntities entities = new NorthwindDataEntities();
List <Orders> orders = (from o in entities.Orders
where o.CustomerID == id
select o).ToList();
entities.Dispose();
List <SimpleOrderData> result = new List <SimpleOrderData>();
foreach (Orders order in orders)
{
SimpleOrderData data = new SimpleOrderData();
data.CustomerId = order.CustomerID;
data.OrderId = order.OrderID;
data.OrderDate = order.OrderDate.ToString();
result.Add(data);
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
public JsonResult GetList()
{
NorthwindDataEntities entities = new NorthwindDataEntities();
//List<Customer> model = entities.Customers.ToList();
var model = (from c in entities.Customers
select new
{
CustomerID = c.CustomerID,
CompanyName = c.CompanyName,
Address = c.Address,
City = c.City
}).ToList();
string json = JsonConvert.SerializeObject(model);
entities.Dispose();
Response.Expires = -1;
Response.CacheControl = "no-cache";
return(Json(json, JsonRequestBehavior.AllowGet));
}
public ActionResult GetOrderData(string id)
{
NorthwindDataEntities entities = new NorthwindDataEntities();
try
{
List <Orders> orders = (from o in entities.Orders
where o.CustomerID == id
orderby o.OrderDate descending
select o).ToList();
StringBuilder html = new StringBuilder();
html.AppendLine("<td colspan=\"5\">" +
"<table class=\"table table-striped\">");
foreach (Orders order in orders)
{
html.AppendLine("<tr><td>" +
order.OrderDate.Value.ToShortDateString() + "</td>" +
"<td>" + order.OrderID + "</td>" +
"<td>" + order.ShipCity + "</td>" +
"<td>" + order.RequiredDate.Value.ToShortDateString() + "</td></tr>");
}
html.AppendLine("</table></td>");
//html.Append("Hello World");
var jsonData = new { html = html.ToString() };
return(Json(jsonData, JsonRequestBehavior.AllowGet));
}
finally
{
entities.Dispose();
}
}