public List <OrderHistory> GetUserOrders(int userID) { ElectronicsShopDBEntities1 dBEntities = new ElectronicsShopDBEntities1(); List <OrderHistory> orders = dBEntities.OrderHistory.AsNoTracking().Where(o => o.CustomerID == userID).ToList(); return(orders); }
public List <OrderDetails> ViewDetails(int OrderID) { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); List <OrderDetails> details = dbEntities.OrderDetails.AsNoTracking().Where(d => d.OrderID == OrderID).ToList(); return(details); }
public void DeleteOrder(int OrderID) { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); OrderHistory orderToRemove = dbEntities.OrderHistory.Where(x => x.OrderID == OrderID).FirstOrDefault(); dbEntities.OrderDetails.RemoveRange(orderToRemove.OrderDetails.ToList()); dbEntities.OrderHistory.Remove(orderToRemove); dbEntities.SaveChanges(); }
public async Task <int> Login(string username, string password) //Login operation (stored procedure with ADO NET) { Users currentUser = new Users(); using (SqlConnection sqlConn = new SqlConnection(strConn)) { using (SqlCommand sqlComm = new SqlCommand("uspLogin")) { try { SqlParameter usernameParam = new SqlParameter("@pLoginName", System.Data.SqlDbType.NVarChar); SqlParameter passwordParam = new SqlParameter("@pPassword", System.Data.SqlDbType.NVarChar); SqlParameter responseParam = new SqlParameter("@responseMessage", System.Data.SqlDbType.Int); usernameParam.Value = username; passwordParam.Value = password; responseParam.Value = null; sqlComm.Parameters.Add(usernameParam); sqlComm.Parameters.Add(passwordParam); sqlComm.Parameters.Add(responseParam); responseParam.Direction = System.Data.ParameterDirection.ReturnValue; //parameter that is supposed to be returned as a result of a sql procedure sqlComm.CommandType = System.Data.CommandType.StoredProcedure; sqlComm.Connection = sqlConn; if (sqlComm.Connection.State == System.Data.ConnectionState.Closed) { sqlComm.Connection.Open(); } await sqlComm.ExecuteNonQueryAsync(); int loginResult = (Int32)responseParam.Value; // if 0 success , if 1 - invalid password , if 2 - Invalid Username ElectronicsShopDBEntities1 dbEntites = new ElectronicsShopDBEntities1(); if (loginResult == 0) { currentUser = dbEntites.Users.Where(u => u.Username == username).FirstOrDefault(); Application.Current.Properties["CurrentUser"] = currentUser; return(0); // Successful login } return(loginResult); //Failed Login } catch { sqlConn.Close(); return(2); } } } }
public int GetProductStock(int productID) { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); if (productID != -1) { return(dbEntities.Products.AsNoTracking().Where(x => x.ProductID == productID).FirstOrDefault().StockQuantity); } return(0); }
public void DeleteOrderDetail(int detailID) { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); //var sql = "DELETE FROM OrderDetails Where OrderDetailID = {0}"; OrderDetails detailsToRemove = dbEntities.OrderDetails.Where(o => o.OrderDetailID == detailID).FirstOrDefault(); dbEntities.OrderDetails.Remove(detailsToRemove); dbEntities.SaveChanges(); //this has to be saved because the price of the details is computed in the database decimal newPrice = GetNewPrice(detailsToRemove.OrderID); dbEntities.OrderHistory.Where(o => o.OrderID == detailsToRemove.OrderID).FirstOrDefault().TotalPrice = newPrice; dbEntities.SaveChanges(); }
public void ModifyOrder(OrderHistory modifiedOrder) { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); OrderHistory orderToModify = dbEntities.OrderHistory.Where(o => o.OrderID == modifiedOrder.OrderID).FirstOrDefault(); orderToModify.EmployeeID = modifiedOrder.EmployeeID; orderToModify.DeliveryAddress = modifiedOrder.DeliveryAddress; orderToModify.ReceiptNumber = modifiedOrder.ReceiptNumber; orderToModify.Status = modifiedOrder.Status; orderToModify.OrderDate = modifiedOrder.OrderDate; dbEntities.SaveChanges(); }
public Dictionary <int, string> GetCustomers() { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); var getUsersQuery = from Users in dbEntities.Users orderby Users.FirstName select new { Users.UserID, Users.FirstName, Users.LastName }; Dictionary <int, string> wholeNames = new Dictionary <int, string>(); foreach (var user in getUsersQuery.ToList()) { string wholeName = $"{user.FirstName} {user.LastName}"; wholeNames[user.UserID] = wholeName; } return(wholeNames); }
public Dictionary <int, string> GetProducts() { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); var getProductsQuery = from Products in dbEntities.Products orderby Products.ProductName select new { Products.ProductID, Products.ProductName }; Dictionary <int, string> productNames = new Dictionary <int, string>(); foreach (var product in getProductsQuery.ToList()) { productNames[product.ProductID] = product.ProductName; } return(productNames); }
public Dictionary <int, string> GetEmployees() { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); var getEmployeesQuery = from Employees in dbEntities.Employees orderby Employees.FirstName select new { Employees.EmployeeID, Employees.FirstName, Employees.LastName }; //won't be tracked as it doesn't contain an instance of an Employee object Dictionary <int, string> wholeNames = new Dictionary <int, string>(); foreach (var employee in getEmployeesQuery.ToList()) { string wholeName = $"{employee.FirstName} {employee.LastName}"; wholeNames[employee.EmployeeID] = wholeName; } return(wholeNames); }
public OrderDetails AddOrderDetail(OrderDetails details) { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); details.UnitPrice = GetProductPrice(details.ProductID); dbEntities.OrderDetails.Add(details); dbEntities.SaveChanges(); //this has to be saved because the price of the details is computed in the database decimal newPrice = GetNewPrice(details.OrderID); dbEntities.OrderHistory.Where(x => x.OrderID == details.OrderID).FirstOrDefault().TotalPrice = newPrice; dbEntities.SaveChanges(); return(details); }
public void AddOrder(Users currentUser, OrderHistory order, OrderDetails details) { ElectronicsShopDBEntities1 dbEntities = new ElectronicsShopDBEntities1(); order.OrderDate = DateTime.Now; order.ReceiptNumber = GetRandomString(13); order.CustomerID = currentUser.UserID; order.CurrencyCode = "USD"; details.OrderID = order.OrderID; details.UnitPrice = GetProductPrice(details.ProductID); order.OrderDetails.Add(details); dbEntities.OrderHistory.Add(order); dbEntities.OrderDetails.Add(details); dbEntities.SaveChanges(); // Add total price after automatically calculating it in DB decimal newPrice = GetNewPrice(order.OrderID); order.TotalPrice = newPrice; //still tracked dbEntities.SaveChanges(); }