public IEnumerable <AlbumCoverData> GetAlbumCoverData()
{
_log.Debug("Retrieving album cover data...");
SqlCommand command = _dbObj.GetNewCommand("Library.GetAlbumCoverData", CommandType.StoredProcedure, false);
_dbObj.AddErrorHandlingParamsToSqlCommand(command);
SqlDataReader reader = command.ExecuteReader();
try
{
int ordId = reader.GetOrdinal("Id");
int ordDirectory = reader.GetOrdinal("Directory");
int ordWmCollectionId = reader.GetOrdinal("WmCollectionId");
int ordTitle = reader.GetOrdinal("Title");
while (reader.Read())
{
AlbumCoverData albumCoverRecord = new AlbumCoverData()
{
Id = reader[ordId].ToString(),
Directory = reader.GetString(ordDirectory),
WmCollectionId = reader[ordWmCollectionId].ToString(),
Title = reader[ordTitle].ToString()
};
yield return(albumCoverRecord);
}
}
finally
{
reader.Close();
//Since it's only a select, we don't care about committing the transaction even if we
_dbObj.FreeCommand(command);
}
}
private void LoadAlbumUrls(Album album)
{
MsSql db = new MsSql(_connectionString);
SqlCommand command = db.GetNewCommand("GetAlbumUrls", CommandType.StoredProcedure);
try
{
command.Parameters.Add("@AlbumId", SqlDbType.BigInt).Value = album.Id;
db.AddErrorHandlingParamsToSqlCommand(command);
SqlDataReader reader = command.ExecuteReader();
try
{
int ordId = reader.GetOrdinal("Id");
int ordDisplayText = reader.GetOrdinal("DisplayText");
int ordUrl = reader.GetOrdinal("Url");
while (reader.Read())
{
album.ListenUrls.Add(new DynamicUrl()
{
DisplayText = reader.GetString(ordDisplayText),
Url = reader.GetString(ordUrl)
});
}
}
finally
{
reader.Close();
}
db.FreeCommand(command);
}
catch (Exception ex)
{
db.FreeCommand(command, false);
throw ex;
}
}
private List <NewsArticle> SelectArticles(bool podcastsOnly)
{
List <NewsArticle> result = new List <NewsArticle>();
MsSql db = new MsSql(_connectionString);
//Call the appropriate stored procedure, based on whether or not we want news and podcasts,
//or just podcasts only
string storedProcedureName;
if (podcastsOnly)
{
storedProcedureName = "GetPodcasts";
}
else
{
storedProcedureName = "GetArticles";
}
SqlCommand command = db.GetNewCommand(storedProcedureName, CommandType.StoredProcedure);
try
{
db.AddErrorHandlingParamsToSqlCommand(command);
SqlDataReader reader = command.ExecuteReader();
try
{
int ordArticleId = reader.GetOrdinal("Id");
int ordPostDateTime = reader.GetOrdinal("PostDateTime");
int ordBodyText = reader.GetOrdinal("BodyText");
int ordDownloadDisplayText = reader.GetOrdinal("DownloadDisplayText");
int ordDownloadUrl = reader.GetOrdinal("DownloadUrl");
while (reader.Read())
{
//Check to see if we have podcast information for the current record
NewsArticle newArticle;
if ((reader[ordDownloadDisplayText] == DBNull.Value) || (reader[ordDownloadUrl] == DBNull.Value))
{
//We are missing podcast download info, so we have a news article
newArticle = new NewsArticle();
}
else
{
//We have download info, so we have a podcast article
newArticle = new PodcastArticle()
{
//Grab the URL download info while we're here
PodcastUrl = new DynamicUrl()
{
Url = reader.GetString(ordDownloadUrl),
DisplayText = reader.GetString(ordDownloadDisplayText)
}
};
}
newArticle.Id = reader.GetInt64(ordArticleId);
newArticle.PostDateTime = reader.GetDateTime(ordPostDateTime);
newArticle.BodyText = reader.GetString(ordBodyText);
if (newArticle is PodcastArticle)
{
//We need to retrieve album information for the podcast
LoadPodcastAlbums((PodcastArticle)newArticle);
}
//Add the new article to the result
result.Add(newArticle);
}
}
finally
{
reader.Close();
}
db.FreeCommand(command);
}
catch (Exception ex)
{
db.FreeCommand(command, false);
throw ex;
}
return(result);
}
private void LoadPodcastAlbums(PodcastArticle article)
{
MsSql db = new MsSql(_connectionString);
SqlCommand command = db.GetNewCommand("GetPodcastAlbums", CommandType.StoredProcedure);
try
{
command.Parameters.Add("@ArticleId", SqlDbType.BigInt).Value = article.Id;
db.AddErrorHandlingParamsToSqlCommand(command);
SqlDataReader reader = command.ExecuteReader();
try
{
int ordId = reader.GetOrdinal("Id");
int ordArtist = reader.GetOrdinal("Artist");
int ordCaption = reader.GetOrdinal("Caption");
int ordCoverImageFileName = reader.GetOrdinal("CoverImageFileName");
int ordLabel = reader.GetOrdinal("Label");
int ordTitle = reader.GetOrdinal("Title");
int ordYear = reader.GetOrdinal("Year");
const int EXPECTED_ALBUM_COUNT = 2;
int albumCount = 0;
while (reader.Read())
{
//Increment our album counter
albumCount++;
//Make sure that we haven't surpassed the expected album count
if (albumCount > EXPECTED_ALBUM_COUNT)
{
throw new PodcastAlbumCountException(albumCount, EXPECTED_ALBUM_COUNT);
}
Album newAlbum = new Album()
{
Artist = reader.GetString(ordArtist),
Caption = reader.GetString(ordCaption),
CoverImageFileName = reader.GetString(ordCoverImageFileName),
Id = reader.GetInt64(ordId),
Label = reader.GetString(ordLabel),
Title = reader.GetString(ordTitle),
Year = reader.GetInt32(ordYear)
};
//Get listen URLs for the album
LoadAlbumUrls(newAlbum);
//Assign the album to the provided article
if (albumCount == 1)
{
//Podcast album #1
article.Album1 = newAlbum;
}
else
{
//Podcast album #2
article.Album2 = newAlbum;
}
}
if (albumCount != EXPECTED_ALBUM_COUNT)
{
throw new PodcastAlbumCountException(albumCount, EXPECTED_ALBUM_COUNT);
}
}
finally
{
reader.Close();
}
db.FreeCommand(command);
}
catch (Exception ex)
{
db.FreeCommand(command, false);
throw ex;
}
}