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