/// <summary>
/// Returns only the specified podcast
/// </summary>
/// <param name="podcastID"></param>
/// <returns></returns>
private IEnumerable <Podcast> GetLastPodcastsFromDatabase(int podcastID)
{
int maxRows = Configuration.MaximumOfPodcastsOnHomePage;
using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
{
string onlyOnePodcast = podcastID > 0 ? $" AND PodcastID = {podcastID}" : String.Empty;
// Search all authors for last podcasts
cmd.Clear();
cmd.CommandText.AppendLine($" SELECT PodcastID, Name, Url ");
cmd.CommandText.AppendLine($" FROM PodcastAuthor ");
cmd.CommandText.AppendLine($" INNER JOIN PodcastAuthorLink ON PodcastAuthorLink.PodcastAuthorID = PodcastAuthor.PodcastAuthorID ");
cmd.CommandText.AppendLine($" WHERE PodcastAuthorLink.PodcastID IN (SELECT TOP {maxRows} PodcastID FROM Podcast ORDER BY PublicationDate DESC) ");
cmd.CommandText.AppendLine(onlyOnePodcast);
var authors = cmd.ExecuteTable((row) =>
{
return(new
{
PodcastID = row.Field <int>("PodcastID"),
Name = row.Field <string>("Name"),
Url = row.Field <string>("Url"),
});
});
// Select last podcasts
cmd.Clear();
cmd.CommandText.AppendLine($" SELECT TOP {maxRows} * ");
cmd.CommandText.AppendLine($" FROM Podcast ");
cmd.CommandText.AppendLine($" WHERE 1=1 " + onlyOnePodcast);
cmd.CommandText.AppendLine($" ORDER BY PublicationDate DESC ");
return(cmd.ExecuteTable((row) =>
{
int id = row.Field <int>("PodcastID");
Podcast podcast = new Podcast(this.Configuration);
podcast.PodcastID = id;
podcast.PodcastKey = row.Field <string>("PodcastKey");
podcast.PublicationDate = row.Field <DateTime>("PublicationDate");
podcast.Summary = new PodcastSummary(podcast)
{
Title = row.Field <string>("SummaryTitle"),
Description = row.Field <string>("SummaryDescription"),
Authors = authors.Where(i => i.PodcastID == id)
.OrderBy(i => i.Name)
.Select(i => new PodcastAuthor(podcast)
{
Name = i.Name,
Email = String.Empty,
Url = i.Url
})
.ToArray()
};
podcast.Notes = row.Field <string>("Notes");
podcast.Audio = new PodcastAudio(podcast)
{
RemoteUrl = row.Field <string>("AudioUrl"),
Size = (uint)row.Field <int>("AudioSize"),
Duration = row.Field <TimeSpan>("AudioDuration"),
};
podcast.Video = new PodcastVideo(podcast)
{
RemoteUrl = row.Field <string>("VideoUrl"),
Size = (uint)row.Field <int>("VideoSize"),
Duration = row.Field <TimeSpan>("VideoDuration"),
YoutubeKey = row.Field <string>("VideoYoutubeKey"),
};
return podcast;
}));
}
}