//Return all Audio Files of a specific user
public List <Audio> getAudioFiles()
{
SqlConnection con = Starter.GetConnection();
SqlCommand cmd = new SqlCommand("SELECT [ID], [fileAlias], [description], [uploader] FROM [Audio] WHERE [uploader]=@nick", con);
cmd.Parameters.AddWithValue("@nick", this.username);
SqlDataReader reader = cmd.ExecuteReader();
List <Audio> userFiles = new List <Audio>();
while (reader.Read())
{
Audio audio = new Audio((int)reader["ID"], (String)reader["fileAlias"], (String)reader["description"]);
userFiles.Add(audio);
}
con.Close();
return(userFiles);
}
public List <Audio> getLikedAudio()
{
//The usersLiked variable includes the information which user has liked the audio file
SqlConnection con = Starter.GetConnection();
SqlCommand cmd = new SqlCommand("SELECT ual.AudioID AS ID, a.fileAlias, a.description FROM [User_Audio_like] ual INNER JOIN [Audio] a ON ual.AudioID = a.ID INNER JOIN [User] u ON ual.UserName = u.nickname WHERE u.nickname=@nickname", con);
cmd.Parameters.AddWithValue("@nickname", this.username);
SqlDataReader reader = cmd.ExecuteReader();
List <Audio> userLikedAudio = new List <Audio>();
while (reader.Read())
{
Audio audio = new Audio((int)reader["ID"], (String)reader["fileAlias"], (String)reader["description"]);
userLikedAudio.Add(audio);
}
con.Close();
return(userLikedAudio);
}
//The loginUser Method looks up in the DB table for corresponding User Credentials
//The parameter password is still text in clear
public static LoggedInUser loginUser(String nickname, String password)
{
SqlConnection con = Starter.GetConnection();
SqlCommand cmd = new SqlCommand("SELECT [nickname], [email], [password] FROM [User] WHERE [nickname]=@nick AND [password]=@passwd", con);
cmd.Parameters.AddWithValue("@nick", nickname);
//The hashed password is placed in the select statement
cmd.Parameters.AddWithValue("@passwd", Encrypt.Pwd_Encode(password));
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
String email = (String)reader["email"];
con.Close();
return(new LoggedInUser(nickname, email, password));
}
con.Close();
return(null);
}
public static List <User> getAllUsers()
{
//Creating the SQLCOnnection for the DB Statement
SqlConnection con = Starter.GetConnection();
SqlCommand cmd = new SqlCommand("SELECT [nickname], [email] FROM [User]", con);
SqlDataReader reader = cmd.ExecuteReader();
List <User> allUsers = new List <User>();
while (reader.Read())
{
//The User Class constructor is internal, allowing the starter class to access the constructor
User user = new User((String)reader["nickname"], (String)reader["email"]);
allUsers.Add(user);
}
//Closing the connection after usage is VERY(!!) important,
//otherwise it becomes impossible to open new connections elsewhere
con.Close();
return(allUsers);
}
//The registerUser method creates a new row in the user table
//If the username already exists, the insert will fail and return null
public static LoggedInUser registerUser(String username, String email, String password)
{
SqlConnection con = Starter.GetConnection();
String insertCommand = "INSERT INTO [User] (nickname, email, password) " +
"VALUES (@nick, @email, @passwd)";
SqlCommand vSQLcommand = new SqlCommand(insertCommand, con);
vSQLcommand.Parameters.AddWithValue("@nick", username);
vSQLcommand.Parameters.AddWithValue("@email", email);
vSQLcommand.Parameters.AddWithValue("@passwd", Encrypt.Pwd_Encode(password));
int insertSuccessfull = vSQLcommand.ExecuteNonQuery();
con.Close();
if (insertSuccessfull > 0)
{
return(new LoggedInUser(username, email, password));
}
else
{
return(null);
}
}
