// GET: Customer
public ActionResult Index()
{
List <CustomerViewModel> model = new List <CustomerViewModel>();
northwindEntities entities = new northwindEntities();
try
{
List <Customers> customer = entities.Customers.OrderBy(Customers => Customers.CustomerID).ToList();
foreach (Customers cust in customer)
{
CustomerViewModel view = new CustomerViewModel();
view.CustomerID = cust.CustomerID;
view.CompanyName = cust.CompanyName;
view.ContactName = cust.ContactName;
view.ContactTitle = cust.ContactTitle;
view.Address = cust.Address;
view.City = cust.City;
view.Region = cust.Region;
view.PostalCode = cust.PostalCode;
view.Country = cust.Country;
view.Phone = cust.Phone;
view.Fax = cust.Fax;
model.Add(view);
}
}
finally
{
entities.Dispose();
}
return(View(model));
}
static void Main(string[] args)
{
northwindEntities db1 = new northwindEntities();
northwindEntities db2 = new northwindEntities();
//Categories can be added simultaneously
for (int i = 0; i < 10; i++)
{
db1.Categories.Add(new Category()
{
CategoryName = "NewCategory" + i
});
db2.Categories.Add(new Category()
{
CategoryName = "NewCategory" + i
});
db1.SaveChanges();
db2.SaveChanges();
}
//Editin same entry
var firstEntry = db1.Categories.Where((x) => x.CategoryName == "NewCategory0").First();
firstEntry.CategoryName = "Lol";
var sameEntry = db2.Categories.Where(x => x.CategoryName == "NewCategory0").First();
db2.Categories.Remove(sameEntry);
//Removing an entry before editing it throws an exception
//db2.SaveChanges();
db1.SaveChanges();
}
public ActionResult Duzenle(Products model)
{
if (!ModelState.IsValid)
{
ModelState.AddModelError("", "Hatalı bir işlemi başarısız");
return(View(model));
}
var db = new northwindEntities();
var urun = db.Products.Find(model.ProductID);
if (urun == null)
{
return(RedirectToAction("Index", "Home"));
}
try
{
urun.ProductName = model.ProductName;
urun.UnitPrice = model.UnitPrice;
urun.UnitsInStock = model.UnitsInStock;
urun.Discontinued = model.Discontinued;
urun.CategoryID = model.CategoryID;
db.SaveChanges();
}
catch (Exception ex)
{
ModelState.AddModelError("", "Güncelle işlemi başarısız");
return(View(model));
}
//return View(model);
return(RedirectToAction("Duzenle", new { id = urun.ProductID }));
}
public ActionResult GetOrderData(string id) //id-parametri tulee MVC-mallin oletusreiteistä => mitä tarkoittaa ????
{
northwindEntities entities = new northwindEntities();
try
{
List <Orders> orders = (from o in entities.Orders
where o.CustomerID == id
orderby o.OrderDate descending
select o).ToList();
StringBuilder html = new StringBuilder(); //lisätään html-dataa taulukkoon
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.OrderDate + "</td>" +
"<td>" + order.ShipCity + "</td>" +
"<td>" + order.RequiredDate.Value.ToShortDateString() + "</td></tr>");
}
html.AppendLine("</table></td>");
// html.Append("Hello!"); //testataan, että kaikki toimii, että Ajax-kutsu toimii oikein!
var jsonData = new { html = html.ToString() }; //palautetaan json-dataa, tässä vielä "Hello!"
return(Json(jsonData, JsonRequestBehavior.AllowGet)); //palautetaan json -muodossa, HTTP-pyyntö GET, sallitaan GET
}
finally
{
entities.Dispose();
}
}
static void Main(string[] args)
{
IObjectContextAdapter context = new northwindEntities();
string cloneNorthwind = context.ObjectContext.CreateDatabaseScript();
var db = new northwindEntities();
string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = createDB.Split(splitter,
StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
db.Database.ExecuteSqlCommand(commandText);
}
SqlConnection dbConForCreatingDB = new SqlConnection(
"Server=.; " +
"Database=NorthwindTwin; " +
"Integrated Security=true");
dbConForCreatingDB.Open();
using (dbConForCreatingDB)
{
SqlCommand cloneDB = new SqlCommand(cloneNorthwind, dbConForCreatingDB);
cloneDB.ExecuteNonQuery();
}
}
// GET: Urun
public ActionResult Duzenle(int?id)
{
if (id == null)
{
return(RedirectToAction("Index", "Home"));
}
var db = new northwindEntities();
var urun = db.Products.Find(id.Value);
if (urun == null)
{
return(RedirectToAction("Index", "Home"));
}
var kategoriler = new List <SelectListItem>();
db.Categories.ToList().ForEach(x =>
kategoriler.Add(new SelectListItem()
{
Text = x.CategoryName,
Value = x.CategoryID.ToString()
})
);
ViewBag.Kategoriler = kategoriler;
return(View(urun));
}
public ActionResult Authorize(Login LoginModel)
{
northwindEntities db = new northwindEntities();
//Haetaan käyttäjän/Loginin tiedot annetuilla tunnustiedoilla tietokannasta LINQ -kyselyllä
var LoggedUser = db.Logins.SingleOrDefault(x => x.UserName == LoginModel.UserName && x.PassWord == LoginModel.PassWord);
if (LoggedUser != null)
{
ViewBag.LoginMessage = "Successfull login";
ViewBag.LoggedStatus = "In";
Session["UserName"] = LoggedUser.UserName;
Session["LoggedStatus"] = "In";
ViewBag.LoginError = 0;
//return View("Login", LoginModel);
return(RedirectToAction("Index", "Home")); //Tässä määritellään mihin onnistunut kirjautuminen johtaa --> Home/Index
}
else
{
ViewBag.LoginMessage = "Login unsuccessfull";
ViewBag.LoggedStatus = "Out";
ViewBag.LoginError = 1;
Session["LoggedStatus"] = "Out";
LoginModel.LoginErrorMessage = "Tuntematon käyttäjätunnus tai salasana.";
return(View("Index", LoginModel));
}
}
// GET: Order
public ActionResult Index()
{
List <OrdersViewModel> model = new List <OrdersViewModel>();
northwindEntities entities = new northwindEntities();
try
{
List <Orders> order = entities.Orders.OrderBy(Orders => Orders.OrderID).ToList();
foreach (Orders ord in order)
{
OrdersViewModel view = new OrdersViewModel();
view.OrderID = ord.OrderID;
view.CustomerID = ord.CustomerID;
view.EmployeeID = ord.EmployeeID;
view.OrderDate = ord.OrderDate;
view.RequiredDate = ord.RequiredDate;
view.ShippedDate = ord.ShippedDate;
view.ShipVia = ord.ShipVia;
view.Freight = ord.Freight;
view.ShipName = ord.ShipName;
view.ShipAddress = ord.ShipAddress;
view.ShipCity = ord.ShipCity;
view.ShipRegion = ord.ShipRegion;
view.ShipPostalCode = ord.ShipPostalCode;
view.ShipCountry = ord.ShipCountry;
model.Add(view);
}
}
finally
{
entities.Dispose();
}
return(View(model));
}
// GET: Customer
public ActionResult Index()
{
northwindEntities db = new northwindEntities();
List <Customer> model = db.Customers.ToList();
db.Dispose();
return(View(model));
}
public List <Customer> GetCustomersByCountry(string country)
{
//ORM
northwindEntities ORM = new northwindEntities();
//retrun via cust country
return(ORM.Customers.Where(x => x.Country.ToLower() == country.ToLower()).ToList());
}
public ActionResult ProdCards2()
{
northwindEntities db = new northwindEntities(); //needs a using sentence
List <Products> tuotteet = db.Products.ToList();
db.Dispose();
return(View(tuotteet));
}
public Product product(int id)
{
northwindEntities ORM = new northwindEntities();
List <Product> products = ORM.Products.Where(x => x.ProductID == id).ToList();
return(products[0]);
}
public int OrderCount()
{
northwindEntities entities = new northwindEntities();
int orderCount = entities.Orders.Count();
entities.Dispose();
return(orderCount);
}
// GET: Orders
public ActionResult Index(string currentFilter1, string OrderShipper, string currentOrderShipper, int?page, int?pagesize)
{
// var orders = db.Orders.Include(o => o.Customers).Include(o => o.Employees).Include(o => o.Shippers);
northwindEntities db = new northwindEntities();
var orders = from o in db.Orders.Include(o => o.Customers).Include(o => o.Employees).Include(o => o.Shippers).OrderBy(o => o.OrderDate)
select o;
//tuottekategoriahakufiltterin laitto muistiin
if ((OrderShipper != null) && (OrderShipper != "0"))
{
page = 1;
}
else
{
OrderShipper = currentOrderShipper;
}
ViewBag.currentOrderShipper = OrderShipper;
// filtering ONLY by shipper companies
if (!String.IsNullOrEmpty(OrderShipper) && (OrderShipper != "0"))
{
int para = int.Parse(OrderShipper);
orders = orders.Where(o => o.ShipVia == para);
}
//creating list for Categories dropdown
List <Shippers> lstShippers = new List <Shippers>();
//bringing shippers to apumuuttuja
var shippersList = from ship in db.Shippers
select ship;
Shippers tyhjaShipper = new Shippers(); //creating an empty category which is needed, if no category has been selected
tyhjaShipper.ShipperID = 0;
tyhjaShipper.CompanyName = "";
lstShippers.Add(tyhjaShipper);
//bringing the shippers to the list
foreach (Shippers shipper in shippersList)
{
Shippers yksiShipper = new Shippers();
yksiShipper.ShipperID = shipper.ShipperID;
yksiShipper.CompanyName = shipper.CompanyName;
lstShippers.Add(yksiShipper);
}
ViewBag.ShipperId = new SelectList(lstShippers, "ShipperID", "CompanyName", OrderShipper); //lopuks luodaan SelectLitin ja sijoitetaan sen Viewbagiin
//the paged nutget extention needs a pagenumber int and a pagesize int which we send from view but if it would be null, we change it here because you cant have 0th page
int pageSize = (pagesize ?? 10);
int pageNumber = (page ?? 1);
return(View(orders.ToPagedList(pageNumber, pageSize)));
//return View(orders);
}
public ActionResult TuoteKuvat()
{
northwindEntities db = new northwindEntities();
List <Products> model = db.Products.ToList();
db.Dispose();
return(View(model));
}
//action to get info about all customers
//no params needed because you're getting ALL info
public List <Customer> GetAllCustomers()
{
//create ORM
northwindEntities ORM = new northwindEntities();//create obj
//get all customers from the ORM
List <Customer> customerList = ORM.Customers.ToList();
//return the list of customers
return(customerList);
}
public List <Order> GetOrdersByDate(string orderID)
{
//ORM
northwindEntities ORM = new northwindEntities();
//find all orders and make a new list OrderList by date
List <Order> OrderList = ORM.Orders.ToList();
var OrderedOrderList = OrderList.OrderByDescending(o => o.OrderDate).ToList();
return(OrderList);
}
public List <Product> GetProductByNameID(int ProductID, string ProductName)
{
//URL....
//ORM
northwindEntities ORM = new northwindEntities();
List <Product> prodList = ORM.Products.ToList();
return(ORM.Products.Where(x => x.ProductID < 0 && ProductName != null).ToList());
}
public List <Product> GetAllProducts()
{
//URL...... /api/Product/GetAllProducts
//ORM
northwindEntities ORM = new northwindEntities();
List <Product> listOfProducts = ORM.Products.ToList();
return(listOfProducts);
}
public ActionResult Index2()
{
ViewBag.OmaTieto = "Tuotehaku";
northwindEntities entities = new northwindEntities();
List <Products> model = entities.Products.ToList();
entities.Dispose();
return(View(model));
}
public List <Product> GetProductByID(int ProductID)
{
//URL..../api/Product/GetProductByID?ProductID=7
//ORM
northwindEntities ORM = new northwindEntities();
// filter Products by ID
List <Product> listByProdID = ORM.Products.Where(x => x.ProductID == ProductID).ToList();
return(listByProdID);
}
static void Main(string[] args)
{
northwindEntities db = new northwindEntities();
var found = GetCustomers(db);
foreach (var row in found)
{
Console.WriteLine(row);
}
}
public List <Order> GetLastOrderByCustomer(string customerID)
{
//ORM
northwindEntities ORM = new northwindEntities();
//create a list of orders
List <Order> CustomersOrderList = ORM.Orders.ToList();
//list cust orders by descending(gives you last one first)...and select where order ==0
var OrdereredList = CustomersOrderList.OrderByDescending(o => o.OrderDate).ToList();
return(CustomersOrderList);
}
public List <Products> GetProductID(int prodID)
{
// URL: http://localhost:54659/api/Northwind/GetProductID?prodID={prodID}
// create ORM
northwindEntities ORM = new northwindEntities();
// get and return products with productID
return(ORM.Products.Where(x => x.ProductID.ToString() == prodID.ToString()).ToList());
}
// GET: Customer
public ActionResult Index()
{
northwindEntities entities = new northwindEntities(); //luodaan luokan ilmentymä eli instanssi
//malliolion = model-olion avulla haetaan tietokannasta kaikki Customers-taulun tiedot
List <Customers> model = entities.Customers.ToList();
entities.Dispose(); //vapautetaan resurssit, joita käytettiin tietokantahakuun
//näkymälle annetaan parametriksi malliolio
return(View(model));
}
// POST api/values
public void Post(Book value)
{
Book book = value;
northwindEntities context = new northwindEntities();
Product product = new Product();
product.ProductName = book.title;
context.Products.Add(product);
context.SaveChanges();
}
static void Main(string[] args)
{
northwindEntities nwDB = new northwindEntities();
var clients = from client in nwDB.Customers
select client.ContactName;
foreach (var client in clients)
{
Console.WriteLine(client);
}
}
[HttpGet] //bc it is dependant on input
public List <Customer> GetCustomersByCity(string city) //need city input
{
//URL .... provide input by adding a query
//http://localhost:portnum/api/Northwind/GetCustomersByCity?city=Detroit
//create ORM
northwindEntities ORM = new northwindEntities();
//search cust based on city
return(ORM.Customers.Where(x => x.City.ToLower() == city.ToLower()).ToList());
//return filtered cust list... included above
}
static void Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
northwindEntities db = new northwindEntities();
var found = GetSales(db, "SP", DateTime.Parse("1995-05-10"), DateTime.Parse("1996-09-10"));
foreach (var row in found)
{
Console.WriteLine(row);
}
}
static void Main(string[] args)
{
northwindEntities db = new northwindEntities();
string companyName = "Exotic Liquids";
var startDate = DateTime.Parse("1980.01.21");
var endDate = DateTime.Parse("1999.01.22");
var income = db.uspSupplierIncome(companyName, startDate, endDate).First();
Console.WriteLine(income);
}
