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