public static List<Album> getAlbumListByGenreId(int genreId)
{
List<Album> albumList = new List<Album>();
String selectStatement = "SELECT * FROM Album WHERE GenreID = @GenreID";
using (SqlConnection connection = MusicStoreDB.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@GenreId", genreId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Album album = new Album();
album.AlbumId = (int)reader["AlbumId"];
album.GenreId = (int)reader["GenreId"];
album.ArtistId = (int)reader["ArtistId"];
album.Title = reader["Title"].ToString();
album.Price = (decimal)reader["Price"];
album.AlbumArtUrl = reader["AlbumArtUrl"].ToString();
album.ArtistName = ArtistDB.GetArtistNameById(album.ArtistId).ArtistName;
albumList.Add(album);
}
reader.Close();
}
}
}
return albumList;
}
public static void SetAlbum(Album album)
{
try {
using (SqlConnection connection = MusicStoreDataDB.GetConnection())
{
using (SqlCommand updateCommand = new SqlCommand())
{
updateCommand.Connection = connection;
updateCommand.CommandText = "spUpdateAlbum";
updateCommand.CommandType = CommandType.StoredProcedure;
updateCommand.Parameters.AddWithValue("@AlbumId", album.AlbumId);
updateCommand.Parameters.AddWithValue("@GenreId", album.GenreId);
updateCommand.Parameters.AddWithValue("@ArtistId", album.ArtistId);
updateCommand.Parameters.AddWithValue("@Title", album.Title);
updateCommand.Parameters.AddWithValue("@Price", album.Price);
updateCommand.Parameters.AddWithValue("@AlbumArtUrl", album.AlbumArtUrl);
connection.Open();
updateCommand.ExecuteNonQuery();
}
}
}
catch (Exception)
{
//SQL Error
}
}
public static Album GetAlbum(int AlbumId)
{
Album album = new Album();
using (SqlConnection connection = MusicStoreDataDB.GetConnection())
{
using (SqlCommand selectCommand = new SqlCommand())
{
selectCommand.Connection = connection;
selectCommand.CommandText = "spGetAlbum";
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@AlbumId", AlbumId);
connection.Open();
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
album.AlbumId = (int)reader["AlbumId"];
album.GenreId = (int)reader["GenreId"];
album.ArtistId = (int)reader["ArtistId"];
album.Title = reader["Title"].ToString();
album.Price = (Decimal)reader["Price"];
album.AlbumArtUrl = reader["AlbumArtUrl"].ToString();
album.Genre = GenreDB.GetGenreById(album.GenreId);
album.Artist = ArtistDB.GetArtistByArtistId(album.ArtistId);
}
reader.Close();
}
}
}
return album;
}
public static List<Album> GetAlbumList()
{
List<Album> albumsList = new List<Album>();
using (SqlConnection connection = MusicStoreDataDB.GetConnection())
{
using (SqlCommand selectCommand = new SqlCommand())
{
selectCommand.Connection = connection;
selectCommand.CommandText = "spGetAllAlbums";
selectCommand.CommandType = CommandType.StoredProcedure;
connection.Open();
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Album album = new Album();
album.AlbumId = (int)reader["AlbumId"];
album.GenreId = (int)reader["GenreId"];
album.ArtistId = (int)reader["ArtistId"];
album.Title = reader["Title"].ToString();
album.Price = (Decimal)reader["Price"];
album.AlbumArtUrl = reader["AlbumArtUrl"].ToString();
album.Genre = GenreDB.GetGenreById(album.GenreId);
album.Artist = ArtistDB.GetArtistByArtistId(album.ArtistId);
albumsList.Add(album);
}
reader.Close();
}
}
}
return albumsList;
}
// POST api/Albums
public HttpResponseMessage PostAlbum(Album album)
{
if (ModelState.IsValid)
{
db.Albums.Add(album);
db.SaveChanges();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, album);
response.Headers.Location = new Uri(Url.Link("DefaultApi", new { id = album.AlbumId }));
return response;
}
else
{
return Request.CreateResponse(HttpStatusCode.BadRequest);
}
}
internal static Album AddNewAlbumsAsJson(HttpClient Client, string title, int year, string producer, ICollection<Song> songs, ICollection<Artist> artists)
{
var album = new Album() { Title = title, Year = year, Producer = producer, Songs = songs, Artists = artists };
var response = Client.PostAsJsonAsync("api/Albums", album).Result;
Album resultAlbum = response.Content.ReadAsAsync<Album>().Result;
if (response.IsSuccessStatusCode)
{
Console.WriteLine("Album added!");
return resultAlbum;
}
else
{
Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
}
return new Album();
}
private void UpdateAlbum()
{
try
{
Album album = new Album();
album.AlbumId = Int32.Parse(albumIdBox.Text);
album.GenreId = (int) genresComboBox.SelectedValue;
album.ArtistId = (int) artistsComboBox.SelectedValue;
album.Title = titleTextBox.Text;
album.Price = Decimal.Parse(PriceTextBox.Text);
album.AlbumArtUrl = AlbumArtUrlTextBox.Text;
AlbumDB.SetAlbum(album);
statusLabel.Content = "Album update SUCCESSFUL!";
statusLabel.Foreground = new SolidColorBrush(Colors.DarkGray);
}
catch (Exception)
{
statusLabel.Content = "Album update FAILED!";
statusLabel.Foreground = new SolidColorBrush(Colors.Red);
}
}
// PUT api/Albums/5
public HttpResponseMessage PutAlbum(int id, Album album)
{
if (ModelState.IsValid && id == album.AlbumId)
{
db.Entry(album).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
return Request.CreateResponse(HttpStatusCode.NotFound);
}
return Request.CreateResponse(HttpStatusCode.OK);
}
else
{
return Request.CreateResponse(HttpStatusCode.BadRequest);
}
}
internal static void UpdateAlbumAsJson(HttpClient Client, int id, Album album)
{
var response = Client.PutAsJsonAsync("api/Albums/" + id, album).Result;
if (response.IsSuccessStatusCode)
{
Console.WriteLine("Album updated!");
}
else
{
Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
}
}
internal static void Main()
{
// ARTIST OPERATIONS
//-------------------------
// Add an Accept header for JSON format.
Client.DefaultRequestHeaders.Accept.Add(
new MediaTypeWithQualityHeaderValue("application/json"));
// Adds artists - JSON format
Artist michaelJackson = ArtistCRUD.AddNewArtistAsJson(Client, "Michael Jackson", "USA", new DateTime(1958, 8, 29));
Artist roxette = ArtistCRUD.AddNewArtistAsJson(Client, "Roxette", "Sweden", new DateTime(1986, 1, 1));
Artist abba = ArtistCRUD.AddNewArtistAsJson(Client, "ABBA", "Sweden", new DateTime(1972, 1, 1));
// Updates an artist by id - JSON format
Artist UpdatedArtistJson = new Artist() { ArtistId = roxette.ArtistId, Name = "Vassil Naidenov", Country = "Bulgaria", DateOfBirth = new DateTime(1952, 5, 3) };
ArtistCRUD.UpdateArtistAsJson(Client, roxette.ArtistId, UpdatedArtistJson);
// Add an Accept header for XML format.
Client.DefaultRequestHeaders.Accept.Add(
new MediaTypeWithQualityHeaderValue("application/xml"));
// Adds artists - XML format
Artist grafa = ArtistCRUD.AddNewArtistAsXml(Client, "Grafa", "Bulgaria", new DateTime(1980, 5, 6));
Artist mariaIlieva = ArtistCRUD.AddNewArtistAsXml(Client, "Maria Ilieva", "Bulgaria", new DateTime(1981, 1, 5));
Artist miro = ArtistCRUD.AddNewArtistAsXml(Client, "Miro", "Bulgaria", new DateTime(1978, 1, 1));
// Updates an artist by id - XML format
Artist UpdatedArtistXml = new Artist() { ArtistId = abba.ArtistId, Name = "Margarita Hranova", Country = "Bulgaria", DateOfBirth = new DateTime(1957, 5, 3) };
ArtistCRUD.UpdateArtistAsJson(Client, abba.ArtistId, UpdatedArtistXml);
// Lists all artists
Console.WriteLine();
Console.WriteLine("All Artists:");
ArtistCRUD.PrintAllArtists(Client);
Console.WriteLine();
// Deletes artists by id
ArtistCRUD.DeleteArtist(Client, mariaIlieva.ArtistId);
// Adds an Accept header for JSON format.
Client.DefaultRequestHeaders.Accept.Add(
new MediaTypeWithQualityHeaderValue("application/json"));
// SONG OPERATIONS
//------------------------
// Adds songs
Song myGirlSong = SongsCRUD.AddNewSongAsJson(Client, "My Girl", 1975, "Pop", michaelJackson);
Song gotToBeThere = SongsCRUD.AddNewSongAsJson(Client, "Got to Be There", 1975, "Pop", michaelJackson);
Song ben = SongsCRUD.AddNewSongAsJson(Client, "Ben", 1975, "Pop", michaelJackson);
Song withAChildsHeart = SongsCRUD.AddNewSongAsJson(Client, "With a Child's Heart", 1975, "Pop", michaelJackson);
Song oneDayInYourLife = SongsCRUD.AddNewSongAsJson(Client, "One Day In Your Life", 1975, "Pop", michaelJackson);
Song aintNoSunshine = SongsCRUD.AddNewSongAsJson(Client, "Ain't No Sunshine", 1975, "Pop", michaelJackson);
// Update a song
Song updatedBen = new Song() {SongId = ben.SongId, Title = "BEN", Year = 1975, Genre = "Pop", Artist = michaelJackson};
SongsCRUD.UpdateSongAsJson(Client, ben.SongId, updatedBen);
// Gets all songs
Console.WriteLine("All songs:");
SongsCRUD.PrintAllSongs(Client);
// Delete a song
SongsCRUD.DeleteSong(Client, gotToBeThere.SongId);
// ALBUM OPERATIONS
//------------------------
IList<Song> michaelJacksonSongs = new List<Song>()
{
myGirlSong,
gotToBeThere,
ben,
withAChildsHeart,
oneDayInYourLife,
aintNoSunshine
};
IList<Artist> michaelJacksonAlbumArtists = new List<Artist>()
{
michaelJackson
};
// Add an album with list of songs and artists
Album michaelJacksonAlbum = AlbumsCRUD.AddNewAlbumsAsJson(
Client, "The Best of Michael Jackson", 1975, "Producer1", michaelJacksonSongs, michaelJacksonAlbumArtists);
// Update an album
Album updatedMichaelJacksonAlbum = new Album() { AlbumId = michaelJacksonAlbum.AlbumId, Title = "The Best of Michael Jackson", Year = 1975, Producer = "Sony Music", Songs = michaelJacksonSongs, Artists = michaelJacksonAlbumArtists };
AlbumsCRUD.UpdateAlbumAsJson(Client, michaelJacksonAlbum.AlbumId, updatedMichaelJacksonAlbum);
// Get all albums
Console.WriteLine("All Albums:");
AlbumsCRUD.PrintAllAlbums(Client);
Console.WriteLine();
// Delete an album
AlbumsCRUD.DeleteAlbum(Client, updatedMichaelJacksonAlbum.AlbumId);
Client.Dispose();
}
