public static UserLogin GetLoggedInUserByUsername(string username) { string query = "SELECT * FROM qryLogin Where UserName = @username"; OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbParameter user = new OleDbParameter(); user.ParameterName = "@username"; user.Value = username.Trim(); cmd.Parameters.Add(user); UserLogin loggedIn = new UserLogin(); OleDbDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { loggedIn.Roles = new List <string>(); while (reader.Read()) { loggedIn.Username = (string)reader["UserName"]; loggedIn.Roles.Add((string)reader["RoleDescription"]); } } reader.Close(); conn.Close(); return(loggedIn); }
public static Meal GetOneMeal(int id) { string query = string.Format(QUERY_GET_ONE, id); OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); Meal oneMeal = ReaderToMeal(reader); reader.Close(); conn.Close(); return(oneMeal); }
public static Role GetOneRole(int id) { string query = string.Format(QUERY_GET_ONE, id); OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); Role role = ReaderToRole(reader); reader.Close(); conn.Close(); return(role); }
public static User GetOneUserByUsername(string username) { string query = string.Format(QUERY_GET_ONE_BY_USERNAME, username); OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); User user = ReaderToUser(reader); reader.Close(); conn.Close(); return(user); }
public static Ingredient GetOneIngredient(int id) { string query = string.Format(QUERY_GET_ONE, id); OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataReader reader = cmd.ExecuteReader(); reader.Read(); Ingredient ingredient = ReaderToIngredient(reader); reader.Close(); conn.Close(); return(ingredient); }
//dont need ANOTHER ReaderToMealIngredient method as we arent modelleing tblMealIngredients public static List <MealIngredient> GetIngredientsForMeal(int mealID) { string query = string.Format(QUERY_SELECT_INGREDIENTS_FOR_MEAL, mealID); OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <MealIngredient> ingredients = new List <MealIngredient>(); while (reader.Read()) { ingredients.Add(ReaderToMealIngredient(reader)); } reader.Close(); conn.Close(); return(ingredients); }
public static List <Role> GetAllRoles() { OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(QUERY_GET_ALL, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <Role> roles = new List <Role>(); while (reader.Read()) { roles.Add(ReaderToRole(reader)); } reader.Close(); conn.Close(); return(roles); }
//dont need ANOTHER ReaderToOrderMeal method as we arent modelleing tblOrderMeals public static List <OrderMeal> GetMealsForOrder(int orderID) { string query = string.Format(QUERY_SELECT_MEALS_FOR_ORDER, orderID); OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <OrderMeal> meals = new List <OrderMeal>(); while (reader.Read()) { meals.Add(ReaderToOrderMeal(reader)); } reader.Close(); conn.Close(); return(meals); }
//dont need ANOTHER ReaderToUserRole method as we arent modelleing tblUserRoles public static List <UserRole> GetRolesForDisplay() { OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(QUERY_SELECT_ROLES_FOR_DISPLAY, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <UserRole> userRoles = new List <UserRole>(); while (reader.Read()) { userRoles.Add(ReaderToUserRole(reader)); } reader.Close(); conn.Close(); return(userRoles); }
public static List <Meal> GetAllMeals() { OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(QUERY_GET_ALL, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <Meal> listOfMeals = new List <Meal>(); while (reader.Read()) { listOfMeals.Add(ReaderToMeal(reader)); } reader.Close(); conn.Close(); return(listOfMeals); }
public static List <User> GetAllUsers() { OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(QUERY_GET_ALL, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <User> users = new List <User>(); while (reader.Read()) { users.Add(ReaderToUser(reader)); } reader.Close(); conn.Close(); return(users); }
public static List <OrderDisplay> GetOrdersToDisplay() { OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(QUERY_GET_ALL_TO_DISPLAY, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <OrderDisplay> listOfOrders = new List <OrderDisplay>(); while (reader.Read()) { listOfOrders.Add(ReaderToOrderDisplay(reader)); } reader.Close(); conn.Close(); return(listOfOrders); }
public static List <UserRole> GetRolesForUser(int userID) { string query = string.Format(QUERY_SELECT_ROLES_FOR_USERS, userID); OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <UserRole> userRoles = new List <UserRole>(); while (reader.Read()) { userRoles.Add(ReaderToUserRole(reader)); } reader.Close(); conn.Close(); return(userRoles); }
public static List <Ingredient> GetAllIngredients() { OleDbConnection conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(QUERY_GET_ALL, conn); OleDbDataReader reader = cmd.ExecuteReader(); List <Ingredient> ingredients = new List <Ingredient>(); while (reader.Read()) { ingredients.Add(ReaderToIngredient(reader)); } reader.Close(); conn.Close(); return(ingredients); }
public static bool ValidateCredentials(string username, string password) { bool returnValue = false; if (IsAlphaNumeric(username)) { OleDbConnection conn = null; try { string query = "SELECT COUNT(*) FROM tblUsers WHERE UserName = @username and UserPassword = @password"; conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbParameter user = new OleDbParameter(); user.ParameterName = "@username"; user.Value = username.Trim(); cmd.Parameters.Add(user); OleDbParameter pass = new OleDbParameter(); pass.ParameterName = "@password"; pass.Value = Crypto.SHA256(password + SALT); //pass.Value = PasswordHash.ArgonHashString(password.Trim()); //pass.Value = CryptoHash.Sha256(password.Trim() + SALT); cmd.Parameters.Add(pass); int results = (int)cmd.ExecuteScalar(); if (results > 0) { returnValue = true; } } catch (Exception ex) { //log error } conn.Close(); } else { //Log error - username is not alpha-numeric } return(returnValue); }
public static int UpdateExistingUser(User user) { //string query = string.Format(QUERY_UPDATE, user.UserName, user.UserPassword, user.UserID); //int results = DatabaseManager.ExecuteNonQuery(query); //return results; string hashedPassword = AuthenticationManager.HashPassword(user.UserPassword); var conn = DatabaseManager.GetOpenedConnection(); var query = "UPDATE tblUsers SET UserName = ?, UserPassword = ? WHERE UserID = ?"; var cmd = new OleDbCommand(query, conn); cmd.Parameters.AddWithValue("?", user.UserName); cmd.Parameters.AddWithValue("?", hashedPassword); cmd.Parameters.AddWithValue("?", user.UserID); int results = cmd.ExecuteNonQuery(); return(results); }
public static int InsertNewUser(User user) { //string hashedPassword = hash.ToString(); //string query = string.Format(QUERY_INSERT, user.UserName, hash); //int results = DatabaseManager.ExecuteNonQuery(query); // converted into parameterised query: using regular style had issues because of OleDB string hashedPassword = AuthenticationManager.HashPassword(user.UserPassword); var conn = DatabaseManager.GetOpenedConnection(); var query = "INSERT INTO tblUsers (UserName, UserPassword) VALUES (?, ?)"; var cmd = new OleDbCommand(query, conn); cmd.Parameters.AddWithValue("?", user.UserName); //cmd.Parameters.AddWithValue("?", user.UserPassword); cmd.Parameters.AddWithValue("?", hashedPassword); int results = cmd.ExecuteNonQuery(); return(results); }
public static UserLogin ValidateLoginCredentials(string username, string password) { UserLogin loggedIn = new UserLogin(); if (IsAlphaNumeric(username)) { OleDbConnection conn = null; string query = "SELECT * FROM qryLogin Where UserName = @username AND UserPassword = @password"; conn = DatabaseManager.GetOpenedConnection(); OleDbCommand cmd = new OleDbCommand(query, conn); OleDbParameter user = new OleDbParameter(); user.ParameterName = "@username"; user.Value = username.Trim(); cmd.Parameters.Add(user); OleDbParameter pass = new OleDbParameter(); pass.ParameterName = "@password"; pass.Value = Crypto.SHA256(password + SALT); cmd.Parameters.Add(pass); OleDbDataReader reader = cmd.ExecuteReader(); if (reader.HasRows) { loggedIn.Roles = new List <string>(); while (reader.Read()) { loggedIn.Username = (string)reader["UserName"]; loggedIn.Roles.Add((string)reader["RoleDescription"]); } } reader.Close(); conn.Close(); } return(loggedIn); }