public void SendWatchListSerie(WatchListSerie watchListSerie)
{
//string query = "INSERT INTO WatchListSeries([serieid], [name], [status], [episodesseen], [rating], [userid])" +
// "VALUES(@serieid, @name, @status, @episodesseen, @rating, @userid)";
string query = "IF EXISTS(SELECT * FROM WatchListSeries WHERE name = @name AND userid=@userid) UPDATE WatchListSeries SET rating=@rating, status=@status, episodesseen=@episodesseen WHERE name=@name AND userid=@userid ELSE INSERT INTO WatchListSeries([serieid], [name], [status], [episodesseen], [rating], [userid]) VALUES(@serieid, @name, @status, @episodesseen, @rating, @userid)";
using (var conn = new SqlConnection(ConnectionString))
{
using (var cmd = new SqlCommand(query, conn))
{
conn.Open();
cmd.Parameters.AddWithValue("@serieid", watchListSerie.SerieId);
cmd.Parameters.AddWithValue("@name", watchListSerie.Name);
cmd.Parameters.AddWithValue("@status", watchListSerie.Status);
cmd.Parameters.AddWithValue("@episodesseen", watchListSerie.EpisodesSeen);
cmd.Parameters.AddWithValue("@rating", watchListSerie.Rating);
cmd.Parameters.AddWithValue("@userid", watchListSerie.User.Id);
cmd.ExecuteNonQuery();
}
}
}
public List <WatchListSerie> GetSeries(int userid)
{
List <WatchListSerie> series = new List <WatchListSerie>();
string query = "SELECT id, name, status, episodesseen, rating, userid, serieid FROM WatchListSeries WHERE userid = @userid";
using (var conn = new SqlConnection(ConnectionString))
{
conn.Open();
using (var cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add(new SqlParameter("@userid", userid));
using (var reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
WatchListSerie serie = new WatchListSerie()
{
Id = (int)reader["id"],
Name = (string)reader["name"],
Status = (string)reader["status"],
EpisodesSeen = (string)reader["episodesseen"],
Rating = (int)reader["rating"],
User = new User
{
Id = (int)reader["userid"]
},
SerieId = (int)reader["serieid"]
};
series.Add(serie);
}
}
}
}
return(series);
}
}
public void AddToWatchList(WatchListSerie watchListSerie)
{
watchListRepository.SendWatchListSerie(watchListSerie);
}
public void SendWatchListSerie(WatchListSerie watchListSerie)
{
watchListContext.SendWatchListSerie(watchListSerie);
}
