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);
}
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);
}
