}//end GetReviews public Reviews GetReviews(string MovieName) { Reviews reviews = new Reviews(); datatier = new DataAccessTier.Data(_DBFile); string sql1 = "SELECT MovieID FROM Movies WHERE MovieName = '" + MovieName + "'"; object mid = datatier.ExecuteScalarQuery(sql1); int MovieID = Convert.ToInt32(mid); //read user input string sql = string.Format(@"SELECT UserID, Rating, ReviewID FROM Reviews WHERE MovieID={0} ORDER BY Rating Desc, UserID ASC;", MovieID); //grab query DataSet result = datatier.ExecuteNonScalarQuery(sql); //get result of string //grab dataset DataTable dt = result.Tables["TABLE"]; //temp table foreach (DataRow row in dt.Rows) { int userID = Convert.ToInt32(row["UserID"]); int ratings = Convert.ToInt32(row["Rating"]); int reviewID = Convert.ToInt32(row["ReviewID"]); reviewID++; reviews.Add(new Review(reviewID, MovieID, userID, ratings)); } return(reviews); }//end GetReviews
} //done public Reviews GetReviews(string MovieName) { Reviews reviews = new Reviews(); string sql2 = string.Format(@"SELECT Rating, COUNT(Rating) as RatingCount FROM Reviews INNER JOIN Movies ON Reviews.MovieID = Movies.MovieID WHERE Movies.MovieName='{0}' GROUP BY Rating ORDER BY Rating DESC;", MovieName); DataSet ds = datatier.ExecuteNonScalarQuery(sql2); DataTable dt = ds.Tables["TABLE"]; if (dt.Rows.Count == 0) { return(null); } else { foreach (DataRow row in dt.Rows) { reviews.Add(new Review(Convert.ToInt32(row["Rating"]), Convert.ToInt32(row["RatingCount"]))); } return(reviews); } } //done
} //done public Reviews GetReviewsforAverage(string MovieName) // int reviewid, int movieid, int userid, int rating) { Reviews reviews = new Reviews(); string sql2 = string.Format(@"SELECT Reviews.ReviewID as rid, Reviews.MovieID as mid, Reviews.UserID as uid, Rating FROM Reviews INNER JOIN Movies ON Reviews.MovieID = Movies.MovieID WHERE Movies.MovieName='{0}';" , MovieName); DataSet ds = datatier.ExecuteNonScalarQuery(sql2); DataTable dt = ds.Tables["TABLE"]; if (dt.Rows.Count == 0) { return(null); } else { foreach (DataRow row in dt.Rows) { reviews.Add(new Review(Convert.ToInt32(row["rid"]), Convert.ToInt32(row["mid"]), Convert.ToInt32(row["uid"]), Convert.ToInt32(row["Rating"]))); } return(reviews); } } //done
} // done public Reviews GetReviews(int MovieID) { Reviews reviews = new Reviews(); string sql = string.Format(@"SELECT UserID, Rating FROM Reviews WHERE MovieID={0} ORDER BY Rating Desc, UserID ASC;", MovieID); DataSet ds = datatier.ExecuteNonScalarQuery(sql); DataTable dt = ds.Tables["TABLE"]; if (dt.Rows.Count == 0) { return(null); } else { foreach (DataRow row in dt.Rows) { reviews.Add(new Review(Convert.ToInt32(row["UserID"]), MovieID, Convert.ToInt32(row["Rating"]))); } return(reviews); } } //done