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