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();
}
}