public async Task <DbCommitResponse> UpdateUserAsync(int userId, string email, string fullname, string avatar)
{
try
{
DbCommitResponse resp = null;
string query =
"UPDATE user SET email = @email, fullname = @fullname, avatar = @avatar WHERE id = @id;";
using (MySqlConnection connection = new MySqlConnection(this.dbConString))
{
await connection.OpenAsync();
using (MySqlCommand cmd = new MySqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@fullname", fullname);
cmd.Parameters.AddWithValue("@avatar", avatar);
cmd.Parameters.AddWithValue("@id", userId);
await cmd.ExecuteNonQueryAsync();
resp = new DbCommitResponse
{
IsSuccess = true,
Message = "User updated"
};
}
}
return(resp);
}
catch (Exception ex) { return(new DbCommitResponse {
IsSuccess = false, Message = ex.Message
}); }
}
public async Task <DbCommitResponse> DeleteAllCommentAsync(int fnbId)
{
try
{
DbCommitResponse resp = null;
string query =
"DELETE FROM fnb_comment WHERE fnb_id = @id;";
using (MySqlConnection connection = new MySqlConnection(this.dbConString))
{
await connection.OpenAsync();
using (MySqlCommand cmd = new MySqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@id", fnbId);
await cmd.ExecuteNonQueryAsync();
resp = new DbCommitResponse
{
IsSuccess = true,
Message = $"All comment deleted"
};
}
}
return(resp);
}
catch (Exception ex)
{
return(new DbCommitResponse
{
IsSuccess = false,
Message = ex.Message
});
}
}
public async Task <DbCommitResponse> ChangePasswordAsync(int userId, string newPasswordHash)
{
try
{
DbCommitResponse resp = null;
string query =
"UPDATE user SET password_hash = @password WHERE id = @id;";
using (MySqlConnection connection = new MySqlConnection(this.dbConString))
{
await connection.OpenAsync();
using (MySqlCommand cmd = new MySqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@password", newPasswordHash);
cmd.Parameters.AddWithValue("@id", userId);
resp = new DbCommitResponse
{
IsSuccess = true,
Message = $"{await cmd.ExecuteNonQueryAsync()} record updated (password)"
};
}
}
return(resp);
}
catch (Exception ex) { return(new DbCommitResponse {
IsSuccess = false, Message = ex.Message
}); }
}
public async Task <DbCommitResponse> UpdateLoginTimeAsync(int userId)
{
try
{
DbCommitResponse resp = null;
string query =
"UPDATE user SET last_login_time = NOW() WHERE id = @id;";
using (MySqlConnection connection = new MySqlConnection(this.dbConString))
{
await connection.OpenAsync();
using (MySqlCommand cmd = new MySqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@id", userId);
await cmd.ExecuteNonQueryAsync();
resp = new DbCommitResponse
{
IsSuccess = true,
Message = $"Last login time updated for user ID '{userId}'"
};
}
}
return(resp);
}
catch (Exception ex) { return(new DbCommitResponse {
IsSuccess = false, Message = ex.Message
}); }
}
public async Task <DbCommitResponse> AddFnbCommentAsync(int fnbId, int commenterId, string comment, int rating, BaseRating baseRating)
{
try
{
DbCommitResponse resp = null;
string query =
"INSERT into fnb_comment (fnb_id, commenter_id, comment, rating, base_rating) VALUES " +
"(@a, @b, @c, @d, @e);";
using (MySqlConnection connection = new MySqlConnection(this.dbConString))
{
await connection.OpenAsync();
using (MySqlCommand cmd = new MySqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@a", fnbId);
cmd.Parameters.AddWithValue("@b", commenterId);
cmd.Parameters.AddWithValue("@c", comment);
cmd.Parameters.AddWithValue("@d", rating);
cmd.Parameters.AddWithValue("@e", baseRating);
await cmd.ExecuteNonQueryAsync();
resp = new DbCommitResponse
{
IsSuccess = true,
Message = $"Comment successfully submitted"
};
}
}
return(resp);
}
catch (Exception ex)
{
return(new DbCommitResponse
{
IsSuccess = false,
Message = ex.Message
});
}
}
public async Task <DbCommitResponse> AddUserAsync(string username, string passwordHash, string passwordSalt, string email, UserRole role)
{
try
{
DbCommitResponse resp = null;
string query =
"INSERT into user (username, password_hash, password_salt, email, role) VALUES " +
"(@a, @b, @c, @d, @e);";
using (MySqlConnection connection = new MySqlConnection(this.dbConString))
{
await connection.OpenAsync();
using (MySqlCommand cmd = new MySqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@a", username);
cmd.Parameters.AddWithValue("@b", passwordHash);
cmd.Parameters.AddWithValue("@c", passwordSalt);
cmd.Parameters.AddWithValue("@d", email);
cmd.Parameters.AddWithValue("@e", role);
resp = new DbCommitResponse
{
IsSuccess = true,
Message = $"{await cmd.ExecuteNonQueryAsync()} record successfully created"
};
}
}
return(resp);
}
catch (Exception ex)
{
return(new DbCommitResponse
{
IsSuccess = false,
Message = ex.Message
});
}
}
public async Task <DbCommitResponse> AddFnbAsync(Fnb fnb)
{
try
{
DbCommitResponse resp = null;
string query =
"INSERT into fnb (name, fnb_type) VALUES " +
"(@a, @b);";
using (MySqlConnection connection = new MySqlConnection(this.dbConString))
{
await connection.OpenAsync();
using (MySqlCommand cmd = new MySqlCommand(query, connection))
{
cmd.Parameters.AddWithValue("@a", fnb.Name);
cmd.Parameters.AddWithValue("@b", fnb.FnbType);
resp = new DbCommitResponse
{
IsSuccess = true,
Message = $"{await cmd.ExecuteNonQueryAsync()} record successfully created"
};
}
}
return(resp);
}
catch (Exception ex)
{
return(new DbCommitResponse
{
IsSuccess = false,
Message = ex.Message
});
}
}
