public TVSeriesExtended GetExtendedTVSeriesItem(TVSeries item) { TVSeriesExtended res = null; DbCommand command = _factory.CreateCommand(); command.Connection = _connection; command.CommandText = $"SELECT TVSeriesTable.Id, TVSeriesTable.Image, TVSeriesTable.Name, TVSeriesTable.Seasons, TVSeriesTable.YearOfIssue, TVSeriesTable.Desription, Channels.Name as 'Channel' FROM TVSeriesTable JOIN Channels ON TVSeriesTable.Channel_Id = Channels.Id WHERE TVSeriesTable.Id = {item.Id}"; DbDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { res = new TVSeriesExtended((String)reader["Desription"], (String)reader["Channel"], new List <string>(), (int?)reader["Seasons"], (int)reader["Id"], (String)reader["Image"], (String)reader["Name"], (int?)reader["YearOfIssue"]); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Ooops", MessageBoxButton.OK); } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } } command.CommandText = $"SELECT Genres.Name FROM Genres JOIN TVSeriesGenres ON Genres.Id = TVSeriesGenres.Genre_Id WHERE TVSeriesGenres.TVSeries_Id = {item.Id}"; try { reader = command.ExecuteReader(); while (reader.Read()) { res.GenreList.Add((String)reader["Name"]); } } catch (Exception ex) { MessageBox.Show(ex.Message, "Ooops", MessageBoxButton.OK); } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } } return(res); }
public void AddTVSeriesItem(TVSeriesExtended item) { DbCommand command = _factory.CreateCommand(); command.Connection = _connection; command.CommandText = $"INSERT INTO TVSeriesTable VALUES('{item.Name}', '{item.Image}', '{item.Year}', '{item.NumberOfSeasons}', '{item.Description}', (SELECT Id FROM Channels WHERE Name = '{item.Channel}'))"; try { command.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Ooops", MessageBoxButton.OK); } command.CommandText = $"(SELECT Id FROM TVSeriesTable WHERE Name = '{item.Name}' AND Image = '{item.Image}' AND YearOfIssue = '{item.Year}' AND Seasons = '{item.NumberOfSeasons}')"; DbDataReader reader = null; try { reader = command.ExecuteReader(); while (reader.Read()) { item.Id = (Int32)reader["Id"]; } } catch (Exception ex) { MessageBox.Show(ex.Message, "Ooops", MessageBoxButton.OK); } finally { if (reader != null && !reader.IsClosed) { reader.Close(); } } if (item.GenreList.Count > 0) { StringBuilder stringBuilder = new StringBuilder("INSERT INTO TVSeriesGenres VALUES"); int size = stringBuilder.Length; foreach (var genre in item.GenreList) { if (stringBuilder.Length <= size) { stringBuilder.Insert(stringBuilder.Length, $"({item.Id}, (SELECT Id FROM Genres WHERE Name = '{genre}'))"); } else { stringBuilder.Insert(stringBuilder.Length, $", ({item.Id}, (SELECT Id FROM Genres WHERE Name = '{genre}'))"); } } command.CommandText = stringBuilder.ToString(); try { command.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message, "Ooops", MessageBoxButton.OK); } } }