public static IList <Album> GetAlbumsByGenre(int genreId) { var albums = new List <Album>(); string selectAlbums = "SELECT GenreId, AlbumId, 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 { AlbumId = (int)reader["AlbumId"], GenreId = (int)reader["Genre"], 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 bool UpdateAlbum(Album album) { SqlConnection connection = MusicStoreDB.GetSqlConnection(); var updateCommand = CreateUpdateCommand(album, connection); try { connection.Open(); int numberOfRowsAffected = updateCommand.ExecuteNonQuery(); return(numberOfRowsAffected > 0); } finally { connection?.Close(); } }
public static Album GetAllAbumById(int albumId) { Album album = null; string selectAlbum = "SELECT GenreId, AlbumId, ArtistId, Title, Price, AlbumArtUrl FROM Album WHERE AlbumId = @AlbumId"; SqlConnection connection = MusicStoreDB.GetSqlConnection(); SqlCommand selectCommand = new SqlCommand { CommandText = selectAlbum, Connection = connection }; selectCommand.Parameters.AddWithValue("@AlbumId", albumId); SqlDataReader reader = null; try { connection.Open(); reader = selectCommand.ExecuteReader(); while (reader.Read()) { album = new Album { AlbumId = (int)reader["AlbumId"], GenreId = (int)reader["GenreId"], Title = reader["Title"].ToString(), ArtistId = (int)reader["ArtistId"], Price = (decimal)reader["Price"], AlbumArtUrl = reader["AlbumArtUrl"].ToString() }; } } finally { connection?.Close(); reader?.Close(); } return(album); }
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 Genre GetGenreById(int genreId) { Genre genre = null; string selectGenres = "SELECT GenreId, Name, Description FROM Genre WHERE GenreId = @GenreId ORDER BY Name"; SqlConnection connection = MusicStoreDB.GetSqlConnection(); SqlCommand selectCommand = new SqlCommand { CommandText = selectGenres, Connection = connection }; selectCommand.Parameters.AddWithValue("@GenreId", genreId); SqlDataReader reader = null; try { connection.Open(); reader = selectCommand.ExecuteReader(); while (reader.Read()) { genre = new Genre { GenreId = (int)reader["GenreId"], Name = reader["Name"].ToString(), Description = reader["Description"].ToString() }; } } finally { connection?.Close(); reader?.Close(); } return(genre); }
public static Artist GetArtistNameById(int artistId) { Artist artist = null; string selectAlbums = "SELECT ArtistId, 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 { ArtistId = (int)reader["ArtistId"], Name = reader["Name"].ToString(), }; } } finally { connection?.Close(); reader?.Close(); } return(artist); }
public static IList <Artist> GetArtists() { var artists = new List <Artist>(); string selectGenres = "SELECT ArtistId, Name FROM Artist 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()) { Artist artist = new Artist { ArtistId = (int)reader["ArtistId"], Name = reader["Name"].ToString() }; artists.Add(artist); } } finally { connection?.Close(); reader?.Close(); } return(artists); }