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