public static void UpdateAlbum(Album album)
{
SqlConnection connection = MusicStoreDB.GetConnection();
connection.Open();
string query =
@"UPDATE Album
SET GenreId = @genreId,
ArtistId = @artistId,
Title = @title,
Price = @price,
AlbumArtUrl = @albumArtUrl
WHERE AlbumId = @albumId;";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@albumId", album.AlbumId);
cmd.Parameters.AddWithValue("@genreId", album.GenreId);
cmd.Parameters.AddWithValue("@artistId", album.ArtistId);
cmd.Parameters.AddWithValue("@title", album.Title);
cmd.Parameters.AddWithValue("@price", album.Price);
cmd.Parameters.AddWithValue("@albumArtUrl", album.AlbumArtUrl);
int rowsUpdated = cmd.ExecuteNonQuery();
connection.Close();
}
public static void CreateAlbum(Album album)
{
SqlConnection connection = MusicStoreDB.GetConnection();
connection.Open();
string query =
@"INSERT INTO Album (GenreId, ArtistId, Title, Price, AlbumArtUrl)
VALUES (@genreId, @artistId, @title, @price, @albumArtUrl);";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@genreId", album.GenreId);
cmd.Parameters.AddWithValue("@artistId", album.ArtistId);
cmd.Parameters.AddWithValue("@title", album.Title);
cmd.Parameters.AddWithValue("@price", album.Price);
cmd.Parameters.AddWithValue("@albumArtUrl", album.AlbumArtUrl);
int rowsUpdated = cmd.ExecuteNonQuery();
connection.Close();
}
public static string GetArtistNameById(int artistId)
{
SqlConnection connection = MusicStoreDB.GetConnection();
connection.Open();
string query =
@"SELECT Name
FROM Artist
WHERE ArtistId = @artistId";
string nameOfArtist = null;
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@artistId", artistId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
nameOfArtist = (string)reader["Name"];
}
reader.Close();
connection.Close();
return(nameOfArtist);
}
public static Genre GetGenreById(int GenreId)
{
Genre genre = new Genre();
SqlConnection connection = MusicStoreDB.GetConnection();
connection.Open();
string query = @"SELECT Name, Description
FROM Genre
WHERE GenreId = @GenreId";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@GenreId", GenreId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string Name = reader["Name"].ToString();
string Description = reader["Description"].ToString();
genre.Name = Name;
genre.Description = Description;
}
return(genre);
}
public static IList <Album> GetAlbumsByGenres(int GenreId)
{
List <Album> Albums = new List <Album>();
SqlConnection connection = MusicStoreDB.GetConnection();
connection.Open();
string query =
@"SELECT AlbumId, AlbumArtUrl, ArtistId, Price, Title
FROM Album
WHERE GenreId = @GenreId";
SqlCommand cmd = new SqlCommand(query, connection);
cmd.Parameters.AddWithValue("@GenreId", GenreId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
int AlbumId = (int)reader["AlbumId"];
string AlbumArtUrl = (string)reader["AlbumArtUrl"];
int ArtistId = (int)reader["ArtistId"];
double Price = Convert.ToDouble(reader["Price"]);
string Title = (string)reader["Title"];
Albums.Add(new Album()
{
AlbumId = AlbumId,
AlbumArtUrl = AlbumArtUrl,
ArtistId = ArtistId,
Price = Price,
Title = Title
});
}
reader.Close();
connection.Close();
return(Albums);
}
