public static void AddBookComplex(List<string> authors, string title, string isbn, string price, string website)
{
BookstoreEntities context = new BookstoreEntities();
Book newBook = new Book();
foreach (var author in authors)
{
newBook.Authors.Add(CreateOrLoadAuthor(context, author));
}
newBook.Title = title.Trim();
if (isbn != null)
{
newBook.ISBN = long.Parse(isbn.Trim());
}
if (price != null)
{
newBook.Price = decimal.Parse(price.Trim());
}
context.Books.Add(newBook);
try
{
context.SaveChanges();
}
catch (DbEntityValidationException e)
{
}
}
public static List<Book> FindBooksByTitleAuthorIsbn(string title, string authorName, string isbn)
{
BookstoreEntities context = new BookstoreEntities();
var booksQuery = from b in context.Books
select b;
if (title != null)
{
booksQuery = from b in context.Books
where b.Title.ToLower() == title.ToLower()
select b;
}
if (isbn != null)
{
booksQuery = booksQuery.Where(b => b.ISBN == isbn);
}
if (authorName != null)
{
booksQuery = booksQuery.Where(
b => b.Authors.Any(a => a.Name.ToLower() == authorName.ToLower()));
}
booksQuery = booksQuery.OrderBy(b => b.Title);
return booksQuery.ToList();
}
public static void AddBookWithReview(List<string> authorNames, string title,
string isbn, string price, string website, List<Review> reviewList, BookstoreEntities context)
{
Book newBook = new Book();
foreach (var author in authorNames)
{
newBook.Authors.Add(CreateOrLoadAuthor(author.Trim(), context));
}
newBook.Title = title.Trim();
if (isbn != null)
{
newBook.ISBN = isbn.Trim();
}
if (price != null)
{
newBook.Price = decimal.Parse(price);
}
if (website != null)
{
newBook.WebSite = website.Trim();
}
foreach (var review in reviewList)
{
newBook.Reviews.Add(review);
}
context.Books.Add(newBook);
context.SaveChanges();
}
public static Author AddAuthor(string name)
{
BookstoreEntities context = new BookstoreEntities();
Author author = CreateOrLoadAuthor(context, name);
return author;
}
public List<Book> FindBooksByTitleAuthorAndISBN(BookstoreEntities context,
BookstoreDAL bookstoreDAL, string bookTitle, string authorName, string ISBN)
{
if (string.IsNullOrEmpty(bookTitle) && string.IsNullOrEmpty(authorName) &&
string.IsNullOrEmpty(ISBN))
{
return new List<Book>();
}
IQueryable<Book> booksQuery = context.Books.Include("Authors");
Book book = new Book();
if (!string.IsNullOrEmpty(bookTitle))
{
booksQuery = booksQuery.Where(
b => b.Title.ToLower() == bookTitle.ToLower());
}
if (!string.IsNullOrEmpty(authorName))
{
booksQuery = booksQuery.Where(
b => b.Authors.Any(a => a.Name.ToLower() == authorName.ToLower()));
}
if (!string.IsNullOrEmpty(ISBN))
{
booksQuery = booksQuery.Where(b => b.ISBN == ISBN);
}
booksQuery = booksQuery.OrderBy(b => b.Title);
return booksQuery.ToList();
}
static void SimpleSearch()
{
XmlDocument doc = new XmlDocument();
doc.Load(Bookstore.Data.Settings.Default.simpleSearchFile);
XmlNode query = doc.SelectSingleNode("/query");
string title = DataImporter.GetValue(query, "title");
string author = DataImporter.GetValue(query, "author");
string isbn = DataImporter.GetValue(query, "isbn");
BookstoreEntities context = new BookstoreEntities();
using (context)
{
IList<Book> found = BookstoreDAL.FindBook(title, author, isbn, context);
if (found.Count > 0)
{
Console.WriteLine("{0} books found:", found.Count);
foreach (var book in found)
{
if (book.Reviews.Count > 0)
{
Console.WriteLine("{0} --> {1} reviews", book.Title, book.Reviews.Count);
}
else
{
Console.WriteLine("{0} --> no reviews", book.Title);
}
}
}
else
{
Console.WriteLine("Nothing found");
}
}
}
static void Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
BookstoreDAL dal = new BookstoreDAL();
XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.Load("../../simple-books.xml");
xmlDoc.Load("../../../tests/5/simple-books.xml");
string xPathQuery = "/catalog/book";
XmlNodeList books = xmlDoc.SelectNodes(xPathQuery);
using (var context = new BookstoreEntities())
{
foreach (XmlNode bookNode in books)
{
string title = GetChildText(bookNode, "title");
if (string.IsNullOrEmpty(title))
{
throw new ArgumentException("The Book <title> node is empty or missing",
"title");
}
string ISBN = GetChildText(bookNode, "isbn");
string priceString = GetChildText(bookNode, "price");
decimal? price = null;
if (priceString != null)
{
price = decimal.Parse(priceString);
}
string websiteUrl = GetChildText(bookNode, "web-site");
List<string> authorNames = new List<string>();
foreach (XmlNode authorNode in bookNode.SelectNodes("author"))
{
string authorName = authorNode.InnerText.Trim();
if (authorName == String.Empty)
{
throw new ArgumentException("Author name cannot be empty.");
}
authorNames.Add(authorName);
}
if (authorNames.Count == 0)
{
throw new ArgumentException("The <author> node is empty or missing.",
"author");
}
using (var tran = new TransactionScope())
{
dal.AddBook(context, authorNames, title, ISBN, price, websiteUrl);
tran.Complete();
}
}
}
}
static void ComplexSearch(XmlTextWriter writer)
{
BookstoreEntities context = new BookstoreEntities();
using (context)
{
XmlDocument doc = new XmlDocument();
doc.Load(Bookstore.Data.Settings.Default.complexSearchReadFile);
XmlNodeList queries = doc.SelectNodes("/review-queries/query");
foreach (XmlNode query in queries)
{
string type = query.Attributes["type"].Value;
if (type == "by-period")
{
string start = query.SelectSingleNode("start-date").InnerText;
string end = query.SelectSingleNode("end-date").InnerText;
IList<Review> found =
BookstoreDAL.FindReviewPeriod(start, end, context);
WriteResultSet(writer, found);
}
else if (type == "by-author")
{
string author = query.SelectSingleNode("author-name").InnerText;
IList<Review> found =
BookstoreDAL.FindReviewAuthor(author, context);
WriteResultSet(writer, found);
}
SearchLogsImporter.AddLog(DateTime.Now, query.OuterXml);
}
}
}
private static List<Review> FindReviewsByReviewQuery(BookstoreDAL dal,
BookstoreEntities context, XmlNode query)
{
string type = GetChildText(query, "@type");
List<Review> foundReviews = null;
switch (type)
{
case "by-period":
string startDateString = GetChildText(query, "start-date");
string endDateString = GetChildText(query, "end-date");
DateTime startDate = DateTime.Parse(startDateString);
DateTime endDate = DateTime.Parse(endDateString);
foundReviews =
dal.FindReviewsByPeriod(context, startDate, endDate);
break;
case "by-author":
string authorName = GetChildText(query, "author-name");
foundReviews =
dal.FindReviewsByAuthor(context, authorName);
break;
default:
throw new ArgumentException("Invalid review query type. " +
"Must be 'by-period' or 'by-author'.");
}
return foundReviews;
}
public int FindBookReviewCount(BookstoreEntities context,
BookstoreDAL bookstoreDAL, Book book)
{
int bookReviewCount =
context.Reviews.Include("Book").Where(r => r.BookId == book.Id).Count();
return bookReviewCount;
}
// Task 4
public void ComplexImportOfBook(
IList<string> authorsNames,
string title,
IList<ReviewTransferObject> reviewTransferObjects,
string isbn,
decimal? price,
string webSite)
{
BookstoreEntities context = new BookstoreEntities();
Book newBook = new Book();
newBook.ISBN = isbn;
newBook.Title = title;
newBook.Price = price;
newBook.WebSite = webSite;
foreach (string authorName in authorsNames)
{
Author author = CreateOrLoadAuthor(context, authorName);
newBook.Authors.Add(author);
}
foreach (ReviewTransferObject reviewTransferObject in reviewTransferObjects)
{
Review review = SerializeToReview(context, reviewTransferObject);
newBook.Reviews.Add(review);
}
context.Books.Add(newBook);
context.SaveChanges();
}
private static Review GetSingleReview(BookstoreEntities context, XmlNode reviewItem)
{
if (reviewItem.Name != "review" || reviewItem.InnerText.Length == 0)
{
throw new ApplicationException("Invalid input XML file - Invalid Review tag");
}
var review = new Review();
review.ReviewText = reviewItem.InnerText.Trim();
if (reviewItem.Attributes["date"] != null)
{
review.Date = DateTime.Parse(reviewItem.Attributes["date"].InnerText.Trim());
}
else
{
review.Date = DateTime.Now.Date;
}
if (reviewItem.Attributes["author"] != null)
{
review.Author = GetSingleAuthor(context, reviewItem.Attributes["author"]);
}
return review;
}
static void Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
BookstoreDAL dataAccessLayer = new BookstoreDAL();
XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.Load("../../complex-books.xml");
//xmlDoc.Load(@"../../../tests/4/test-transaction.xml");
xmlDoc.Load(@"../../../tests/5/complex-books.xml");
string xPathQuery = "/catalog/book";
XmlNodeList books = xmlDoc.SelectNodes(xPathQuery);
using (var context = new BookstoreEntities())
{
foreach (XmlNode bookNode in books)
{
string title = GetChildText(bookNode, "title");
if (string.IsNullOrEmpty(title))
{
throw new ArgumentException("The Book <title> node is empty or missing",
"title");
}
string ISBN = GetChildText(bookNode, "isbn");
string priceString = GetChildText(bookNode, "price");
decimal? price = null;
if (priceString != null)
{
price = decimal.Parse(priceString);
}
string websiteUrl = GetChildText(bookNode, "web-site");
List<string> authorNames = new List<string>();
foreach (XmlNode authorNode in bookNode.SelectNodes("authors/author"))
{
authorNames.Add(authorNode.InnerText);
}
Book addedBook =
dataAccessLayer.AddBook(context, authorNames, title,
ISBN, price, websiteUrl);
using (var tran = new TransactionScope())
{
foreach (XmlNode reviewNode in bookNode.SelectNodes("reviews/review"))
{
ProcessReviewNode(context, dataAccessLayer, addedBook, reviewNode);
}
tran.Complete();
}
}
}
}
// Task 6
public IEnumerable<FoundReviewTransferObject> GetReviewsByAuthorName(string authorName)
{
BookstoreEntities context = new BookstoreEntities();
var reviewsInPeriod = context.Reviews.Include("Book").Include("Book.Authors")
.Where(r => r.Author.Name.ToLower() == authorName)
.OrderBy(r => r.DateOfCreation).ThenBy(r => r.Content);
IList<FoundReviewTransferObject> reviewsFound = new List<FoundReviewTransferObject>();
DeserializeFoundReviews(reviewsInPeriod, reviewsFound);
return reviewsFound;
}
// Task 6
public IEnumerable<FoundReviewTransferObject> GetReviewsByPeriod(DateTime starDate, DateTime endDate)
{
BookstoreEntities context = new BookstoreEntities();
var reviewsInPeriod = context.Reviews.Include("Book").Include("Book.Authors")
.Where(r => r.DateOfCreation.CompareTo(starDate) >= 0 &&
r.DateOfCreation.CompareTo(endDate) <= 0)
.OrderBy(r => r.DateOfCreation).ThenBy(r => r.Content);
IList<FoundReviewTransferObject> reviewsFound = new List<FoundReviewTransferObject>();
DeserializeFoundReviews(reviewsInPeriod, reviewsFound);
return reviewsFound;
}
public static Author CreateOrLoadAuthor(string author, BookstoreEntities context)
{
Author currentAuthor = context.Authors.FirstOrDefault(x => x.Name.ToLower() == author.ToLower());
if (currentAuthor == null)
{
currentAuthor = new Author();
currentAuthor.Name = author;
context.Authors.Add(currentAuthor);
context.SaveChanges();
}
return currentAuthor;
}
public static Review AddReview(int? authorId, int? bookId, DateTime reviewDate, string reviewContent)
{
BookstoreEntities context = new BookstoreEntities();
Review review = new Review();
review.AuthorId = authorId;
review.BookId = bookId;
review.Date = reviewDate;
review.Text = reviewContent;
context.Reviews.Add(review);
context.SaveChanges();
return review;
}
public static void AddBookstore(string author, string title, long? isbn, decimal? price, string webSite)
{
BookstoreEntities context = new BookstoreEntities();
Book newBook = new Book();
newBook.Author = CreateOrLoadAuthor(context, author);
newBook.Title = title;
newBook.ISBN = isbn;
newBook.Price = price;
newBook.WebSite = webSite;
context.Books.Add(newBook);
context.SaveChanges();
}
private Author CreateOrLoadAuthor(BookstoreEntities context, string authorName)
{
Author author = context.Authors.FirstOrDefault<Author>(a => a.Name == authorName);
if (author != null)
{
return author;
}
Author newAuthor = new Author() { Name = authorName };
context.Authors.Add(newAuthor);
context.SaveChanges();
return newAuthor;
}
private Review SerializeToReview(BookstoreEntities context, ReviewTransferObject reviewTransferObject)
{
Review review = new Review();
review.Content = reviewTransferObject.Content;
string authorName = reviewTransferObject.Author;
if (authorName != null)
{
review.Author = CreateOrLoadAuthor(context, authorName);
}
review.DateOfCreation = reviewTransferObject.Date;
return review;
}
// Tasks 3
public void SimpleImportOfBook(string authorName, string title, string isbn, decimal? price, string webSite)
{
BookstoreEntities context = new BookstoreEntities();
Book newBook = new Book();
Author author = CreateOrLoadAuthor(context, authorName);
newBook.Authors.Add(author);
newBook.ISBN = isbn;
newBook.Price = price;
newBook.Title = title;
newBook.WebSite = webSite;
context.Books.Add(newBook);
context.SaveChanges();
}
static void ComplexImport()
{
BookstoreEntities context = new BookstoreEntities();
using (context)
{
XmlDocument doc = new XmlDocument();
doc.Load(Bookstore.Data.Settings.Default.complexImportFile);
XmlNodeList catalog = doc.SelectNodes("/catalog/book");
foreach (XmlNode book in catalog)
{
TransactionScope scope = new TransactionScope(
TransactionScopeOption.Required,
new TransactionOptions()
{
IsolationLevel = IsolationLevel.RepeatableRead
});
using (scope)
{
List<string> authorNames = new List<string>();
XmlNodeList authors = book.SelectNodes("authors/author");
foreach (XmlNode author in authors)
{
authorNames.Add(author.InnerText);
}
string title = GetValue(book, "title");
string isbn = GetValue(book, "isbn");
string price = GetValue(book, "price");
string website = GetValue(book, "web-site");
List<Review> reviewList = new List<Review>();
XmlNodeList reviews = book.SelectNodes("reviews/review");
foreach (XmlNode review in reviews)
{
Review currentReview = new Review();
currentReview.ReviewText = review.InnerText;
currentReview.Date = GetDate(review);
string authorName = GetAuthor(review);
currentReview.Author = BookstoreDAL.CreateOrLoadAuthor(authorName, context);
reviewList.Add(currentReview);
}
BookstoreDAL.AddBookWithReview(authorNames, title, isbn, price, website, reviewList, context);
scope.Complete();
}
}
}
}
public Author CreateOrLoadAuthor(BookstoreEntities context, string authorName)
{
Author existingAuthor = context.Authors.Where(a => a.Name == authorName)
.FirstOrDefault();
if (existingAuthor != null)
{
return existingAuthor;
}
Author newAuthor = new Author();
newAuthor.Name = authorName;
context.Authors.Add(newAuthor);
context.SaveChanges();
return newAuthor;
}
private static Author CreateOrLoadAuthor(BookstoreEntities context, string author)
{
Author existingAuthor =
(from a in context.Authors
where a.AuthorName == author
select a).FirstOrDefault();
if (existingAuthor != null)
{
return existingAuthor;
}
Author newAuthor = new Author();
newAuthor.AuthorName = author;
context.Authors.Add(newAuthor);
return newAuthor;
}
public static Review AddReview(Book book, string reviewAuthor,
DateTime reviewDate, string reviewContent)
{
BookstoreEntities context = new BookstoreEntities();
Review newReview = new Review();
newReview.BookId = book.BookId;
if (reviewAuthor != null)
{
Author author = CreateOrLoadAuthor(context, reviewAuthor);
newReview.Author = author;
}
newReview.Date = reviewDate;
newReview.Content = reviewContent;
context.Reviews.Add(newReview);
context.SaveChanges();
return newReview;
}
private static Review CreateOrLoadReview(BookstoreEntities context, int reviewId)
{
Review existingReview =
(from r in context.Reviews
where r.ReviewId == reviewId
select r).FirstOrDefault();
if (existingReview != null)
{
return existingReview;
}
Review newReview = new Review();
newReview.ReviewId = reviewId;
context.Reviews.Add(newReview);
return newReview;
}
static void Main(string[] args)
{
Thread.CurrentThread.CurrentCulture = CultureInfo.InvariantCulture;
BookstoreDAL dal = new BookstoreDAL();
XmlDocument xmlDoc = new XmlDocument();
//xmlDoc.Load("../../simple-query.xml");
xmlDoc.Load("../../../tests/5/test6.xml");
string xPathQuery = "/query";
using (var context = new BookstoreEntities())
{
XmlNode query = xmlDoc.SelectSingleNode(xPathQuery);
string title = GetChildText(query, "title");
string author = GetChildText(query, "author");
string ISBN = GetChildText(query, "isbn");
List<Book> foundBooks = dal.FindBooksByTitleAuthorAndISBN(context,
dal, title, author, ISBN);
int foundBooksCount = foundBooks.Count;
if (foundBooksCount > 0)
{
Console.WriteLine("{0} books found:", foundBooks.Count);
foreach (var book in foundBooks)
{
int numberOfReviews = dal.FindBookReviewCount(context, dal, book);
if (numberOfReviews > 0)
{
Console.WriteLine("{0} --> {1} reviews", book.Title, numberOfReviews);
}
else
{
Console.WriteLine("{0} --> no reviews", book.Title);
}
}
}
else
{
Console.WriteLine("Nothing found");
}
}
}
public static Dictionary<string, int> FindBooksByTitleAndAuthorAndISBN(string title, string author, string isbn)
{
/*Problem 5. Simple Search for Books. */
Dictionary<string, int> booksReviews = new Dictionary<string, int>();
BookstoreEntities context = new BookstoreEntities();
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 (isbn != null && isbn != string.Empty)
{
long isbnNumber = long.Parse(isbn);
booksQuery =
from b in context.Books
where b.ISBN == isbnNumber
select b;
}
if (author != null)
{
booksQuery = booksQuery.Where(
b => b.Authors.Any(a => a.Name.ToLower() == author.ToLower()));
}
booksQuery = booksQuery.OrderBy(b => b.Title);
var booksData = booksQuery.Select(b => new {b.Title, b.Reviews.Count});
foreach (var book in booksData)
{
booksReviews.Add(book.Title, book.Count);
}
return booksReviews;
}
public static void ComplexBookAdd(string title, string isbn, string price, string webSiteURL, List<string> authors, List<Dictionary<string, string>> reviews)
{
BookstoreEntities context = new BookstoreEntities();
Book newBook = new Book();
foreach (var author in authors)
{
Author bookAuthor = CreateOrLoadAuthor(context, author);
newBook.Authors.Add(bookAuthor);
}
foreach (var review in reviews)
{
Review bookReview = new Review();
if (review["author"] != null && review["author"] != string.Empty)
{
Author reviewAuthor = CreateOrLoadAuthor(context, review["author"]);
bookReview.Author = reviewAuthor;
}
bookReview.Text = review["text"];
var creationDate = DateTime.Now;
if (review["creationDate"] != null && review["creationDate"] != string.Empty)
{
creationDate = DateTime.Parse(review["creationDate"]);
}
bookReview.CreationDate = creationDate;
newBook.Reviews.Add(bookReview);
}
newBook.Title = title;
newBook.WebSiteURL = webSiteURL;
if (isbn != string.Empty && isbn != null)
{
newBook.ISBN = long.Parse(isbn);
}
if (price != string.Empty && price != null)
{
newBook.Price = decimal.Parse(price);
}
context.Books.Add(newBook);
context.SaveChanges();
}
public static Book AddBook(
string title, string authorName, decimal? price, string isbn, string url)
{
BookstoreEntities context = new BookstoreEntities();
Book newBook = new Book();
newBook.Title = title;
if (authorName != null)
{
Author author = CreateOrLoadAuthor(context, authorName);
newBook.Authors.Add(author);
}
newBook.Price = price;
newBook.ISBN = isbn;
newBook.Url = url;
context.Books.Add(newBook);
context.SaveChanges();
return newBook;
}
private static Author CreateOrLoadAuthor(BookstoreEntities context, string author)
{
Author existringAuthor =
(from a in context.Authors
where a.Name == author
select a).FirstOrDefault();
if (existringAuthor != null)
{
return(existringAuthor);
}
Author newAuthor = new Author();
newAuthor.Name = author;
context.Authors.Add(newAuthor);
context.SaveChanges();
return(newAuthor);
}
