public EpisodeRating GetEpisodeRating(int epsiodeId, int userid)
{
string query = "SELECT id, rating, episodeid, userid FROM Episoderating WHERE episodeid= @episodeid AND userid= @userid";
EpisodeRating rating = new EpisodeRating();
using (var conn = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand(query, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@episodeid", epsiodeId);
cmd.Parameters.AddWithValue("@userid", userid);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
rating = new EpisodeRating
{
Id = (int)reader["id"],
Rating = (int)reader["rating"],
Userid = (int)reader["userid"],
Episode = new Episode
{
Id = (int)reader["episodeid"]
}
};
}
return(rating);
}
}
}
}
public void CreateRating(EpisodeRating rating)
{
//string query = "UPDATE Episoderating SET rating=@rating WHERE episodeid=@episodeid AND userid=@userid";
string query = "IF EXISTS(SELECT * FROM Episoderating WHERE episodeid = @episodeid AND userid=@userid) UPDATE Episoderating SET rating=@rating WHERE episodeid=@episodeid AND userid=@userid ELSE INSERT INTO Episoderating([rating], [episodeid], [userid]) VALUES(@rating, @episodeid, @userid)";
using (var conn = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand(query, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@rating", rating.Rating);
cmd.Parameters.AddWithValue("@episodeid", rating.Episode.Id);
cmd.Parameters.AddWithValue("@userid", rating.Userid);
cmd.ExecuteNonQuery();
}
}
}
public List <EpisodeRating> GetEpisodeRatingsBySerieId(int serieid, int userid)
{
List <EpisodeRating> ratings = new List <EpisodeRating>();
string query = "SELECT * FROM Serie AS a INNER JOIN Episode AS b ON a.id = b.serieid INNER JOIN Episoderating AS c ON b.id = c.episodeid WHERE serieid = @serieid AND userid = @userid";
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (var cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@serieid", serieid));
cmd.Parameters.Add(new SqlParameter("@userid", userid));
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
EpisodeRating rating = new EpisodeRating
{
Id = (int)reader["id"],
Rating = (int)reader["rating"],
Userid = (int)reader["userid"],
Episode = new Episode
{
Id = (int)reader["episodeid"]
}
};
ratings.Add(rating);
}
}
}
}
return(ratings);
}
}
public void CreateRating(EpisodeRating rating)
{
episodeRepository.CreateRating(rating);
}
public void CreateRating(EpisodeRating rating)
{
episodeContext.CreateRating(rating);
}
