public static IList <Album> GetAlbumsByGenre(int genreId)
        {
            IList <Album> albumList       = new List <Album>();
            SqlCommand    command         = null;
            string        selectStatement = "SELECT AlbumId, GenreId, ArtistId, Title, Price, AlbumArtUrl " +
                                            "FROM Album " +
                                            "WHERE GenreId = @GenreId";

            try
            {
                command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
                command.Parameters.Add(new SqlParameter("@GenreId", genreId));

                command.Connection.Open();
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

                int albumIdOrdinal     = reader.GetOrdinal("AlbumId");
                int genreIdOrdinal     = reader.GetOrdinal("GenreId");
                int artistOrdinal      = reader.GetOrdinal("ArtistId");
                int titleOrdinal       = reader.GetOrdinal("Title");
                int priceOrdinal       = reader.GetOrdinal("Price");
                int albumArtUrlOrdinal = reader.GetOrdinal("AlbumArtUrl");

                while (reader.Read())
                {
                    Album album = new Album();

                    album.AlbumId     = reader.GetInt32(albumIdOrdinal);
                    album.GenreId     = reader.GetInt32(genreIdOrdinal);
                    album.ArtistId    = reader.GetInt32(artistOrdinal);
                    album.Title       = reader.GetString(titleOrdinal);
                    album.Price       = reader.GetSqlDecimal(priceOrdinal);
                    album.AlbumArtUrl = reader.IsDBNull(albumArtUrlOrdinal)
                        ? null
                        : reader.GetString(albumArtUrlOrdinal);

                    albumList.Add(album);
                }
                reader.Close();
            }
            finally
            {
                command.Connection?.Close();
            }

            return(albumList);
        }
        public static IList <Genre> GetGenres()
        {
            SqlCommand    command         = null;
            IList <Genre> genreList       = new List <Genre>();
            string        selectStatement = "SELECT GenreId, name, Description " +
                                            "FROM GENRE";

            try
            {
                command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
                SqlDataReader reader = null;

                command.Connection.Open();
                reader = command.ExecuteReader();

                int genreIdOrdinal          = reader.GetOrdinal("GenreId");
                int genreNameOrdinal        = reader.GetOrdinal("Name");
                int genreDescriptionOrdinal = reader.GetOrdinal("Description");

                while (reader.Read())
                {
                    Genre genre = new Genre();

                    genre.ID          = reader.GetInt32(genreIdOrdinal);
                    genre.Name        = reader.IsDBNull(genreNameOrdinal) ? null : reader.GetString(genreNameOrdinal);
                    genre.Description = reader.IsDBNull(genreDescriptionOrdinal)
                        ? null
                        : reader.GetString(genreDescriptionOrdinal);

                    genreList.Add(genre);
                }

                reader.Close();
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                command.Connection?.Close();
            }

            return(genreList);
        }
Example #3
0
        public static string GetArtistNameById(int artistId)
        {
            SqlCommand command = null;
            string     artistName;
            string     selectStatement = "SELECT Name " +
                                         "FROM Artist " +
                                         "WHERE ArtistId = @ArtistId";

            try
            {
                command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
                command.Parameters.Add(new SqlParameter("@ArtistId", artistId));

                command.Connection.Open();
                artistName = command.ExecuteScalar().ToString();
            }
            finally
            {
                command.Connection?.Close();
            }

            return(artistName);
        }