private static Author CreateOrLoadAuthor( BookstoreDbEntities context, string name) { Author existingAuthor = (from a in context.Authors where a.Name.ToLower() == name.ToLower() select a).FirstOrDefault(); if (existingAuthor != null) { return existingAuthor; } var newAuthor = new Author(); newAuthor.Name = name; context.Authors.Add(newAuthor); context.SaveChanges(); return newAuthor; }
/*Task 3: */ //Note: Please note that the connection strings must be corrected for each task. Thank you ! static void Main(string[] args) { var dbCon = new BookstoreDbEntities(); using (dbCon) { XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load("../../simple-books.xml"); string xPathQuery = "/catalog/book"; XmlNodeList books = xmlDoc.SelectNodes(xPathQuery); //Reading the XML with XPath foreach (XmlNode book in books) { var bookEntry = new Book(); bookEntry.title = book.GetChildText("title"); if (bookEntry.title == null) { throw new ArgumentException("Provided book does not have an title!"); } var authors = book.SelectNodes("author"); if (authors.Count == 0) { throw new ArgumentException("Provided book does not have an author(s)!"); } //Making sure that all the authors are processed. foreach (XmlNode author in authors) { var name = author.InnerText; bookEntry.Authors.Add(CreateOrLoadAuthor(dbCon, name)); } var price = book.GetChildText("price"); if (price != null) { bookEntry.Price = decimal.Parse(price); } else { bookEntry.Price = null; } var webSite = book.GetChildText("web-site"); if (webSite != null) { bookEntry.Website = book.GetChildText("web-site"); } var isbn = book.GetChildText("isbn"); if (isbn != null) { bookEntry.ISBN = book.GetChildText("isbn"); } dbCon.Books.Add(bookEntry); } dbCon.SaveChanges(); } }
static void Main(string[] args) { var dbCon = new BookstoreDbEntities(); /*Using the context from Task 7. If performance test must be done, please comment the task 7 part. Thank you! */ var logContext = new LogContext(); Database.SetInitializer(new MigrateDatabaseToLatestVersion <LogContext, Configuration>()); XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load("../../reviews-queries.xml"); string xPathQuery = "/review-queries/query"; XmlNodeList queries = xmlDoc.SelectNodes(xPathQuery); string fileName = "../../reviews-search-results.xml"; Encoding encoding = Encoding.GetEncoding("windows-1251"); using (XmlTextWriter writer = new XmlTextWriter(fileName, encoding)) { writer.Formatting = Formatting.Indented; writer.IndentChar = '\t'; writer.Indentation = 1; writer.WriteStartDocument(); writer.WriteStartElement("search-results"); foreach (XmlNode query in queries) { //Task 7 Call Method UpdateXMLToLogDB(logContext, query); if (query.Attributes["type"].Value == "by-period") { var startDate = DateTime.Parse(query.GetChildText("start-date")); var endDate = DateTime.Parse(query.GetChildText("end-date")); if (startDate == null || endDate == null) { throw new ArgumentException("Stard date and end date are mendatory!"); } if (startDate > endDate) { var reviews = from r in dbCon.Reviews.Include("Books") select r; reviews = reviews.Where(r => r.DateOfCreation < startDate && r.DateOfCreation > endDate) .OrderBy(r => r.DateOfCreation).ThenBy(r => r.Text); WriteResults(writer, reviews); } else { var reviews = from r in dbCon.Reviews.Include("Books") select r; reviews = reviews.Where(r => r.DateOfCreation > startDate && r.DateOfCreation < endDate) .OrderBy(r => r.DateOfCreation).ThenBy(r => r.Text); WriteResults(writer, reviews); } } else if (query.Attributes["type"].Value == "by-author") { var authorName = query.GetChildText("author-name"); if (authorName == null) { throw new ArgumentException("Author's Name is mendatory!"); } var author = CreateOrLoadAuthor(dbCon, authorName); var reviews = from r in dbCon.Reviews.Include("Books") select r; reviews = reviews.Where(r => r.AuthorId == author.Id) .OrderBy(r => r.DateOfCreation).ThenBy(r => r.Text); WriteResults(writer, reviews); } } writer.WriteEndElement(); } }
/*Task 4*/ static void Main(string[] args) { //As Nakov told us book ISBN should not be unique, thus, same books can be added. It's not a bug, its a feature :) var dbCon = new BookstoreDbEntities(); using (dbCon) { XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load("../../complex-books.xml"); string xPathQuery = "/catalog/book"; XmlNodeList books = xmlDoc.SelectNodes(xPathQuery); //Reading the XML with XPath foreach (XmlNode book in books) { //Processing each query in different transaction, so we can be sure that if one fails, the correct ones will be processed. //There should be an easier way, but for now this works as well :) var tran = new TransactionScope(); using (tran) { try { var bookEntry = new Book(); bookEntry.title = book.GetChildText("title"); if (bookEntry.title == null) { throw new ArgumentException("Provided book does not have an title!"); } var authors = book.SelectNodes("authors/author"); if (authors.Count == 0) { throw new ArgumentException("Cannot create book with missing author!"); } foreach (XmlNode author in authors) { var name = author.InnerText; bookEntry.Authors.Add(CreateOrLoadAuthor(dbCon, name)); } foreach (XmlNode review in book.SelectNodes("reviews/review")) { string authorName = null; var currentReview = new Review(); DateTime date = DateTime.Now; if (review.Attributes["author"] != null) { authorName = review.Attributes["author"].Value; var author = CreateOrLoadAuthor(dbCon, authorName); currentReview.AuthorId = author.Id; } if (review.Attributes["date"] != null) { date = DateTime.Parse(review.Attributes["date"].Value); } var text = review.InnerText; currentReview.DateOfCreation = date; currentReview.Text = text; dbCon.Reviews.Add(currentReview); dbCon.SaveChanges(); bookEntry.Reviews.Add(currentReview); } var price = book.GetChildText("price"); if (price != null) { bookEntry.Price = decimal.Parse(price); } else { bookEntry.Price = null; } var webSite = book.GetChildText("web-site"); if (webSite != null) { bookEntry.Website = book.GetChildText("web-site"); } var isbn = book.GetChildText("isbn"); if (isbn != null) { bookEntry.ISBN = book.GetChildText("isbn"); } dbCon.Books.Add(bookEntry); dbCon.SaveChanges(); } catch (Exception ex) { Console.WriteLine("Transaction failed to complete with the following error: \n" + ex.Message.ToString()); Environment.Exit(2); } tran.Complete(); } } } }
/*Task 5*/ static void Main(string[] args) { /*Using the context from Task 7*/ var logContext = new LogContext(); Database.SetInitializer(new MigrateDatabaseToLatestVersion <LogContext, Configuration>()); var dbCon = new BookstoreDbEntities(); XmlDocument xmlDoc = new XmlDocument(); xmlDoc.Load("../../simple-query.xml"); string title = null; string author = null; string isbn = null; XmlNodeList queries = xmlDoc.SelectNodes("/query"); foreach (XmlNode query in queries) { title = query.GetChildText("title"); author = query.GetChildText("author"); isbn = query.GetChildText("isbn"); //Update the query XML to the log DB. May slow things a little bit... // var log = new CustomLogs(); // log.Date = DateTime.Now; // log.QueryXML = query.OuterXml; // logContext.Logs.Add(log); // logContext.SaveChanges(); } var booksQuery = from b in dbCon.Books.Include("Authors") select b; if (title != null) { booksQuery = from b in dbCon.Books where b.title.ToLower() == title.ToLower() select b; } if (author != null) { booksQuery = booksQuery.Where( b => b.Authors.Any(t => t.Name.ToLower() == author.ToLower())); } if (isbn != null) { booksQuery = booksQuery.Where( b => b.ISBN == isbn); } booksQuery = booksQuery.OrderBy(b => b.title); if (booksQuery.Count() == 0) { Console.WriteLine("Nothing Found"); } else { foreach (var item in booksQuery) { string reviews = string.Empty; if (item.Reviews.Count() > 1) { reviews = item.Reviews.Count() + " review"; } else if (item.Reviews.Count() > 2) { reviews = item.Reviews.Count() + " reviews"; } else { reviews = " no reviews"; } Console.WriteLine(item.title + "--> " + reviews); } } }