public static IList <Album> GetAlbumsByGenre(int genreId)
{
var albums = new List <Album>();
string selectAlbums = "SELECT 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
{
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);
}
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);
}
public static Artist GetArtistNameById(int artistId)
{
Artist artist = null;
string selectAlbums = "SELECT 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
{
Name = reader["Name"].ToString(),
};
}
}
finally
{
connection?.Close();
reader?.Close();
}
return(artist);
}
