private void AddBookLoad(object sender, EventArgs e) { using (var context = new LibraryModel()) { var publishers = from p in context.publishers select p; publishersList = new List <string>(); foreach (var p in publishers) { publishersList.Add(p.publisher_name); } publishersList.Sort(); publisherComboBox.DataSource = publishersList; var genres = (from item in context.items select item.genre).Distinct(); genreList = new List <string>(); foreach (var g in genres) { genreList.Add(g); } genreComboBox.DataSource = genreList; } }
private void button1_Click(object sender, EventArgs e) { int id; bool result = int.TryParse(userIdTextBox.Text, out id); if (userIdTextBox.Text != "" && result) { using (var context = new LibraryModel()) { var userId = (from user in context.adminusers where user.admin_id == id select user).FirstOrDefault(); if (userId != null) { showMainForm(true); } else { MessageBox.Show("No User Found with given User ID", "Library Database", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); } } } else { MessageBox.Show("No User Found with given User ID", "Library Database", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning); } }
public Form1() { selectedItem = SelectedItem.Book; InitializeComponent(); switchToBookView(); genres = new List <string>(); durations = new List <string>(); mpaaRatings = new List <string>(); numTracks = new List <string>(); ratingsById = new Dictionary <int, double>(); minimumRating = 3; if (!isAdmin) { this.MainMenuStrip.Hide(); } // Get ratings using (var context = new LibraryModel()) { //GROUP BY AGGREGATE QUERY //======================== //Select avg(r.rating) , i.item_title //from Rating r //join item i on i.item_id = r.item_id //group by r.item_id //HAVING avg(r.rating) > UserAverage var ratings = from r in context.ratings //join r in context.ratings on i.item_id equals r.item_id group r by new { r.item_id } into g select new { Average = g.Average(p => p.rating1), g.Key.item_id }; foreach (var rating in ratings) { ratingsById.Add(rating.item_id, rating.Average.Value); } } }
private void onLoad(object sender, EventArgs e) { using (var context = new LibraryModel()) { var genres = (from item in context.items select item.genre).Distinct(); genreList = new List <string>(); foreach (var g in genres) { genreList.Add(g); } genreComboBox.DataSource = genreList; } }
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 searchButton_Click(object sender, EventArgs e) { searchResultsGrid.Rows.Clear(); var title = searchTextBox.Text; using (var context = new LibraryModel()) { switch (selectedItem) { case SelectedItem.Book: // Basic query var books = context.items.Include("book").Where(i => i.Item_title.ToUpper().Contains(title.ToUpper())).Where(i => i.book != null); //books.Join(b in book_published) //books.Include("book_publishedby"); // Apply filters books = addGenreFilter(books); // Execute query foreach (var book in books.ToList()) { if ((ratingsById.ContainsKey(book.item_id) && ratingsById[book.item_id] >= minimumRating) || (!ratingsById.ContainsKey(book.item_id))) { searchResultsGrid.Rows.Add(createBookRow(book)); } } break; case SelectedItem.Movie: // Basic query var movies = context.items.Include("movie").Where(i => i.Item_title.ToUpper().Contains(title.ToUpper())).Where(i => i.movie != null); // Apply filters movies = addGenreFilter(movies); movies = addDurationFilter(movies); movies = addMpaaRatingsFilter(movies); // Execute query foreach (var movie in movies.ToList()) { if ((ratingsById.ContainsKey(movie.item_id) && ratingsById[movie.item_id] >= minimumRating) || (!ratingsById.ContainsKey(movie.item_id))) { searchResultsGrid.Rows.Add(createMovieRow(movie)); } } break; case SelectedItem.Music: // Basic query var music = context.items.Include("musicalbum").Where(i => i.Item_title.ToUpper().Contains(title.ToUpper())).Where(i => i.musicalbum != null); // Apply filters music = addGenreFilter(music); music = addNumTracksFilter(music); // Execute query foreach (var album in music.ToList()) { if ((ratingsById.ContainsKey(album.item_id) && ratingsById[album.item_id] >= minimumRating) || (!ratingsById.ContainsKey(album.item_id))) { searchResultsGrid.Rows.Add(createMusicRow(album)); } } break; default: break; } } searchResultsGrid.Update(); }
private void getTopRatedButton_Click(object sender, EventArgs e) { using (var context = new LibraryModel()) { searchResultsGrid.Rows.Clear(); var title = searchTextBox.Text; switch (selectedItem) { //CORRELATED NESTED QUERY //========================== //select i.Item_title , avg(r.rating) as aRating, i.genre //from item i, rating r //where i.item_id = r.item_id //group by i.item_id //having //(aRating > // (select avg(rating.rating) // from rating, item i2 // where rating.item_id = i2.item_id // && // i2.genre = i.genre // group by i2.genre) // ); case SelectedItem.Book: var topRatedBooks = from i in context.items.ToList() join b in context.books on i.item_id equals b.item_id where i.ratings.Average(r => r.rating1) > ( from item in context.items where item.genre == i.genre select item.ratings.Average(r => r.rating1) ).Average() select i; foreach (var book in topRatedBooks) { searchResultsGrid.Rows.Add(createBookRow(book)); } break; case SelectedItem.Movie: var topRatedMovies = from i in context.items.ToList() join m in context.movies on i.item_id equals m.item_id where i.Item_title.Contains(title) && i.ratings.Average(r => r.rating1) > ( from item in context.items where item.genre == i.genre select item.ratings.Average(r => r.rating1) ).Average() select i; foreach (var movie in topRatedMovies) { searchResultsGrid.Rows.Add(createMovieRow(movie)); } break; case SelectedItem.Music: var topRatedMusic = from i in context.items.ToList() join m in context.musicalbums on i.item_id equals m.item_id where i.Item_title.Contains(title) && i.ratings.Average(r => r.rating1) > ( from item in context.items where item.genre == i.genre select item.ratings.Average(r => r.rating1) ).Average() select i; foreach (var album in topRatedMusic) { searchResultsGrid.Rows.Add(createMusicRow(album)); } break; default: break; } } }
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 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(); } }