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