private static void BooksTitlesByAgeRestriction(BookShopSystemContext ctx) { var input = Console.ReadLine().ToLower(); if (input == "minor") { var ageRestriction = Book.AgeRestrictions.Minor; var books = ctx.Books.Where(x => x.AgeRestriction == ageRestriction).ToList(); foreach (var item in books) { Console.WriteLine($"{item.Title}"); } } else if (input == "teen") { var ageRestriction = Book.AgeRestrictions.Teen; var books = ctx.Books.Where(x => x.AgeRestriction == ageRestriction).ToList(); foreach (var item in books) { Console.WriteLine($"{item.Title}"); } } else { var ageRestriction = Book.AgeRestrictions.Adult; var books = ctx.Books.Where(x => x.AgeRestriction == ageRestriction).ToList(); foreach (var item in books) { Console.WriteLine($"{item.Title}"); } } }
private static void StoredProcedure(BookShopSystemContext context) { Console.WriteLine($"-------======= [16. Stored Procedure] =======-------"); Console.WriteLine("Please enter first and last name of the author (ex: Wanda Morales):"); string[] name = Console.ReadLine().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToArray(); //CREATE PROCEDURE GetAuthorBooksCount @firstName NVARCHAR(MAX), //@lastName NVARCHAR(MAX), //@result INT OUT //AS //SET @result = ( //SELECT COUNT(*) AS COUNT FROM Authors AS a //inner join Books AS b //ON b.Author_Id = a.Id //WHERE a.FirstName = @firstName and a.LastName = @lastName) SqlParameter firstName = new SqlParameter("@firstName", name[0]); SqlParameter secondName = new SqlParameter("@lastName", name[1]); SqlParameter result = new SqlParameter(); result.ParameterName = "@result"; result.Direction = ParameterDirection.Output; result.SqlDbType = SqlDbType.Int; context.Database.ExecuteSqlCommand($"execute GetAuthorBooksCount @firstName, @lastName ,@result output", result, firstName, secondName); Console.WriteLine($"{firstName.Value} {secondName.Value} has written {result.Value} books"); }
private static void RemoveBooks(BookShopSystemContext context) { Console.WriteLine($"-------======= [15. Remove Book] =======-------"); const int count = 4200; var books = context.Books.Where(b => b.Copies < count); int counteDeletedBooks = 0; foreach (var book in books) { try { context.Books.Remove(book); counteDeletedBooks++; } catch (Exception) { Console.WriteLine($"Cannot delete book: {book.Title}"); } } try { context.SaveChanges(); Console.WriteLine($"{counteDeletedBooks} books were deleted"); } catch (Exception e) { Console.WriteLine(e.Message); throw; } }
private static void BooksTitleWithSearch(BookShopSystemContext ctx) { var input = Console.ReadLine().ToLower(); var result = ctx.Books.Where(b => b.Title.Contains(input)).ToList(); foreach (var b in result) { Console.WriteLine($"{b.Title}"); } }
private static void NotReleasedBooks(BookShopSystemContext ctx) { var input = int.Parse(Console.ReadLine()); var result = ctx.Books.Where(y => y.ReleaseData.Year != input).OrderBy(b => b.Id).ToList(); foreach (var book in result) { Console.WriteLine($"{book.Title}"); } }
private static void TotalBookCopies(BookShopSystemContext ctx) { var result = ctx.Authors .OrderByDescending(a => a.Books) .ToList(); foreach (var a in result) { Console.WriteLine($"{a.FirstName} {a.LastName} - {a.Books.ToList().Sum(x => x.Copies)}"); } }
private static void AuthorWthNameStartsWith(BookShopSystemContext ctx) { var input = Console.ReadLine().ToLower(); var result = ctx.Authors.Where(b => b.FirstName.EndsWith(input)).ToList(); foreach (var a in result) { Console.WriteLine($"{a.FirstName} {a.LastName}"); } }
static void Main() { var ctx = new BookShopSystemContext(); ctx.Database.Initialize(true); //Adding relating books AddRelatedBooks(ctx); //Show top 3 book relations BookRelation(ctx); }
static void Main() { var ctx = new BookShopSystemContext(); ctx.Database.Initialize(true); //1.Books Titles by Age Restriction //BooksTitlesByAgeRestriction(ctx); //2.Golden Books //GoldenBooks(ctx); //3.Books by Price //BookByPrice(ctx); //4.Not Released Books //NotReleasedBooks(ctx); //5.Book Titles by Category //BookTitlesByCategory(ctx); //6.Books Released Before Date //BooksReleasedBeforeDate(ctx); //7.Authors Search //AuthorWthNameStartsWith(ctx); //8.Books Search //BooksTitleWithSearch(ctx); //10.Count Books //CountBooks(ctx); //11.Total Book Copies //TotalBookCopies(ctx); //12.Find Profit //FindProfit(ctx); //13.Most Recent Books //MostRecentBooks(ctx); //14.Increase Book Copies //IncreaseBookCopies(ctx); //15.Remove Books //RemoveBooks(ctx); //16.Stored Procedure //StoredProcedure(ctx); //HAVE PROBLEM WITH OTHER DATABASE.... To Be Continue... }
private static void BookRelation(BookShopSystemContext ctx) { var booksFromQuery = ctx.Books.Take(3).ToList(); foreach (var book in booksFromQuery) { Console.WriteLine("--{0}", book.Title); foreach (var relatedBook in book.RelatedBooks) { Console.WriteLine(relatedBook.Title); } } }
private static void IncreaseBookCopies(BookShopSystemContext context) { Console.WriteLine($"-------======= [14. Increase Book Copies] =======-------"); const int count = 44; var books = context.Books.Where(b => b.ReleaseData > new DateTime(2013, 06, 06)); foreach (var book in books) { book.Copies += count; } Console.WriteLine($"{books.Count()} books are released after 6 Jun 2013 so total of {books.Count() * count} book copies were added"); }
private static void BookByPrice(BookShopSystemContext ctx) { var result = ctx.Books .Where(p => p.Price <= 5 || p.Price >= 40) .OrderBy(b => b.Id) .Select(b => new { b.Title, b.Price }) .ToList(); foreach (var book in result) { Console.WriteLine($"{book.Title} - ${book.Price}"); } }
private static void AddRelatedBooks(BookShopSystemContext ctx) { var books = ctx.Books .Take(3) .ToList(); books[0].RelatedBooks.Add(books[1]); books[1].RelatedBooks.Add(books[0]); books[0].RelatedBooks.Add(books[2]); books[2].RelatedBooks.Add(books[0]); ctx.SaveChanges(); }
private static void MostRecentBooks(BookShopSystemContext context) { Console.WriteLine($"-------======= [13. Most Recent Books] =======-------"); foreach (var category in context.Categories.Where(c => c.Books.Count > 35).OrderByDescending(c => c.Books.Count)) { Console.WriteLine($"--{category.Name}: {category.Books.Count} books"); foreach (var book in context.Books.OrderByDescending(b => b.ReleaseData).ThenBy(b => b.Title).Take(3)) { Console.WriteLine($"{book.Title} ({book.ReleaseDate.Value.Year})"); } } }
private static void BookTitlesByCategory(BookShopSystemContext ctx) { string input = Console.ReadLine().ToLower(); string[] categories = input.Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries).ToArray(); foreach (var book in ctx.Books) { if (book.Categories.Any(c => categories.Contains(c.Name))) { Console.WriteLine(book.Title); } } }
private static void BooksReleasedBeforeDate(BookShopSystemContext ctx) { DateTime date; DateTime.TryParseExact(Console.ReadLine(), "dd-MM-yyyy", null, System.Globalization.DateTimeStyles.None, out date); var result = ctx.Books.Where(b => b.ReleaseData < date).ToList(); foreach (var book in result) { Console.WriteLine($"{book.Title} {book.Type} {book.Price}"); } }
private static void GoldenBooks(BookShopSystemContext ctx) { var type = Book.EditionType.Gold; var result = ctx.Books .Where(c => c.Type == type) .Where(c => c.Copies < 5000) .OrderBy(b => b.Id) .ToList(); foreach (var cop in result) { Console.WriteLine($"{cop.Title}"); } }
private static void FindProfit(BookShopSystemContext ctx) { Dictionary <string, decimal> result = new Dictionary <string, decimal>(); foreach (var category in ctx.Categories) { result[category.Name] = 0; foreach (var book in ctx.Books) { if (book.Categories.Contains(category)) { result[category.Name] += book.Copies * book.Price; } } } Console.WriteLine(string.Join(Environment.NewLine, result .OrderByDescending(x => x.Value) .ThenBy(x => x.Key) .Select(kv => $"{kv.Key} - ${kv.Value}"))); }
private static void CountBooks(BookShopSystemContext ctx) { int input = int.Parse(Console.ReadLine()); Console.WriteLine(ctx.Books.Count(x => x.Title.Length > input)); }