//Liking an audio file is no one-way progress, the like can also be reversed.
//Therefore it must be determined whether the audio file has already been liked or nor.
//As this must already be examined for the UserMusicOverview Screen, it is no more neccessary to
//create a toggleLike function and check inside if it has already been liked in order to use the correct method
public int likeAudio(int audioID)
{
SqlConnection con = Starter.GetConnection();
String insertCommand = "INSERT INTO User_Audio_Like (UserName, AudioID) " +
"VALUES (@nick, @audioID)";
SqlCommand vSQLcommand = new SqlCommand(insertCommand, con);
vSQLcommand.Parameters.AddWithValue("@nick", this.username);
vSQLcommand.Parameters.AddWithValue("@audioID", audioID);
int insertSuccessfull = 0;
try
{
insertSuccessfull = vSQLcommand.ExecuteNonQuery();
}
catch (SqlException e) { }
con.Close();
if (insertSuccessfull > 0)
{
return(0);
}
return(-1);
}
//Deletes an AudioFile from db and removed it from the file system
public int deleteAudio(int audioID)
{
//1. Deleting the row from the db
SqlConnection con = Starter.GetConnection();
String delteteCommand = "DELETE FROM Audio WHERE ID=@ID AND uploader=@nick";
SqlCommand vSQLcommand = new SqlCommand(delteteCommand, con);
vSQLcommand.Parameters.AddWithValue("@ID", audioID);
vSQLcommand.Parameters.AddWithValue("@nick", this.username);
int insertSuccessfull = vSQLcommand.ExecuteNonQuery();
con.Close();
//2. if successful remove the file from the file system
if (insertSuccessfull > 0)
{
String SaveLocation = HttpContext.Current.Server.MapPath("AudioUpload") + "\\" + audioID + ".mp3";
if ((System.IO.File.Exists(SaveLocation)))
{
System.IO.File.Delete(SaveLocation);
return(0);
}
else
{
return(-2);
}
}
return(-1);
}
public int unlikeAudio(int audioID)
{
SqlConnection con = Starter.GetConnection();
//Users can only delete their own files,
//with including the username in the delete statement this is guaranteed
String deleteCommand = "DELETE FROM User_Audio_Like WHERE UserName=@nick AND AudioID=@audioID";
SqlCommand vSQLcommand = new SqlCommand(deleteCommand, con);
vSQLcommand.Parameters.AddWithValue("@nick", this.username);
vSQLcommand.Parameters.AddWithValue("@audioID", audioID);
int deleteSuccessfull = 0;
try
{
deleteSuccessfull = vSQLcommand.ExecuteNonQuery();
}
catch (SqlException e) { }
con.Close();
if (deleteSuccessfull > 0)
{
return(0);
}
return(-1);
}
//uploadAudio receives a file, posted from the audio management form
public int uploadAudio(HttpPostedFile file, String alias, String description)
{
if ((file == null) || (file.ContentLength <= 0))
{
//No file delivered
return(-1);
}
else if (file.ContentLength > 10485766)
{
//File size too large
return(-2);
}
else
{
//The comment below shows the possibility to use the original filename (instead of the alias)
//String fn = System.IO.Path.GetFileName(AudioManagement_FileUpload.PostedFile.FileName);
String fn;
SqlConnection con = Starter.GetConnection();
String insertCommand = "INSERT INTO Audio (fileAlias, description, uploader) " +
"VALUES (@fileAlias, @description, @uploader)";
SqlCommand vSQLcommand = new SqlCommand(insertCommand, con);
vSQLcommand.Parameters.AddWithValue("@fileAlias", alias);
vSQLcommand.Parameters.AddWithValue("@description", description);
vSQLcommand.Parameters.AddWithValue("@uploader", this.username);
int insertSuccessfull = vSQLcommand.ExecuteNonQuery();
//When the insert is successfull, the inserted ID is read into a variable
if (insertSuccessfull > 0)
{
String scalarCommand = "SELECT IDENT_CURRENT('Audio')";
vSQLcommand = new SqlCommand(scalarCommand, con);
var newAudioID = vSQLcommand.ExecuteScalar();
//patching the new filename out of the inserted id and the ".mp3" ending
fn = newAudioID.ToString() + ".mp3";
con.Close();
}
else
{
//DB insert unsuccessful
con.Close();
return(-3);
}
//FilePath for the AudioFile
String SaveLocation = HttpContext.Current.Server.MapPath("AudioUpload") + "\\" + fn;
try
{
//Trying to move the file to the specified path
file.SaveAs(SaveLocation);
return(0);
}
catch (Exception ex)
{
return(-4);
//HttpContext.Current.Response.Write("Error: " + ex.Message);
}
}
}
//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);
}
}
