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