public static string GetArtistById(int artistId)
{
SqlCommand command = null;
string selectStatement = "SELECT Name FROM Artist WHERE ArtistId=@artistId";
string artistName = "";
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Parameters.Add(new SqlParameter("@artistId", artistId));
command.Connection.Open();
artistName = (string)command.ExecuteScalar();
}
catch (SqlException ex)
{
//
}
catch (FormatException ex)
{
//
}
finally
{
command.Connection?.Close();
}
return(artistName);
}
public static string GetGenreById(int genreId)
{
SqlCommand command = null;
string selectStatement = "SELECT Name FROM Genre WHERE GenreId=@genreId";
string genre = "";
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Parameters.Add(new SqlParameter("@genreId", genreId));
command.Connection.Open();
genre = (string)command.ExecuteScalar();
}
catch (SqlException ex)
{
//
}
catch (FormatException ex)
{
//
}
finally
{
command.Connection?.Close();
}
return(genre);
}
public static IList <Album> GetAlbums()
{
IList <Album> albumList = new List <Album>();
SqlCommand command = null;
string selectStatement = "SELECT AlbumId, GenreId, ArtistId, Title, Price, AlbumArtUrl " +
"FROM Album";
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
int albumIdOrdinal = reader.GetOrdinal("AlbumId");
int genreIdOrdinal = reader.GetOrdinal("GenreId");
int artistIdOrdinal = reader.GetOrdinal("ArtistId");
int titleOrdinal = reader.GetOrdinal("Title");
int priceOrdinal = reader.GetOrdinal("Price");
int albumArtUrlOrdinal = reader.GetOrdinal("AlbumArturl");
while (reader.Read())
{
Album album = new Album();
album.AlbumId = reader.GetInt32(albumIdOrdinal);
album.GenreId = reader.GetInt32(genreIdOrdinal);
album.ArtistId = reader.GetInt32(artistIdOrdinal);
album.Title = reader.GetString(titleOrdinal);
album.Price = string.Format("€{0:#.00}", reader.GetSqlDecimal(priceOrdinal));
album.AlbumArtUrl = reader.IsDBNull(albumArtUrlOrdinal) ? null : reader.GetString(albumArtUrlOrdinal);
albumList.Add(album);
}
reader.Close();
}
catch (SqlException ex)
{
}
finally
{
command.Connection?.Close();
}
return(albumList);
}
public static Album GetAlbumById(int albumId)
{
SqlCommand command = null;
string selectStatement = "SELECT AlbumId, GenreId, ArtistId, Title, Price, AlbumArtUrl " +
"FROM Album WHERE AlbumId=@albumId";
Album album = new Album();
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Parameters.Add(new SqlParameter("@albumId", albumId));
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
int albumIdOrdinal = reader.GetOrdinal("AlbumId");
int genreIdOrdinal = reader.GetOrdinal("GenreId");
int artistIdOrdinal = reader.GetOrdinal("ArtistId");
int titleOrdinal = reader.GetOrdinal("Title");
int priceOrdinal = reader.GetOrdinal("Price");
int albumArtUrlOrdinal = reader.GetOrdinal("AlbumArturl");
while (reader.Read())
{
album.AlbumId = reader.GetInt32(albumIdOrdinal);
album.GenreId = reader.GetInt32(genreIdOrdinal);
album.ArtistId = reader.GetInt32(artistIdOrdinal);
album.Title = reader.GetString(titleOrdinal);
album.Price = string.Format("€{0:#.00}", reader.GetSqlDecimal(priceOrdinal));
album.AlbumArtUrl = reader.IsDBNull(albumArtUrlOrdinal) ? null : reader.GetString(albumArtUrlOrdinal);
}
reader.Close();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
command.Connection?.Close();
}
return(album);
}
public static IList <Genre> GetAllGenres()
{
IList <Genre> genreList = new List <Genre>();
SqlCommand command = null;
string selectStatement = "SELECT GenreId, Name, Description FROM Genre";
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
int genreIdOrdinal = reader.GetOrdinal("GenreId");
int nameOrdinal = reader.GetOrdinal("Name");
int descriptionOrdinal = reader.GetOrdinal("Description");
while (reader.Read())
{
Genre genre = new Genre();
genre.GenreId = reader.GetInt32(genreIdOrdinal);
genre.Name = reader.GetString(nameOrdinal);
genre.Description = reader.GetString(descriptionOrdinal);
genreList.Add(genre);
}
reader.Close();
}
catch (SqlException ex)
{
}
finally
{
command.Connection?.Close();
}
return(genreList);
}
public static void UpdateAlbumByAlbumId(Album updatedAlbum)
{
SqlCommand command = null;
string activeQuery =
"UPDATE Album " +
"SET GenreId=@genreId, ArtistId=@artistId, Title=@title, Price=@Price, AlbumArtUrl=@albumArtUrl " +
"WHERE AlbumId=@albumId";
try
{
command = new SqlCommand(activeQuery, MusicStoreDb.GetConnection());
command.Parameters.Add(new SqlParameter("@albumId", updatedAlbum.AlbumId));
command.Parameters.Add(new SqlParameter("@genreId", updatedAlbum.GenreId));
command.Parameters.Add(new SqlParameter("@artistId", updatedAlbum.ArtistId));
command.Parameters.Add(new SqlParameter("@title", updatedAlbum.Title));
command.Parameters.Add(new SqlParameter("@Price", 8.99));
command.Parameters.Add(new SqlParameter("@albumArtUrl", updatedAlbum.AlbumArtUrl));
command.Connection.Open();
int result = command.ExecuteNonQuery();
if (result != 1)
{
throw new Exception("Operation failed");
}
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
command.Connection?.Close();
}
}
public static IList <Artist> GetAllArtists()
{
IList <Artist> artistList = new List <Artist>();
SqlCommand command = null;
string selectStatement = "SELECT ArtistId, Name FROM Artist";
try
{
command = new SqlCommand(selectStatement, MusicStoreDb.GetConnection());
command.Connection.Open();
SqlDataReader reader = command.ExecuteReader();
int artistIdOrdinal = reader.GetOrdinal("ArtistId");
int nameOrdinal = reader.GetOrdinal("Name");
while (reader.Read())
{
Artist artist = new Artist();
artist.ArtistId = reader.GetInt32(artistIdOrdinal);
artist.Name = reader.GetString(nameOrdinal);
artistList.Add(artist);
}
reader.Close();
}
catch (SqlException ex)
{
Console.WriteLine(ex.Message);
}
finally
{
command.Connection?.Close();
}
return(artistList);
}