コード例 #1
0
 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;
 }
コード例 #2
0
 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
     }
 }
コード例 #3
0
 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;
 }
コード例 #4
0
        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;
        }
コード例 #5
0
        // 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);
            }
        }
コード例 #6
0
ファイル: AlbumsCRUD.cs プロジェクト: sabrie/TelerikAcademy
        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();
        }
コード例 #7
0
 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);
     }
 }
コード例 #8
0
        // 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);
            }
        }
コード例 #9
0
ファイル: AlbumsCRUD.cs プロジェクト: sabrie/TelerikAcademy
 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);
     }
 }
コード例 #10
0
        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();
        }