예제 #1
0
        public static IList <Album> GetAlbumsByGenre(int genreId)
        {
            var albums = new List <Album>();



            string selectAlbums = "SELECT GenreId, AlbumId, ArtistId, Title, Price, AlbumArtUrl FROM Album WHERE GenreId = @GenreId ORDER BY Title";

            SqlConnection connection = MusicStoreDB.GetSqlConnection();

            SqlCommand selectCommand = new SqlCommand
            {
                CommandText = selectAlbums,
                Connection  = connection
            };

            selectCommand.Parameters.AddWithValue("@GenreId", genreId);

            SqlDataReader reader = null;

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

                while (reader.Read())
                {
                    Album album = new Album
                    {
                        AlbumId     = (int)reader["AlbumId"],
                        GenreId     = (int)reader["Genre"],
                        Title       = reader["Title"].ToString(),
                        ArtistId    = (int)reader["ArtistId"],
                        Price       = (decimal)reader["Price"],
                        AlbumArtUrl = reader["AlbumArtUrl"].ToString()
                    };
                    albums.Add(album);
                }
            }
            finally
            {
                connection?.Close();
                reader?.Close();
            }

            return(albums);
        }
예제 #2
0
        public static bool UpdateAlbum(Album album)
        {
            SqlConnection connection    = MusicStoreDB.GetSqlConnection();
            var           updateCommand = CreateUpdateCommand(album, connection);

            try
            {
                connection.Open();

                int numberOfRowsAffected = updateCommand.ExecuteNonQuery();
                return(numberOfRowsAffected > 0);
            }
            finally
            {
                connection?.Close();
            }
        }
예제 #3
0
        public static Album GetAllAbumById(int albumId)
        {
            Album album = null;

            string selectAlbum = "SELECT GenreId, AlbumId, ArtistId, Title, Price, AlbumArtUrl FROM Album WHERE AlbumId = @AlbumId";

            SqlConnection connection = MusicStoreDB.GetSqlConnection();

            SqlCommand selectCommand = new SqlCommand
            {
                CommandText = selectAlbum,
                Connection  = connection
            };

            selectCommand.Parameters.AddWithValue("@AlbumId", albumId);
            SqlDataReader reader = null;

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

                while (reader.Read())
                {
                    album = new Album
                    {
                        AlbumId     = (int)reader["AlbumId"],
                        GenreId     = (int)reader["GenreId"],
                        Title       = reader["Title"].ToString(),
                        ArtistId    = (int)reader["ArtistId"],
                        Price       = (decimal)reader["Price"],
                        AlbumArtUrl = reader["AlbumArtUrl"].ToString()
                    };
                }
            }
            finally
            {
                connection?.Close();
                reader?.Close();
            }

            return(album);
        }
예제 #4
0
        public static IList <Genre> GetGenres()
        {
            var genres = new List <Genre>();



            string selectGenres = "SELECT GenreId, Name, Description FROM Genre ORDER BY Name";

            SqlConnection connection = MusicStoreDB.GetSqlConnection();

            SqlCommand selectCommand = new SqlCommand
            {
                CommandText = selectGenres,
                Connection  = connection
            };

            SqlDataReader reader = null;

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

                while (reader.Read())
                {
                    Genre genre = new Genre
                    {
                        GenreId     = (int)reader["GenreId"],
                        Name        = reader["Name"].ToString(),
                        Description = reader["Description"].ToString()
                    };
                    genres.Add(genre);
                }
            }
            finally
            {
                connection?.Close();
                reader?.Close();
            }

            return(genres);
        }
예제 #5
0
        public static Genre GetGenreById(int genreId)
        {
            Genre genre = null;

            string selectGenres = "SELECT GenreId, Name, Description FROM Genre WHERE GenreId = @GenreId ORDER BY Name";

            SqlConnection connection = MusicStoreDB.GetSqlConnection();

            SqlCommand selectCommand = new SqlCommand
            {
                CommandText = selectGenres,
                Connection  = connection
            };

            selectCommand.Parameters.AddWithValue("@GenreId", genreId);
            SqlDataReader reader = null;

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

                while (reader.Read())
                {
                    genre = new Genre
                    {
                        GenreId     = (int)reader["GenreId"],
                        Name        = reader["Name"].ToString(),
                        Description = reader["Description"].ToString()
                    };
                }
            }
            finally
            {
                connection?.Close();
                reader?.Close();
            }

            return(genre);
        }
예제 #6
0
        public static Artist GetArtistNameById(int artistId)
        {
            Artist artist = null;

            string selectAlbums = "SELECT ArtistId, Name FROM Artist WHERE ArtistId = @ArtistId";

            SqlConnection connection = MusicStoreDB.GetSqlConnection();

            SqlCommand selectCommand = new SqlCommand
            {
                CommandText = selectAlbums,
                Connection  = connection
            };

            selectCommand.Parameters.AddWithValue("@ArtistId", artistId);

            SqlDataReader reader = null;

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

                while (reader.Read())
                {
                    artist = new Artist
                    {
                        ArtistId = (int)reader["ArtistId"],
                        Name     = reader["Name"].ToString(),
                    };
                }
            }
            finally
            {
                connection?.Close();
                reader?.Close();
            }

            return(artist);
        }
예제 #7
0
        public static IList <Artist> GetArtists()
        {
            var artists = new List <Artist>();

            string selectGenres = "SELECT ArtistId, Name FROM Artist ORDER BY Name";

            SqlConnection connection = MusicStoreDB.GetSqlConnection();

            SqlCommand selectCommand = new SqlCommand
            {
                CommandText = selectGenres,
                Connection  = connection
            };

            SqlDataReader reader = null;

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

                while (reader.Read())
                {
                    Artist artist = new Artist
                    {
                        ArtistId = (int)reader["ArtistId"],
                        Name     = reader["Name"].ToString()
                    };
                    artists.Add(artist);
                }
            }
            finally
            {
                connection?.Close();
                reader?.Close();
            }

            return(artists);
        }