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);
}
