public static Author GetOrCreateAuthor(BookstoreEntities entities, string authorName)
{
var author = entities.Authors.FirstOrDefault(a => a.Name == authorName);
if (author == null)
{
author = new Author { Name = authorName };
entities.Authors.Add(author);
}
return author;
}
public static Review CreateReview(BookstoreEntities entities, string text, string dateStr, string authorName)
{
var review = new Review() { Date = DateTime.Now, Content = text };
if (authorName != null)
{
var author = entities.Authors.FirstOrDefault(a => a.Name == authorName);
review.Author = author;
}
if (dateStr != null)
{
var date = DateTime.Parse(dateStr);
review.Date = date;
}
entities.Reviews.Add(review);
return review;
}
public static Author GetAuthor(BookstoreEntities bookstoreContext, XPathNavigator node)
{
string authorName = Utils.GetNodeValue(node);
var author = bookstoreContext.Authors.FirstOrDefault(a => a.AuthorName == authorName);
if (author == null)
{
author = new Author
{
AuthorName = authorName
};
bookstoreContext.Authors.Add(author);
bookstoreContext.SaveChanges();
}
return(author);
}
private static Author CreateOrLoadAuthor(BookstoreEntities dbContext, string authorName)
{
Author existingAuthor = dbContext.Authors.Where(a => a.Name == authorName.ToLower()).FirstOrDefault();
if (existingAuthor != null)
{
return(existingAuthor);
}
else
{
Author newAuthor = new Author()
{
Name = authorName.ToLower()
};
dbContext.Authors.Add(newAuthor);
dbContext.SaveChanges();
return(newAuthor);
}
}
public static void ImportBooksAndAuthors(string bookTitle, string isbn,
string author, decimal?price, string webSite)
{
using (BookstoreEntities dbContext = new BookstoreEntities())
{
Book newBook = new Book()
{
Title = bookTitle.ToLower(),
ISBN = isbn,
Price = price,
website = webSite,
};
Author bookAutor = CreateOrLoadAuthor(dbContext, author);
newBook.Authors.Add(bookAutor);
dbContext.Books.Add(newBook);
dbContext.SaveChanges();
}
}
public static void SearchForReviews(XmlWriter xmlWriter, string authorName)
{
using (BookstoreEntities dbContext = new BookstoreEntities())
{
var reviewsFound = dbContext.Reviews.Include("Book").Include("Author")
.Where(r => r.Author.Name == authorName.ToLower())
.OrderBy(r => r.CreationDate).ToList().OrderBy(r => r.Content);
foreach (Review review in reviewsFound)
{
List <string> authors = review.Book.Authors.Select(a => a.Name).ToList();
authors.Sort();
string authorsStr = string.Join(", ", authors);
XmlManager.WriteFoundReview(xmlWriter, review.CreationDate, review.Content, review.Book.Title,
authorsStr, review.Book.ISBN, review.Book.website);
}
}
}
public static ICollection <Tuple <string, int> > SearchForBooks(string title, string author, string isbn)
{
if (title != null || author != null || isbn != null)
{
IQueryable <Book> queryResult;
using (BookstoreEntities dbContext = new BookstoreEntities())
{
queryResult = dbContext.Books;
if (title != null)
{
queryResult = queryResult.Where(b => b.Title == title.ToLower());
}
if (author != null)
{
queryResult = queryResult.Where(b => b.Authors.Any(a => a.Name == author.ToLower()));
}
if (isbn != null)
{
queryResult = queryResult.Where(b => b.ISBN == isbn);
}
queryResult.OrderBy(b => b.Title);
ICollection <Tuple <string, int> > result = new List <Tuple <string, int> >();
foreach (Book book in queryResult)
{
result.Add(new Tuple <string, int>(book.Title, book.Reviews.Count));
}
return(result);
}
}
else
{
return(new List <Tuple <string, int> >());
}
}
public static void ImportBooksAuthorsAndReviews(string bookTitle, string isbn,
decimal?price, string webSite, List <string> authorsNames, List <ReviewData> reviewsData)
{
using (BookstoreEntities dbContext = new BookstoreEntities())
{
Book newBook = new Book
{
Title = bookTitle.ToLower(),
ISBN = isbn,
Price = price,
website = webSite,
};
foreach (string authorName in authorsNames)
{
Author bookAutor = CreateOrLoadAuthor(dbContext, authorName);
newBook.Authors.Add(bookAutor);
}
foreach (ReviewData reviewData in reviewsData)
{
Review newReview = new Review()
{
Content = reviewData.Content,
CreationDate = reviewData.Date,
};
if (reviewData.AuthorName != null)
{
newReview.Author = CreateOrLoadAuthor(dbContext, reviewData.AuthorName);
}
newBook.Reviews.Add(newReview);
}
dbContext.Books.Add(newBook);
dbContext.SaveChanges();
}
}
public static void ExecureQuery(string filePath)
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(filePath);
string pathQuery = "/query";
XmlNode xmlQuery = xmlDocument.SelectSingleNode(pathQuery);
string title = xmlQuery.GetInnerText("title");
string author = xmlQuery.GetInnerText("author");
string isbn = xmlQuery.GetInnerText("isbn");
BookstoreEntities context = new BookstoreEntities();
var booksQuery =
from b in context.Books
select b;
if (title != null)
{
booksQuery = context.Books.Where(x => x.Title == title);
}
if (author != null)
{
booksQuery = booksQuery.Where(x => x.Authors.Any(y => y.Name.ToLower() == author.ToLower()));
}
if (isbn != null)
{
booksQuery = booksQuery.Where(x => x.Isbn == isbn);
}
booksQuery = booksQuery.OrderBy(x => x.Title);
PrintBooksOnConsole(booksQuery.ToList());
}
public static Review CreateReview(BookstoreEntities entities, string text, string dateStr, string authorName)
{
var review = new Review()
{
Date = DateTime.Now, Content = text
};
if (authorName != null)
{
var author = entities.Authors.FirstOrDefault(a => a.Name == authorName);
review.Author = author;
}
if (dateStr != null)
{
var date = DateTime.Parse(dateStr);
review.Date = date;
}
entities.Reviews.Add(review);
return(review);
}
public static Review CreateReview(BookstoreEntities entities, string text)
{
return CreateReview(entities, text, null, null);
}
public Import(BookstoreEntities entities)
{
Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.InvariantCulture;
this.entities = entities;
}
public XmlImporter(BookstoreEntities dbContext)
{
this.dbContext = dbContext;
}
public static void ExecureQuery(string filePath)
{
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.Load(filePath);
string pathQuery = "/review-queries/query";
XmlNodeList xmlQuery = xmlDocument.SelectNodes(pathQuery);
string fileName = "../../search-results.xml";
using (var writer = new XmlTextWriter(fileName, Encoding.UTF8))
{
writer.Formatting = Formatting.Indented;
writer.IndentChar = '\t';
writer.Indentation = 1;
writer.WriteStartDocument();
writer.WriteStartElement("search-results");
writer.WriteStartElement("result-set");
BookstoreEntities context = new BookstoreEntities();
foreach (XmlNode query in xmlQuery)
{
if (query.Attributes["type"].Value == "by-period")
{
string startDateStr = query["start-date"].InnerText;
string endDateStr = query["end-date"].InnerText;
DateTime startDate = DateTime.ParseExact(startDateStr, "d-MMM-yyyy", CultureInfo.InvariantCulture);
DateTime endDate = DateTime.ParseExact(endDateStr, "d-MMM-yyyy", CultureInfo.InvariantCulture);
var searchQuery =
from r in context.Reviews
where r.CreationDate >= startDate && r.CreationDate <= endDate
select r;
searchQuery = searchQuery.OrderByDescending(x => x.CreationDate);
searchQuery = searchQuery.OrderByDescending(x => x.ReviewText);
WriteBookmarks(writer, searchQuery.ToList());
}
else
{
string authorName = query["author-name"].InnerText;
var searchQuery =
from r in context.Reviews
join a in context.Authors on r.AuthorId equals a.AuthorId
where a.Name == authorName
select r;
searchQuery = searchQuery.OrderByDescending(x => x.CreationDate);
searchQuery = searchQuery.OrderByDescending(x => x.ReviewText);
WriteBookmarks(writer, searchQuery.ToList());
}
//Create log
var logsContext = new LogsContext();
Log log = new Log
{
Date = DateTime.Now,
QueryXml = CreateQueryXml(query)
};
logsContext.Logs.Add(log);
logsContext.SaveChanges();
}
writer.WriteEndElement();
writer.WriteEndDocument();
}
}
private static void ParseAndSaveBook(BookstoreEntities bookstoreContext, XPathNodeIterator iterator)
{
using (var scope = new TransactionScope())
{
XPathNavigator currentNode = iterator.Current;
XPathNavigator bookTitleNode = currentNode.SelectSingleNode("title");
XPathNavigator bookAuthorsNode = currentNode.SelectSingleNode("authors");
XPathNavigator bookWebSiteNode = currentNode.SelectSingleNode("web-site");
XPathNavigator bookReviewsNode = currentNode.SelectSingleNode("reviews");
XPathNavigator bookIsbnNode = currentNode.SelectSingleNode("isbn");
XPathNavigator bookPriceNode = currentNode.SelectSingleNode("price");
string bookTitle = Utils.GetNodeValue(bookTitleNode);
if (bookTitle == null)
{
throw new XPathException("Book title is a required tag.");
}
var book = new Book();
book.BookTitle = bookTitle;
if (bookAuthorsNode != null)
{
var authorsIterator = bookAuthorsNode.SelectChildren(XPathNodeType.Element);
while (authorsIterator.MoveNext())
{
XPathNavigator authorNode = authorsIterator.Current;
var author = Utils.GetAuthor(bookstoreContext, authorNode);
book.Authors.Add(author);
}
}
string bookWebSite = Utils.GetNodeValue(bookWebSiteNode);
book.BookWebSite = bookWebSite;
string bookIsbn = Utils.GetNodeValue(bookIsbnNode);
book.BookISBN = bookIsbn;
string bookPriceAsString = Utils.GetNodeValue(bookPriceNode);
decimal?bookPrice = null;
if (bookPriceAsString != null)
{
bookPrice = decimal.Parse(bookPriceAsString);
}
book.BookPrice = bookPrice;
bookstoreContext.Books.Add(book);
bookstoreContext.SaveChanges();
if (bookReviewsNode != null)
{
var reviewsIterator = bookReviewsNode.SelectChildren(XPathNodeType.Element);
while (reviewsIterator.MoveNext())
{
var bookReview = new BookReview();
XPathNavigator reviewNode = reviewsIterator.Current;
string reviewContents = Utils.GetNodeValue(reviewNode);
bookReview.BookReviewContents = reviewContents;
var reviewAuthorNode = reviewNode.SelectSingleNode("@author");
if (reviewAuthorNode != null)
{
var author = Utils.GetAuthor(bookstoreContext, reviewAuthorNode);
bookReview.Author = author;
}
var reviewDateNode = reviewNode.SelectSingleNode("@date");
DateTime bookReviewDate = DateTime.Now;
if (reviewDateNode != null)
{
bookReviewDate = Utils.GetDate(reviewDateNode);
}
bookReview.BookReviewDate = bookReviewDate;
bookReview.Book = book;
bookstoreContext.BookReviews.Add(bookReview);
}
bookstoreContext.SaveChanges();
}
scope.Complete();
}
}
public DbBaseOperations()
{
dbContext = new BookstoreEntities();
logContext = new LogsContext();
}
private static void Main()
{
string bookTitle = null;
string bookAuthor = null;
string bookIsbn = null;
using (XmlTextReader reader = new XmlTextReader("../../Resources/test1.xml"))
{
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
switch (reader.Name)
{
case "title":
{
bookTitle = reader.ReadElementString();
break;
}
case "author":
{
bookAuthor = reader.ReadElementString();
break;
}
case "isbn":
{
bookIsbn = reader.ReadElementString();
break;
}
default:
break;
}
}
}
}
using (var bookstoreContext = new BookstoreEntities())
{
var booksList =
(from book in bookstoreContext.Books.Include(b => b.Authors)
where (bookTitle == null || string.Compare(book.BookTitle, bookTitle, true) == 0) &&
(bookAuthor == null || book.Authors.Any(a => string.Compare(a.AuthorName, bookAuthor, true) == 0)) &&
(bookIsbn == null || string.Compare(book.BookISBN, bookIsbn, true) == 0)
orderby book.BookTitle
select new
{
BookTitle = book.BookTitle,
ReviewsCount = book.BookReviews.Count()
}).ToList();
int booksFound = booksList.Count;
if (booksFound == 0)
{
Console.WriteLine("Nothing found");
}
else
{
Console.WriteLine("{0} book(s) found:", booksFound);
foreach (var book in booksList)
{
Console.WriteLine(
"{0} --> {1} reviews",
book.BookTitle,
book.ReviewsCount > 0 ? book.ReviewsCount.ToString() : "no");
}
}
}
}
public static Review CreateReview(BookstoreEntities entities, string text)
{
return(CreateReview(entities, text, null, null));
}
