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);
}
}
}