private static ICollection <Author> CreateOrLoadAuthors(BookstoreDBEntities db, List <string> authorsName)
{
HashSet <Author> authors = new HashSet <Author>();
for (int i = 0; i < authorsName.Count; i++)
{
if (authorsName[i] == null || authorsName[i] == string.Empty)
{
throw new ArgumentNullException("Author name can't be null!");
}
string authorNameStr = authorsName[i];
Author authorEntry = db.Authors.Where(x => x.Name == authorNameStr).FirstOrDefault();
if (authorEntry == null)
{
authorEntry = new Author();
authorEntry.Name = authorsName[i];
db.Authors.Add(authorEntry);
}
authors.Add(authorEntry);
db.SaveChanges();
}
return(authors);
}
public static void AddBookComplex(string title, List<string> authors, string isbn, string price, string site, List<Review> reviewsList)
{
TransactionScope tran = new TransactionScope();
using (tran)
{
BookstoreDBEntities context = new BookstoreDBEntities();
Book curBook = new Book();
curBook.Title = title;
if (authors.Count > 0)
{
foreach (var author in authors)
{
curBook.Authors.Add(CreateOrLoadAuthor(context, author));
}
}
curBook.ISBN = isbn;
curBook.Price = Convert.ToDecimal(price);
curBook.WebSite = site;
if (reviewsList.Count > 0)
{
foreach (var review in reviewsList)
{
curBook.Reviews.Add(review);
}
}
context.Books.Add(curBook);
context.SaveChanges();
tran.Complete();
}
}
public static List <Review> SearchForReviewsByAuthor(string authorName)
{
var db = new BookstoreDBEntities();
return(db.Reviews.Include("Book").Include("Author").Where(x => x.Author.Name.ToLower() == authorName.ToLower()).
OrderBy(x => x.DateCreated).ThenBy(x => x.Text).ToList());
}
public static void AddToDatabase(string author, string title, string isbn, string price, string website)
{
using (BookstoreDBEntities bookstoreContext = new BookstoreDBEntities())
{
Book book = new Book();
var authorToCheck = CreateOrLoadAuthor(bookstoreContext, author);
book.Authors.Add(authorToCheck);
book.Title = title;
book.ISBN = isbn;
if (price == null)
{
book.Price = null;
}
else
{
var convertedPrice = decimal.Parse(price, System.Globalization.CultureInfo.InvariantCulture);
book.Price = convertedPrice;
}
book.URL = website;
bookstoreContext.Books.Add(book);
bookstoreContext.SaveChanges();
}
}
public static List <Review> SearchForReviewsByPeriod(string startDate, string endDate)
{
DateTime start = DateTime.Parse(startDate);
DateTime end = DateTime.Parse(endDate);
var db = new BookstoreDBEntities();
return(db.Reviews.Include("Book").Include("Author").Where(x => x.DateCreated > start && x.DateCreated < end).
OrderBy(x => x.DateCreated).ThenBy(x => x.Text).ToList());
}
public static int GetAuthorId(string author)
{
BookstoreDBEntities context = new BookstoreDBEntities();
int authorId =
(from a in context.Authors
where a.Name.ToLower() == author.ToLower()
select a.AuthorId).FirstOrDefault();
return authorId;
}
public static ICollection<Review> SearchByAuthorName(string authorName)
{
BookstoreDBEntities context = new BookstoreDBEntities();
var booksQuery =
(from r in context.Reviews.Include("Books")
select r);
booksQuery = booksQuery.Where(
x => x.Author.Name.ToLower() == authorName.ToLower());
booksQuery = booksQuery.OrderBy(x => x.DateOfCreation).OrderBy(x => x.ReviewText);
return booksQuery.ToList();
}
private static ICollection <Review> CreateReviews(BookstoreDBEntities db, List <ReviewImport> reviews, Book book)
{
HashSet <Review> reviewsHash = new HashSet <Review>();
foreach (var review in reviews)
{
Review reviewObj = new Review();
reviewObj.Text = review.Review;
if (review.Date != null)
{
reviewObj.DateCreated = DateTime.Parse(review.Date);
}
else
{
reviewObj.DateCreated = DateTime.Now;
}
if (review.Author != null)
{
if (review.Author == null || review.Author == string.Empty)
{
throw new ArgumentNullException("Author name can't be null!");
}
Author authorEntry = db.Authors.Where(x => x.Name == review.Author).FirstOrDefault();
if (authorEntry == null)
{
authorEntry = new Author();
authorEntry.Name = review.Author;
db.Authors.Add(authorEntry);
}
reviewObj.Author = authorEntry;
db.SaveChanges();
}
reviewObj.Book = book;
reviewsHash.Add(reviewObj);
}
return(reviewsHash);
}
public static void AddBook(string isbn, string price, string site, string author, string title)
{
TransactionScope tran = new TransactionScope();
using (tran)
{
BookstoreDBEntities context = new BookstoreDBEntities();
Book newBook = new Book();
newBook.ISBN = isbn;
if (price != null)
{
newBook.Price = decimal.Parse(price);
}
newBook.WebSite = site;
newBook.Title = title;
newBook.Authors.Add(CreateOrLoadAuthor(context, author));
context.Books.Add(newBook);
context.SaveChanges();
tran.Complete();
}
}
public static void ComplexBooksImport(List <string> authors, string title, string isbn, string price, string website, List <ReviewImport> reviews)
{
using (var db = new BookstoreDBEntities())
{
Book book = new Book();
book.Authors = CreateOrLoadAuthors(db, authors);
if (title == null || title == string.Empty)
{
throw new ArgumentNullException("Author name can't be null!");
}
book.Title = title;
if (db.Books.Any(x => x.ISBNnumber == isbn) == false)
{
book.ISBNnumber = isbn;
}
else
{
if (isbn != null)
{
throw new ArgumentException("ISBN:{0} already exists!", isbn);
}
}
if (price != null)
{
book.Price = decimal.Parse(price);
}
book.Website = website;
book.Reviews = CreateReviews(db, reviews, book);
db.Books.Add(book);
db.SaveChanges();
}
}
private static ICollection <Author> CreateOrLoadAuthors(BookstoreDBEntities db, string authorName)
{
if (authorName == null || authorName == string.Empty)
{
throw new ArgumentNullException("Author name can't be null!");
}
Author authorEntry = db.Authors.Where(x => x.Name == authorName).FirstOrDefault();
if (authorEntry == null)
{
authorEntry = new Author();
authorEntry.Name = authorName;
db.Authors.Add(authorEntry);
}
HashSet <Author> authors = new HashSet <Author>();
authors.Add(authorEntry);
db.SaveChanges();
return(authors);
}
public static List <Book> SimpleSearchForBooks(string title, string author, string isbn)
{
using (var db = new BookstoreDBEntities())
{
var query = db.Books.Include("Reviews").AsQueryable();
if (title != null && title != string.Empty)
{
query = query.Where(x => x.Title == title).Select(x => x);
}
if (author != null && author != string.Empty)
{
query = query.Where(x => x.Authors.Any(y => y.Name.ToLower() == author.ToLower()));
}
if (isbn != null && isbn != string.Empty)
{
query = query.Where(x => x.ISBNnumber.ToLower() == isbn.ToLower());
}
query = query.OrderBy(x => x.Title);
return(query.ToList());
}
}
public static ICollection<Review> SearchByDate(DateTime startDate, DateTime endDate)
{
BookstoreDBEntities context = new BookstoreDBEntities();
var booksQuery =
(from r in context.Reviews.Include("Books")
select r);
booksQuery = booksQuery.Where(
x => x.DateOfCreation >= startDate && x.DateOfCreation <= endDate);
booksQuery = booksQuery.OrderBy(x => x.DateOfCreation).OrderBy(x => x.ReviewText);
return booksQuery.ToList();
}
public static ICollection<Book> FindBook(string title, string author, string isbn)
{
BookstoreDBEntities context = new BookstoreDBEntities();
var booksQuery =
from b in context.Books.Include("Reviews")
select b;
if (title != null)
{
booksQuery =
from b in context.Books
where b.Title.ToLower() == title.ToLower()
select b;
}
if (author != null)
{
booksQuery = booksQuery.Where(
b => b.Authors.Any(n => n.Name == author));
}
if (isbn != null)
{
booksQuery = booksQuery.Where(
b => b.ISBN == isbn);
}
booksQuery = booksQuery.OrderBy(b => b.Title);
return booksQuery.ToList();
}
public static List<SimpleQueryInfo> FindByAuthorTitleOrISBN(string title,string author, string isbn )
{
using (BookstoreDBEntities bookstoreContext = new BookstoreDBEntities())
{
var query = bookstoreContext.Books
.Include("Authors").AsQueryable();
if(title!=null)
{
query = query.Where(x=>x.Title==title);
}
if (author != null)
{
query = query.Where(b => b.Authors.Any(t => t.Name == author));
}
if (isbn != null)
{
query = query.Where(b => b.ISBN==isbn);
}
query = query.OrderBy(x => x.Title);
var queryWithSelection = query.Select(x => new SimpleQueryInfo(){ Title = x.Title,ReviewsCount=x.Reviews.Count });
return queryWithSelection.ToList();
}
}
public static List<Review> FindReviewByAuthor(string author)
{
BookstoreDBEntities bookstoreContext = new BookstoreDBEntities();
var query = bookstoreContext.Reviews
.Include("Author")
.Where(x => x.Author.Name == author)
.OrderBy(x => x.CreatedOn)
.ThenBy(x => x.Text)
.ToList();
return query;
}
public static List<Review> FindReviewByPeriod(DateTime startDate, DateTime endDate)
{
BookstoreDBEntities bookstoreContext = new BookstoreDBEntities();
var query = bookstoreContext.Reviews
.Include("Author")
.Where(x => startDate <= x.CreatedOn && x.CreatedOn <= endDate)
.OrderBy(x => x.CreatedOn)
.OrderBy(x => x.Text)
.ToList();
return query;
}
public static void AddToDbComplex(XmlNodeList authors, string title, string isbn, string price, string website, List<ReviewInfo> reviews)
{
using (BookstoreDBEntities bookstoreContext = new BookstoreDBEntities())
{
Book book = new Book();
foreach (XmlNode author in authors)
{
var authorToCheck = CreateOrLoadAuthor(bookstoreContext, author.InnerText);
book.Authors.Add(authorToCheck);
}
book.Title = title;
book.ISBN = isbn;
if (price == null)
{
book.Price = null;
}
else
{
var convertedPrice = decimal.Parse(price, System.Globalization.CultureInfo.InvariantCulture);
book.Price = convertedPrice;
}
book.URL = website;
if (reviews != null)
{
foreach (var review in reviews)
{
Review currentReview = new Review();
if (review.Author == null)
{
currentReview.AuthorId = null;
}
else
{
var currentReviewAuthor = CreateOrLoadAuthor(bookstoreContext, review.Author);
currentReview.AuthorId = currentReviewAuthor.AuthorId;
}
currentReview.Text = review.Text;
//GetAuthorId(bookstoreContext, review.Author);
if (review.CreatedOn == null)
{
currentReview.CreatedOn = DateTime.Now;
}
currentReview.CreatedOn = review.CreatedOn;
book.Reviews.Add(currentReview);
bookstoreContext.SaveChanges();
}
}
bookstoreContext.Books.Add(book);
bookstoreContext.SaveChanges();
}
}
//private static int GetAuthorId(BookstoreDBEntities context, string name)
//{
// var authorid = context.Authors.Where(x => x.Name == name).Select(x => x.AuthorId).First();
// return authorid;
//}
private static Author CreateOrLoadAuthor(
BookstoreDBEntities context, string author)
{
Author existingUser =
(from u in context.Authors
where u.Name.ToLower() == author.ToLower()
select u).FirstOrDefault();
if (existingUser != null)
{
return existingUser;
}
Author newUser = new Author();
newUser.Name = author;
context.Authors.Add(newUser);
context.SaveChanges();
return newUser;
}