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