private void insertBookButton_Click(object sender, EventArgs e) { //validate fields //insert new book int genreIndex = genreComboBox.SelectedIndex; int pubIndex = publisherComboBox.SelectedIndex; string p = publishersList.ElementAt(pubIndex); string g = genreList.ElementAt(genreIndex); string title = titleTextBox.Text; string author = authorTextBox.Text; DateTime date = datePicker.Value; if (author == "" || title == "") { //validation error MessageBox.Show("Please fix invalid input", "Library Database", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); } else { using (var context = new LibraryModel()) { //INSERT INTO item(item_title, genre) //VALUES([title], [genre]); item i = new item { genre = g, Item_title = title, release_date = date }; context.items.Add(i); context.SaveChanges(); int id = (from item in context.items orderby item.item_id descending select item).FirstOrDefault().item_id; //INSERT INTO book(author, item_id) //VALUES([author], (SELECT item_id FROM item ORDER BY item_id DESC LIMIT 1)) book b = new book { item_id = id, author = author }; context.books.Add(b); int pid = (from pub in context.publishers where pub.publisher_name == p select pub).First().publisher_id; //INSERT INTO book_publishedby(item_id, publisher_id) //VALUES((SELECT item_id FROM item ORDER BY item_id DESC LIMIT 1), [publisherid]) book_publishedby published = new book_publishedby { item_id = id, publisher_id = pid }; context.book_publishedby.Add(published); context.SaveChanges(); var booksWithGenre = from item in context.items join bk in context.books on item.item_id equals bk.item_id group item.item_id by item.genre into group1 select new { count = group1.Count(), genre = group1.Key }; string message = "____________________________\n\n"; foreach (var selectedGenre in booksWithGenre) { message = message + selectedGenre.genre + "(" + selectedGenre.count + ")\n"; } MessageBox.Show("You've added '" + title + "'.\nThis book has been added into the genre '" + g + "'.\n" + message, "Library Database", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); context.Dispose(); } this.Close(); } }
private void insertMovie(object sender, EventArgs e) { string director = directorTextBox.Text; string title = titleTextBox.Text; string durationStr = durationTextBox.Text; int rIndex = ratingComboBox.SelectedIndex; int gIndex = genreComboBox.SelectedIndex; string genre = genreList.ElementAt(gIndex); string mpaaRating = ratings[rIndex]; DateTime date = datePicker.Value; //validate input int duration; bool result = int.TryParse(durationStr, out duration); if (director == "" || title == "" || durationStr == "" || !result) { //validation error MessageBox.Show("Please fix invalid input", "Library Database", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); } else { //validation successful //insert new movie using (var context = new LibraryModel()) { //INSERT INTO item(Item_title, genre, release_date) //VALUES([title], [genre], [date]) item i = new item { genre = genre, Item_title = title, release_date = date }; context.items.Add(i); context.SaveChanges(); int id = (from item in context.items orderby item.item_id descending select item).FirstOrDefault().item_id; //INSERT INTO movie(item_id, director, mpaa_rating, duration) //VALUES((SELECT item_id FROM item ORDER BY item_id DESC LIMIT 1), [director], [mpaa_rating], [duration]) movie m = new movie { item_id = id, director = director, mpaa_rating = mpaaRating, duration = duration }; context.movies.Add(m); context.SaveChanges(); var moviesWithGenre = from item in context.items join mv in context.movies on item.item_id equals mv.item_id group item.item_id by item.genre into group1 select new { count = group1.Count(), genre = group1.Key }; string message = "____________________________\n\n"; foreach (var selectedGenre in moviesWithGenre) { message = message + selectedGenre.genre + "(" + selectedGenre.count + ")\n"; } MessageBox.Show("You've added '" + title + "'.\nThis movie has been added into the genre '" + genre + "'.\n" + message, "Library Database", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); context.Dispose(); } this.Close(); } }
private void insertMusicAlbum(object sender, EventArgs e) { string artist = artistTextBox.Text; string title = titleTextBox.Text; string numTracksString = numTracksTextBox.Text; int gIndex = genreComboBox.SelectedIndex; string genre = genreList.ElementAt(gIndex); DateTime date = datePicker.Value; //validate input int numTracks; bool result = int.TryParse(numTracksString, out numTracks); if (artist == "" || title == "" || numTracksString == "" || !result) { //validation error MessageBox.Show("Please fix invalid input", "Library Database", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); } else { //validation successful //insert new music album using (var context = new LibraryModel()) { //INSERT INTO item(Item_title, genre, release_date) //VALUES([title], [genre], [date]) item i = new item { genre = genre, Item_title = title, release_date = date }; context.items.Add(i); context.SaveChanges(); int id = (from item in context.items orderby item.item_id descending select item).FirstOrDefault().item_id; //INSERT INTO musicalbum(item_id, artist, num_of_tracks) //VALUES((SELECT item_id FROM item ORDER BY item_id DESC LIMIT 1), [artist], [numTracks]) musicalbum m = new musicalbum { item_id = id, artist = artist, num_of_tracks = numTracks }; context.musicalbums.Add(m); context.SaveChanges(); var musicWithGenre = from item in context.items join music in context.musicalbums on item.item_id equals music.item_id group item.item_id by item.genre into group1 select new { count = group1.Count(), genre = group1.Key }; string message = "____________________________\n\n"; foreach (var selectedGenre in musicWithGenre) { message = message + selectedGenre.genre + "(" + selectedGenre.count + ")\n"; } MessageBox.Show("You've added '" + title + "'.\nThis music album has been added into the genre '" + genre + "'.\n" + message, "Library Database", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); context.Dispose(); } this.Close(); } }