public static string AddTagsToReview(ReviewObject review, List <TagObject> Tags) { if (Tags.Count < 1) { return(""); } StringBuilder SQL_Statement = new StringBuilder(); //Insert into ReviewTagsSelect ID as ReviewID, TagID from MusicReview join Tags where Title = 'test' and Tags.Name = "action" SQL_Statement.Append("Insert into ReviewTags(ReviewID,TagID) Select ID as ReviewID, TagID from MusicReview join Tags " + "where Title = '" + review.Title + "' " + "and Artist = '" + review.Artist + "' " + "and Release_Date = " + review.Release_Date + " " + "and "); SQL_Statement.Append("(Name = '" + Tags[0].Name + "' "); for (int x = 1; x < Tags.Count; x++) { SQL_Statement.Append(" OR Name = '" + Tags[x].Name + "'"); } SQL_Statement.Append(")"); return(SQL_Statement.ToString()); }
public Review_Reference(ReviewObject ro) { Title = ro.Title; Artist = ro.Artist; Rating = ro.Rating; Release_Date = ro.Release_Date; Review_Date = ro.Review_Date; Album = ro.Album; }
//Delete Review From Database public static string DeleteReview(ReviewObject ro) { StringBuilder SQL_Statement = new StringBuilder(); SQL_Statement.Append("Delete from ReviewTags where ReviewID in (Select ID as ReviewID from MusicReview where Title = '" + ro.Title + "' and " + "Artist = '" + ro.Artist + "'" + " and Release_Date = " + ro.Release_Date.ToString() + "); "); SQL_Statement.Append("Delete from MusicReview where Title = '" + ro.Title + "' and " + "Artist = '" + ro.Artist + "'" + " and Release_Date = " + ro.Release_Date.ToString()); return(SQL_Statement.ToString()); }
public ReviewObject(ReviewObject givenReview) { this.Title = givenReview.Title; this.Release_Date = givenReview.Release_Date; this.Review_Date = givenReview.Review_Date; this.Album = givenReview.Album; this.Artist = givenReview.Artist; this.Rating = givenReview.Rating; this.Review = givenReview.Review; this.File_Path = givenReview.File_Path; this.Tags = givenReview.Tags; }
private static string RemoveTagsFromReview(ReviewObject new_ro, List <TagObject> tagsToRemove) { if (tagsToRemove.Count < 1) { return(""); } StringBuilder SQL_Statement = new StringBuilder(); SQL_Statement.Append("Delete from ReviewTags where ReviewID in (Select ID as ReviewID from MusicReview where Title = '" + new_ro.Title + "' AND Artist = '" + new_ro.Artist + "' AND Release_Date = " + new_ro.Release_Date + ") " + " AND TagID in (Select TagID from Tags where Name = '" + tagsToRemove[0].Name + "'"); for (int x = 1; x < tagsToRemove.Count; x++) { SQL_Statement.Append(" OR Name = '" + tagsToRemove[x].Name + "'"); } return(SQL_Statement.ToString() + ")"); }
public void UpdateReview(ReviewObject old_ro, ReviewObject new_ro) { ExecuteNoResponseSQL(SqlStatements.UpdateReview(old_ro, new_ro)); }
public void deleteReview(ReviewObject ro) { ExecuteNoResponseSQL(SqlStatements.DeleteReview(ro)); }
//Adding stuff to the database public void AddReview(ReviewObject rvw) { ExecuteNoResponseSQL(SqlStatements.AddReview(rvw)); }
//Update a review entry; public static string UpdateReview(ReviewObject old_ro, ReviewObject new_ro) { StringBuilder SQL_Statement = new StringBuilder(); SQL_Statement.Append("Update MusicReview Set "); if (new_ro.Title != null) { SQL_Statement.Append("Title = '" + new_ro.Title + "', "); } if (new_ro.Release_Date != -1) { SQL_Statement.Append("Release_Date = " + new_ro.Release_Date + ", "); } if (new_ro.Review_Date != -1) { SQL_Statement.Append("Review_Date = " + new_ro.Review_Date + ", "); } if (new_ro.Album != null) { SQL_Statement.Append("Album = '" + new_ro.Album + "', "); } if (new_ro.Artist != null) { SQL_Statement.Append("Artist = '" + new_ro.Artist + "', "); } if (new_ro.Rating != (float)-1.0) { SQL_Statement.Append("Rating = " + new_ro.Rating.ToString() + ", "); } if (new_ro.Review != null) { SQL_Statement.Append("Review = '" + new_ro.Review.Replace('\'', '"') + "', "); } if (new_ro.File_Path != null) { SQL_Statement.Append("File_Path = '" + new_ro.File_Path + "', "); } SQL_Statement.Remove(SQL_Statement.Length - 2, 2); //Remove the uneeeded "," SQL_Statement.Append(" Where Title = '" + old_ro.Title + "' AND Artist = '" + old_ro.Artist + "' AND Release_Date = " + old_ro.Release_Date.ToString() + "; "); //Update the Tags. List <TagObject> TagsToAdd = new List <TagObject>(); List <TagObject> TagsToRemove = new List <TagObject>(); List <TagObject> New_Tags = new_ro.getTags(); List <TagObject> Old_Tags = old_ro.getTags(); foreach (TagObject Tag in New_Tags) { if (!Old_Tags.Contains(Tag)) { TagsToAdd.Add(Tag); Old_Tags.Remove(Tag); } } foreach (TagObject Tag in Old_Tags) { if (!New_Tags.Contains(Tag)) { TagsToRemove.Add(Tag); } } //Insert the new tags, remove the old tags. SQL_Statement.Append(AddTagsToReview(new_ro, TagsToAdd) + "; "); SQL_Statement.Append(RemoveTagsFromReview(new_ro, TagsToRemove) + "; "); return(SQL_Statement.ToString()); }
//Add Tags to Review public static string AddTagsToReview(ReviewObject review) { List <TagObject> tags = review.getTags(); return(AddTagsToReview(review, tags)); }
//Add Review to table public static string AddReview(ReviewObject review) { return(NonNullFields(review) + FieldData(review) + "; " + AddTagsToReview(review)); string NonNullFields(ReviewObject ro) { StringBuilder SQL_Statement = new StringBuilder(); SQL_Statement.Append("Insert into MusicReview("); SQL_Statement.Append("Title,"); if (ro.Release_Date != -1) { SQL_Statement.Append("Release_Date,"); } SQL_Statement.Append("Review_Date,"); if (!ro.Album.Equals("")) { SQL_Statement.Append("Album,"); } SQL_Statement.Append("Artist,"); if (ro.Rating != (float)-1.0) { SQL_Statement.Append("Rating,"); } if (!ro.Review.Equals("")) { SQL_Statement.Append("Review,"); } if (!ro.File_Path.Equals("")) { SQL_Statement.Append("File_Path,"); } SQL_Statement.Remove(SQL_Statement.Length - 1, 1); //Remove the uneeeded "," SQL_Statement.Append(") "); return(SQL_Statement.ToString()); } string FieldData(ReviewObject ro) { StringBuilder SQL_Statement = new StringBuilder(); SQL_Statement.Append(" VALUES("); SQL_Statement.Append('\'' + ro.Title + "\',"); if (ro.Release_Date != -1) { SQL_Statement.Append(ro.Release_Date.ToString() + ','); } SQL_Statement.Append(ro.Review_Date.ToString() + ','); if (!ro.Album.Equals("")) { SQL_Statement.Append('\'' + ro.Album + "\',"); } SQL_Statement.Append('\'' + ro.Artist + "\',"); if (ro.Rating != (float)-1.0) { SQL_Statement.Append(ro.Rating.ToString() + ','); } if (!ro.Review.Equals("")) { SQL_Statement.Append('\'' + ro.Review.Replace('\'', '"') + "\',"); } //Really LAZY sanitaztion this could be made much more robust. if (!ro.File_Path.Equals("")) { SQL_Statement.Append('\'' + ro.File_Path + "\',"); } SQL_Statement.Remove(SQL_Statement.Length - 1, 1); //Remove the uneeeded "," SQL_Statement.Append(")"); return(SQL_Statement.ToString()); } }