// when we know the exact bookID and authorID we use this method
static void AddBookAuthorPair(int bookID, int authorID)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
connection.AddBookAuthorRecord(bookID, authorID);
}
}
static List<string> GetAllBooksAlphabetically()
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var books = from book in connection.Books
orderby book.Title
select book.Title;
return books.ToList();
}
}
static List<string> GetAllBooksSortedByAuthor()
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var books = from book in connection.Books
join bookAuthor in connection.BookAuthors on book.BookID equals bookAuthor.BookID
join author in connection.Authors on bookAuthor.AuthorID equals author.AuthorID
orderby author.First_Name ascending
select book.Title;
return books.ToList();
}
}
static List<string> GetAllBooksByAuthor(string authorFirstName, string authorLastName)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var books = from author in connection.Authors
join bookAuthor in connection.BookAuthors on author.AuthorID equals bookAuthor.AuthorID
join book in connection.Books on bookAuthor.BookID equals book.BookID
where author.First_Name == authorFirstName && author.Last_Name == authorLastName
select book.Title;
return books.ToList();
}
}
static List<string> GetAllBooksByAuthor(int authorID)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var books = from author in connection.Authors
join bookAuthor in connection.BookAuthors on author.AuthorID equals bookAuthor.AuthorID
join book in connection.Books on bookAuthor.BookID equals book.BookID
where author.AuthorID == authorID
select book.Title;
return books.ToList();
}
}
// gets the last added book/author pair and adds their IDs to the BookAuthor table
static void AddBookAuthorPair()
{
int bookIDToAdd, authorIDToAdd;
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var listOfBookIDs = from book in connection.Books
select book.BookID;
var listOfAuthorIDs = from author in connection.Authors
select author.AuthorID;
bookIDToAdd = listOfBookIDs.Max();
authorIDToAdd = listOfAuthorIDs.Max();
connection.AddBookAuthorRecord(bookIDToAdd, authorIDToAdd);
}
}
static List<string> GetAllBooksSortedByGenre()
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var books = from book in connection.Books
orderby book.Genre
select book.Title;
return books.ToList();
}
}
static void ReturnBook(int userID, int bookID)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var listOfBooks = (from book in connection.Books
select book.BookID).ToList();
var listOfUsers = (from user in connection.Users
select user.UserID).ToList();
if (!listOfBooks.Contains(bookID))
throw new BookNotExistantException();
if (!listOfUsers.Contains(userID))
throw new UserNotExistantException();
var listOfUserBookPairs = (from userBook in connection.UserBooks
select userBook.BookID).ToList();
if (!listOfUserBookPairs.Contains(bookID))
throw new UserBookPairNotExistantException();
var listOfUsersWhoHaveThisBook = (from userbook in connection.UserBooks
where userbook.BookID == bookID
select userbook.UserID).ToList();
if (!listOfUsersWhoHaveThisBook.Contains(userID))
throw new UserHasNotBeenGivenThisBookException();
connection.Users.Where(x => x.UserID == userID).FirstOrDefault().Books_lent--;
connection.RemoveUserBookRecord(userID, bookID);
connection.SubmitChanges();
}
Console.WriteLine("Book successfully returned.");
}
static void LoanBookToUser(int bookID, int userID, DateTime dateGiven, DateTime dateReturn)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var listOfBooks = (from book in connection.Books
select book.BookID).ToList();
if(!listOfBooks.Contains(bookID))
throw new BookNotExistantException();
var listOfUsers = (from user in connection.Users
select user.UserID).ToList();
if (!listOfUsers.Contains(userID))
throw new UserNotExistantException();
int numberOfBookCopies = (from book in connection.Books
where book.BookID == bookID
select book.Number_of_copies).SingleOrDefault();
int numberOfBookCopiesLoaned = (from book in connection.UserBooks
where book.BookID == bookID
select book).Count();
if (numberOfBookCopiesLoaned >= numberOfBookCopies)
{
Console.WriteLine("No copies left.");
return;
}
if(connection.Users.Where(x => x.UserID == userID).FirstOrDefault().Books_lent >= 5)
{
Console.WriteLine("This user has already been given 5 books. No more books can be loaned until some are returned.");
return;
}
connection.Users.Where(x => x.UserID == userID).FirstOrDefault().Books_lent++;
connection.AddUserBookRecord(bookID, userID, numberOfBookCopiesLoaned + 1, dateGiven, dateReturn);
connection.SubmitChanges();
}
Console.WriteLine("Book successfully loaned.");
}
static void InsertUser(string firstName, string lastName, string pseudonim, string email, int phone)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
if (pseudonim == "")
pseudonim = null;
if (email == "")
email = null;
User userToAdd = new User() { First_Name = firstName, Last_Name = lastName, Pseudonim = pseudonim, Email = email, Phone = phone, Books_lent = 0 };
connection.Users.InsertOnSubmit(userToAdd);
connection.SubmitChanges();
}
}
static void InsertBook(string title, string description, string datePublished, string publisher, string genre, int pages, int isbn, int numberOfCopies)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var listOfBooks = connection.Books;
foreach (var book in listOfBooks)
if (book.Title == title)
throw new BookAlreadyInDatabaseException();
foreach (var book in listOfBooks)
if (book.ISBN == isbn)
throw new BookISBNAlreadyExistsInDatabaseException();
if (description == "")
description = null;
Book bookToAdd = new Book() { Title = title, Description = description, Date_Published = DateTime.Parse(datePublished), Publisher = publisher, Genre = genre, Pages = pages, ISBN = isbn, Number_of_copies = numberOfCopies };
connection.Books.InsertOnSubmit(bookToAdd);
connection.SubmitChanges();
}
}
static void InsertAuthor(string firstName, string lastName, int yearBorn, int yearDied)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
Author authorToAdd = new Author() { First_Name = firstName, Last_Name = lastName, Year_Born = yearBorn, Year_Died = yearDied };
connection.Authors.InsertOnSubmit(authorToAdd);
connection.SubmitChanges();
}
}
static void GetInfoAboutBookByName(string title)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var listOfBooks = from book in connection.Books
where book.Title.Contains(title)
select book;
Book bookToGet = listOfBooks.First();
GetInfoAboutBook(bookToGet.ISBN);
}
}
static void GetInfoAboutBook(int isbn)
{
using (BookDatabaseDataContext connection = new BookDatabaseDataContext())
{
var listOfISBNs = (from Book in connection.Books
select Book.ISBN).ToList();
var listOfBooks = (from bookAuthor in connection.BookAuthors
select bookAuthor.BookID).ToList();
var listOfAuthors = (from Author in connection.Authors
select Author.AuthorID).ToList();
if(!listOfISBNs.Contains(isbn))
throw new ISBNDoesNotExistException();
Book book = connection.Books.Single(x => x.ISBN == isbn);
if(!listOfBooks.Contains(book.BookID))
throw new NoBookAuthorPairException();
int authorID = connection.BookAuthors.Single(x => x.BookID == book.BookID).AuthorID;
if (!listOfAuthors.Contains(authorID))
throw new MissingAuthorException();
Author author = connection.Authors.Single(x => x.AuthorID == authorID);
Console.WriteLine($"Title: {book.Title}");
Console.WriteLine($"Description: {book.Description}");
Console.WriteLine($"Date published: {book.Date_Published.ToString("yyyy MMMM dd")}");
Console.WriteLine($"Publisher: {book.Publisher}");
Console.WriteLine($"Genre: {book.Genre}");
Console.WriteLine($"Pages: {book.Pages}");
Console.WriteLine($"ISBN: {book.ISBN}\n");
Console.WriteLine($"Author first name: {author.First_Name}");
Console.WriteLine($"Author last name: {author.Last_Name}");
Console.WriteLine($"Year born: {author.Year_Born}");
Console.WriteLine($"Year died: {author.Year_Died}");
}
}