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