public static List <Artist> GetAllArtists() { SqlConnection connection = MusicStoreDB.GetConnection(); connection.Open(); string query = @"SELECT Name, ArtistId FROM Artist"; SqlCommand cmd = new SqlCommand(query, connection); SqlDataReader reader = cmd.ExecuteReader(); List <Artist> artistList = new List <Artist>(); while (reader.Read()) { string artistName = (string)reader["Name"]; int artistId = Convert.ToInt32(reader["ArtistId"]); artistList.Add(new Artist() { ArtistId = artistId, Name = artistName }); } reader.Close(); connection.Close(); return(artistList); }
//vraag Sam nog eens wat een Ilist precies is public static IList <Genre> GetGenres() { List <Genre> genres = new List <Genre>(); SqlConnection connection = MusicStoreDB.GetConnection(); connection.Open(); string query = @"SELECT GenreId, Name, Description FROM Genre"; SqlCommand cmd = new SqlCommand(query, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int GenreId = Convert.ToInt32(reader["GenreId"]); string Name = reader["Name"].ToString(); string Description = reader["Description"].ToString(); genres.Add(new Genre() { GenreId = GenreId, Name = Name, Description = Description }); } return(genres); }
public static Album GetAlbumById(int albumId) { Album AlbumById = new Album(); SqlConnection connection = MusicStoreDB.GetConnection(); connection.Open(); string query = @"SELECT GenreId, ArtistId, Title, Price, AlbumArtUrl FROM Album WHERE AlbumId =@albumId"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.AddWithValue("@albumId", albumId); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int GenreId = (int)reader["GenreId"]; string AlbumArtUrl = (string)reader["AlbumArtUrl"]; int ArtistId = (int)reader["ArtistId"]; double Price = Convert.ToDouble(reader["Price"]); string Title = (string)reader["Title"]; AlbumById.GenreId = GenreId; AlbumById.AlbumArtUrl = AlbumArtUrl; AlbumById.ArtistId = ArtistId; AlbumById.Price = Price; AlbumById.Title = Title; } reader.Close(); connection.Close(); return(AlbumById); }
public static IList <Album> GetAllAlbums() { List <Album> Albums = new List <Album>(); SqlConnection connection = MusicStoreDB.GetConnection(); connection.Open(); string query = @"SELECT AlbumId, GenreId, ArtistId, Title, Price, AlbumArtUrl FROM Album"; SqlCommand cmd = new SqlCommand(query, connection); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int AlbumId = (int)reader["AlbumId"]; int GenreId = (int)reader["GenreId"]; 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, GenreId = GenreId, AlbumArtUrl = AlbumArtUrl, ArtistId = ArtistId, Price = Price, Title = Title }); } reader.Close(); connection.Close(); return(Albums); }
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 IList <Album> GetAlbumsByGenre(int genreId) { var albums = new List <Album>(); string selectAlbums = "SELECT 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 { 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 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 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 Artist GetArtistNameById(int artistId) { Artist artist = null; string selectAlbums = "SELECT 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 { Name = reader["Name"].ToString(), }; } } finally { connection?.Close(); reader?.Close(); } return(artist); }
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); }