// Returns admin status public bool CheckIsUserisAdmin(int UserID) { string queryString = "SELECT IsAdmin FROM Users WHERE UserID=@UserID"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@UserID", UserID)); bool isAdmin = false; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { isAdmin = reader.GetBoolean(0); } } connection.Close(); return(isAdmin); }
// Returns users data public Auth_UserModel GetUsersData(int userID) { string queryString = "SELECT * FROM Users WHERE UserID=@UserID"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@UserID", userID)); Auth_UserModel user = new Auth_UserModel(); using (MySqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { user.userID = int.Parse(RemoveSpacesInString(reader[0].ToString())); user.username = RemoveSpacesInString(reader[1].ToString()); user.firstname = RemoveSpacesInString(reader[2].ToString()); user.lastname = RemoveSpacesInString(reader[3].ToString()); user.email = RemoveSpacesInString(reader[4].ToString()); user.password = RemoveSpacesInString(reader[5].ToString()); user.googleSubjectID = RemoveSpacesInString(reader[6].ToString()); user.isAdmin = Convert.ToBoolean(Convert.ToInt16(RemoveSpacesInString(reader[7].ToString()))); user.profileImageUrl = RemoveSpacesInString(reader[8].ToString()); } } connection.Close(); return(user); }
// Returns the user id if password is correct else return -1 public int CheckIfPasswordIsCorrect(string emailOrUsername, string password) { string queryString = "SELECT UserID FROM Users WHERE Email=@Email OR Username=@Username AND Password=@Password"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@Username", emailOrUsername)); cmd.Parameters.Add(new MySqlParameter("@Email", emailOrUsername)); cmd.Parameters.Add(new MySqlParameter("@Password", password)); int userID = -1; using (MySqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { userID = reader.GetInt32(0); } } connection.Close(); return(userID); }
// Return true if account is linked, add more values to add more linked options. public Tuple <bool, string> IsAccountLinkedToAlternativeAuth(int userID) { string queryString = "SELECT GoogleSubjectID FROM Users WHERE UserID=@UserID"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@UserID", userID)); string value = null; using (MySqlDataReader reader = cmd.ExecuteReader()) { if (reader.Read()) { value = RemoveSpacesInString(reader[0].ToString()); } } connection.Close(); if (string.IsNullOrWhiteSpace(value) || string.IsNullOrEmpty(value)) { return(Tuple.Create(false, "None")); } else { return(Tuple.Create(true, value)); } }
// Creates a new user and return UserID public int CreateNewUser(Auth_UserModel user) { string queryString = "INSERT INTO Users (Username, Email, FirstName, LastName, Password, GoogleSubjectID, IsAdmin) VALUES (@Username, @Email, @FirstName, @LastName, @Password, @GoogleSubjectID, @IsAdmin)"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@Username", user.username)); cmd.Parameters.Add(new MySqlParameter("@Email", user.email)); cmd.Parameters.Add(new MySqlParameter("@FirstName", user.firstname)); cmd.Parameters.Add(new MySqlParameter("@LastName", user.lastname)); cmd.Parameters.Add(new MySqlParameter("@Password", user.password)); cmd.Parameters.Add(new MySqlParameter("@GoogleSubjectID", user.googleSubjectID)); cmd.Parameters.Add(new MySqlParameter("@IsAdmin", false)); cmd.ExecuteScalar(); connection.Close(); return(CheckIfPasswordIsCorrect(user.email, user.password)); // <- returns userID }
public void DeleteAccount(int userID) { string queryString = "DELETE FROM Users WHERE UserID=@UserID"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@UserID", userID)); cmd.ExecuteScalar(); connection.Close(); }
public void UploadProfileImageUrlToDB(string url, int userID) { string queryString = "UPDATE Users SET ProfileImageUrl=@ProfileImageUrl WHERE UserID=@UserID"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@ProfileImageUrl", url)); cmd.Parameters.Add(new MySqlParameter("@UserID", userID)); cmd.ExecuteScalar(); connection.Close(); }
// Changes pasword of user public void ChangePassword(int userID, string password) { string queryString = "UPDATE Users SET Password=@Password WHERE UserID=@UserID"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@Password", password)); cmd.Parameters.Add(new MySqlParameter("@UserID", userID)); cmd.ExecuteReader(); connection.Close(); }
// Return true if email exist public bool DoesEmailExist(string email) { string queryString = "SELECT Email FROM Users WHERE Email=@Email"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@Email", email)); MySqlDataReader reader = cmd.ExecuteReader(); bool returnParam = reader.HasRows; connection.Close(); return(returnParam); }
// Return true if username exist public bool DoesUsernameExist(string username) { string queryString = "SELECT Username FROM Users WHERE Username=@Username"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@Username", username)); MySqlDataReader reader = cmd.ExecuteReader(); bool returnParam = reader.HasRows; connection.Close(); return(returnParam); }
// Update User information public void EditUser(Auth_UserModel user) { string queryString = "UPDATE Users SET Username=@Username, Email=@Email, Firstname=@Firstname, Lastname=@Lastname, Password=@Password WHERE UserID=@UserID"; MySqlConnection connection = new MySqlConnection(ConfigContex.GetConnectionString()); MySqlCommand cmd = new MySqlCommand(); cmd.Connection = connection; cmd.CommandText = queryString; connection.Open(); cmd.Parameters.Add(new MySqlParameter("@UserID", user.userID)); cmd.Parameters.Add(new MySqlParameter("@Username", user.username)); cmd.Parameters.Add(new MySqlParameter("@Email", user.email)); cmd.Parameters.Add(new MySqlParameter("@Firstname", user.firstname)); cmd.Parameters.Add(new MySqlParameter("@Lastname", user.lastname)); cmd.Parameters.Add(new MySqlParameter("@Password", user.password)); cmd.ExecuteScalar(); connection.Close(); }