private void UpdateShows(string sId)
{
var seriesId = sId;
var query = "SELECT * FROM series WHERE series_id=" + seriesId;
var dt = _db.GetDataTable(query);
var drow = dt.Rows[0];
if (!Directory.Exists("temp"))
{
Directory.CreateDirectory("temp");
}
if (!Directory.Exists("res"))
{
Directory.CreateDirectory("res");
}
var client = new WebClient();
client.DownloadFile("http://thetvdb.com/api/" + Constants.ApiKey + "/series/" + seriesId + "/all/" + drow["language"].ToString() + ".zip",
@"temp/tmp.zip");
using (var zip = ZipFile.Read("temp/tmp.zip"))
{
zip.ExtractAll("temp/");
}
var doc = XDocument.Load("temp/en.xml");
// var names = doc.Descendants("Series");
var names = from ele in doc.Descendants("Episode")
select new
{
episodeName = (string)ele.Element("EpisodeName") ?? string.Empty,
episode = (int)ele.Element("EpisodeNumber"),
season = (int)ele.Element("SeasonNumber"),
firstAired = (string)ele.Element("FirstAired") ?? string.Empty,
imdbId = (string)ele.Element("IMDB_ID") ?? string.Empty,
overview = (string)ele.Element("Overview") ?? string.Empty,
rating = getRating(ele),
episodeId = (int)ele.Element("id")
};
foreach (var n in names)
{
try
{
if (_db.EpisodeExists(seriesId, n.episodeId.ToString()))
{
_db.UpdateEpisode(seriesId, n.episodeId.ToString(), n.episodeName, n.overview, n.firstAired, n.rating.ToString());
}
else
{
var edb = new EpisodeDatabaseEntry(n.episodeName, n.episode, n.season, n.rating, n.firstAired, n.imdbId, n.overview, false, n.episodeId);
_db.InsertEpisode("episodes_" + seriesId, edb);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
_db.UpdateDate(seriesId, DateTime.Now.ToString());
Directory.Delete(@"temp", true);
}
public int InsertEpisode(string tableName, EpisodeDatabaseEntry episode)
{
var cnn = new SQLiteConnection(_dbConnection);
cnn.Open();
var mycommand = new SQLiteCommand(cnn)
{
CommandText =
"INSERT INTO " + tableName +
" (episode_name, episode, season, first_aired, imdb_id, overview, rating, episode_id, watched) VALUES(@episodeName, @episode, @season, @firstAired, @imdbId, @overview, @rating, @episode_id, @watched)"
};
mycommand.Parameters.Add(new SQLiteParameter("@episodeName", episode.EpisodeName));
mycommand.Parameters.Add(new SQLiteParameter("@episode", episode.Episode));
mycommand.Parameters.Add(new SQLiteParameter("@firstAired", episode.FirstAired));
mycommand.Parameters.Add(new SQLiteParameter("@season", episode.Season));
mycommand.Parameters.Add(new SQLiteParameter("@imdbId", episode.ImdbId));
mycommand.Parameters.Add(new SQLiteParameter("@overview", episode.Overview));
mycommand.Parameters.Add(new SQLiteParameter("@rating", episode.Rating));
mycommand.Parameters.Add(new SQLiteParameter("@watched", episode.Watched));
mycommand.Parameters.Add(new SQLiteParameter("@episode_id", episode.EpisodeId));
var rowsUpdated = mycommand.ExecuteNonQuery();
cnn.Close();
return rowsUpdated;
}