private Int64 GetMachineId(SqlCommand command) { _dbObj.ResetCommand(command); command.CommandText = "Library.GetWorkstationId"; command.CommandType = CommandType.StoredProcedure; //Add standard param(s) command.Parameters.Add("@WorkstationName", SqlDbType.VarChar).Value = Environment.MachineName; //Create the result parameter and add it to the command SqlParameter paramResultMachineId = new SqlParameter("@Result", SqlDbType.BigInt) { Direction = ParameterDirection.Output }; command.Parameters.Add(paramResultMachineId); //Add error handling params _dbObj.AddErrorHandlingParamsToSqlCommand(command); command.ExecuteNonQuery(); _dbObj.VerifyStoredProcedureResult(command); Int64 result = (Int64)paramResultMachineId.Value; return(result); }
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; } }