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