public static ObservableCollection<Artist> GetAllArtists()
{
ObservableCollection<Artist> artistsList = new ObservableCollection<Artist>();
using (SqlConnection connection = MusicStoreDataDB.GetConnection())
{
using (SqlCommand selectCommand = new SqlCommand())
{
selectCommand.Connection = connection;
selectCommand.CommandText = "spGetAllArtists";
selectCommand.CommandType = CommandType.StoredProcedure;
connection.Open();
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
while (reader.Read())
{
Artist artist = new Artist();
artist.ArtistId = (int)reader["ArtistId"];
artist.Name = reader["Name"].ToString();
artistsList.Add(artist);
}
reader.Close();
}
}
}
return artistsList;
}
public static Artist GetArtistByArtistId(int ArtistId)
{
Artist artist = new Artist();
using (SqlConnection connection = MusicStoreDataDB.GetConnection())
{
using (SqlCommand selectCommand = new SqlCommand())
{
selectCommand.Connection = connection;
selectCommand.CommandText = "spGetArtistByArtistId";
selectCommand.CommandType = CommandType.StoredProcedure;
selectCommand.Parameters.AddWithValue("@ArtistId", ArtistId);
connection.Open();
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
artist.ArtistId = (int)reader["ArtistId"];
artist.Name = reader["Name"].ToString();
}
reader.Close();
}
}
}
return artist;
}
// POST api/Artists
public HttpResponseMessage PostArtist(Artist artist)
{
if (ModelState.IsValid)
{
db.Artists.Add(artist);
db.SaveChanges();
HttpResponseMessage response = Request.CreateResponse(HttpStatusCode.Created, artist);
response.Headers.Location = new Uri(Url.Link("DefaultApi", new { id = artist.ArtistId }));
return response;
}
else
{
return Request.CreateResponse(HttpStatusCode.BadRequest);
}
}
internal static Song AddNewSongAsXML(HttpClient Client, string title, int year, string genre, Artist artist)
{
var album = new Song() { Title = title, Year = year, Genre = genre, Artist = artist };
var response = Client.PostAsXmlAsync("api/Songs", album).Result;
Song resultSong = response.Content.ReadAsAsync<Song>().Result;
if (response.IsSuccessStatusCode)
{
Console.WriteLine("Song added!");
return resultSong;
}
else
{
Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
}
return new Song();
}
internal static Artist AddNewArtistAsXml(HttpClient Client, string name, string country, DateTime dateOfBirth)
{
var artist = new Artist { Name = name, Country = country, DateOfBirth = dateOfBirth };
var response = Client.PostAsXmlAsync("api/Artists", artist).Result;
Artist resultArtist = response.Content.ReadAsAsync<Artist>().Result;
if (response.IsSuccessStatusCode)
{
Console.WriteLine("Artist added!");
return resultArtist;
}
else
{
Console.WriteLine("{0} ({1})", (int)response.StatusCode, response.ReasonPhrase);
}
return new Artist();
}
public static Artist GetArtistNameById(int ArtistId)
{
Artist artist = new Artist();
String selectStatement = "SELECT * FROM Artist WHERE ArtistId = @ArtistId";
using (SqlConnection connection = MusicStoreDB.GetConnection())
{
connection.Open();
using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
{
selectCommand.Parameters.AddWithValue("@ArtistId", ArtistId);
using (SqlDataReader reader = selectCommand.ExecuteReader())
{
if (reader.Read())
{
artist.ArtistId = (int)reader["ArtistId"];
artist.ArtistName = reader["Name"].ToString();
reader.Close();
}
}
}
}
return artist;
}
private Artist CreateOrUpdateArtist(Artist artist)
{
var existingArtist = db.Artists.Where(a => a.ArtistId == artist.ArtistId &&
a.Name == artist.Name &&
a.Country == artist.Country &&
a.DateOfBirth == artist.DateOfBirth)
.FirstOrDefault();
if (existingArtist != null)
{
return existingArtist;
}
else
{
Artist newArtist = db.Artists.Add(artist);
db.SaveChanges();
return newArtist;
}
}
internal static void UpdateArtistAsJson(HttpClient Client, int id, Artist artist)
{
var response = Client.PutAsJsonAsync("api/Artists/" + id, artist).Result;
if (response.IsSuccessStatusCode)
{
Console.WriteLine("Artist 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();
}
// PUT api/Artists/5
public HttpResponseMessage PutArtist(int id, Artist artist)
{
if (ModelState.IsValid && id == artist.ArtistId)
{
db.Entry(artist).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
return Request.CreateResponse(HttpStatusCode.NotFound);
}
return Request.CreateResponse(HttpStatusCode.OK);
}
else
{
return Request.CreateResponse(HttpStatusCode.BadRequest);
}
}