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