public static int AddSeries(string connectionString, string title, int debutYear, string filmType, string genre, string language)
{
switch (filmType)
{
case "0":
filmType = "Animation";
break;
case "1":
filmType = "Anime";
break;
case "2":
filmType = "Live Action";
break;
}
SeriesModel series = new SeriesModel
{
Title = title,
DebutYear = debutYear,
FilmType = filmType,
Genre = genre,
Language = language
};
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
string sql = @"CALL series.usp_Insert_Series(@Title, @DebutYear, @FilmType, @Genre, @Language)";
return(sqlDataAccess.SaveData <SeriesModel>(sql, series));
}
private static int InsertEpisodesToDatabase(string connectionString, string seriesLanguage, int seasonID, List <WikiEpisode> episodeList)
{
int episodesAdded = 0;
int episodeTitlesAdded = 0;
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
string insertEpisodeSQL = @"CALL Series.usp_Insert_Episode(@SeriesID, @SeasonNumber, @episodeNumberInSeries, @episodeNumberInSeason, @originalAirDate)";
for (int i = 0; i < episodeList.Count; i++)
{
WikiEpisode currentEpisode = episodeList[i];
episodesAdded += sqlDataAccess.SaveData <WikiEpisode>(insertEpisodeSQL, currentEpisode);
string retrieveEpisodeIDSQL = $"SELECT * FROM series.uf_retrieve_episodeid({ seasonID }, '{ episodeList[i].episodeNumberInSeries }')";
int episodeID = Convert.ToInt32(sqlDataAccess.RetrieveData(retrieveEpisodeIDSQL));
//Add English Episode
episodeTitlesAdded += InsertEpisodeTitle(connectionString, episodeID, "ENG", currentEpisode.title);
if (seriesLanguage.Equals("Japanese"))
{
episodeTitlesAdded += InsertEpisodeTitle(connectionString, episodeID, "RMJ", currentEpisode.titleRomaji);
episodeTitlesAdded += InsertEpisodeTitle(connectionString, episodeID, "JPN", currentEpisode.titleJapanese);
}
}
return(episodesAdded);
}
public static int DeleteSeries(string connectionString, int seriesID)
{
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
string sql = $"CALL series.usp_delete_series( { seriesID } )";
return(sqlDataAccess.ExecuteStoredProcedure(sql));
}
public static string GetSeriesLanguage(string connectionString, int seriesID)
{
string sql = $"SELECT * FROM series.uf_get_series_language( { seriesID } )";
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
return(sqlDataAccess.RetrieveData(sql));
}
// Series
public static List <SeriesModel> LoadSeries(string connectionString)
{
string sql = "SELECT * FROM series.view_ListSeries ORDER BY title;";
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
return(sqlDataAccess.LoadData <SeriesModel>(sql));
}
// Episodes
public static List <EpisodeModel> LoadEpisodes(string connectionString, int id)
{
string sqlAllEpisodes = $"SELECT * FROM series.uf_list_all_episodes( { id } )";
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
List <EpisodeModel> episodeList = TransformEpisodeModel(sqlDataAccess.LoadData <EpisodeSqlModel>(sqlAllEpisodes));
return(episodeList);
}
private static int GetSeasonID(string connectionString, bool oneSeason, int seriesID, int seasonNumber)
{
SqlDataAccess sqlDataAccess = new SqlDataAccess();
sqlDataAccess.GetConnectionString(connectionString);
string sql;
if (oneSeason)
{
sql = $"SELECT * FROM Series.uf_Get_SeasonID({ seriesID }, 1)";
}
else
{
sql = $"SELECT * FROM Series.uf_Get_SeasonID({ seriesID }, { seasonNumber })";
}
return(Convert.ToInt32(sqlDataAccess.RetrieveData(sql)));
}