예제 #1
0
        public static List <Album> GetAlbumByGenre(int genreId)
        {
            String selectStatement = "SELECT * " +
                                     "FROM dbo.Album " +
                                     "WHERE GenreId = " + genreId + ";";

            SqlConnection connection = MusicStoreDb.GetConnection();

            SqlCommand    command = new SqlCommand(selectStatement, connection);
            SqlDataReader reader  = null;
            var           albums  = new List <Album>();

            try
            {
                connection.Open();
                reader = command.ExecuteReader();

                int albumIdOrdinal     = reader.GetOrdinal("AlbumId");
                int genreIdOrdinal     = reader.GetOrdinal("GenreId");
                int artistIdOrdinal    = 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()
                    {
                        AlbumId     = reader.GetInt32(albumIdOrdinal),
                        GenreId     = reader.GetInt32(genreIdOrdinal),
                        ArtistId    = reader.GetInt32(artistIdOrdinal),
                        Title       = reader.GetString(titleOrdinal),
                        Price       = reader.GetDecimal(priceOrdinal),
                        AlbumArtUrl = reader.GetString(albumArtUrlOrdinal)
                    };
                    albums.Add(album);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                connection.Close();
            }
            return(albums);
        }
예제 #2
0
        public static List <Genre> GetGenres()
        {
            String selectStatement = "SELECT genreId, name, description " +
                                     "FROM dbo.Genre";

            SqlConnection connection = MusicStoreDb.GetConnection();

            SqlCommand command = new SqlCommand(selectStatement, connection);

            SqlDataReader reader = null;

            var genres = new List <Genre>();

            try
            {
                connection.Open();
                reader = command.ExecuteReader();

                int genreIdOrdinal = reader.GetOrdinal("GenreId");
                int genreName      = reader.GetOrdinal("Name");
                int description    = reader.GetOrdinal("Description");

                while (reader.Read())
                {
                    Genre genre = new Genre()
                    {
                        GenreId     = reader.GetInt32(genreIdOrdinal),
                        Name        = reader.GetString(genreName),
                        Description = reader.GetString(description)
                    };

                    genres.Add(genre);
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader = null;
                }
                connection.Close();
            }
            return(genres);
        }
예제 #3
0
        public static String GetArtistNameById(int artistId)
        {
            String selectStatement = "SELECT * " +
                                     "FROM dbo.Artist " +
                                     "WHERE ArtistId = " + artistId + ";";
            SqlConnection connection = MusicStoreDb.GetConnection();

            SqlCommand    command = new SqlCommand(selectStatement, connection);
            SqlDataReader reader  = null;
            Artist        artist  = new Artist();

            try
            {
                connection.Open();
                reader = command.ExecuteReader();

                int artistIdOrdinal = reader.GetOrdinal("ArtistId");
                int nameOrdinal     = reader.GetOrdinal("Name");

                while (reader.Read())
                {
                    Artist temporaryArtist = new Artist()
                    {
                        ArtistId = reader.GetInt32(artistIdOrdinal),
                        Name     = reader.GetString(nameOrdinal)
                    };

                    artist = temporaryArtist;
                }
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
                connection.Close();
            }
            return(artist.Name);
        }