/// <summary> /// Generate a list of BookData objects, where each of them contains information about a book /// </summary> public static List <BookData> List() { var bookData = new List <BookData>(); const string sql = "SELECT * FROM books"; var query = Db.Query(sql); while (query.Read()) { if (!File.Exists(BookKeeper.GetAbsoluteBookFilePath(query["Path"].ToString()))) { continue; } bookData.Add(BookKeeper.CastSqlBookRowToBookData(query)); } query.Dispose(); return(bookData); }
private void BookGrid_OnLoaded(object sender, RoutedEventArgs e) { DebugConsole.WriteLine("Loading the book grid..."); var gridLoadStopwatch = new Stopwatch(); gridLoadStopwatch.Start(); var sqlCategoriesQueryStopwatch = new Stopwatch(); var sqlbooksQueryStopwatch = new Stopwatch(); var grid = (DataGrid)sender; var sql = AssembleQuery(); var categoryList = new Dictionary <string, List <string> >(); var bookDataList = new List <BookData>(); var bookList = new List <Book>(); var cultureList = CultureInfo.GetCultures(CultureTypes.SpecificCultures); sqlbooksQueryStopwatch.Start(); //Turn rows form the db into BookData objects using (var query = Db.Query(sql.Item1, sql.Item2)) { sqlbooksQueryStopwatch.Stop(); sqlCategoriesQueryStopwatch.Start(); //Add list of attached categories to each BookData object using (var queryAllCategories = Db.Query("SELECT Path, Name FROM categories")) //Pull all categories from database, so that we don't have to do a separate query for each book { sqlCategoriesQueryStopwatch.Stop(); //Turn category rows into a single dictionary, where each key is a Path, whose value is a List of category Names associated with said Path while (queryAllCategories.Read()) { var key = queryAllCategories["Path"].ToString(); var value = queryAllCategories["Name"].ToString(); if (categoryList.ContainsKey(key)) { categoryList[key].Add(value); } else { categoryList.Add(key, new List <string> { value }); } } } //Cast book rows into BookData objects and assign them their category lists while (query.Read()) { var bookData = BookKeeper.CastSqlBookRowToBookData(query); if (categoryList.ContainsKey(query["Path"].ToString())) { bookData.Categories = categoryList[query["Path"].ToString()]; } bookDataList.Add(bookData); } } foreach (var bookData in bookDataList) { var countryCode = "_unknown"; if (bookData.Language != "" && cultureList.FirstOrDefault(x => x.Name == bookData.Language) != null) //Make sure the book language is not neutral (ex: en instead of en-US), or invalid. This will make sure we don't display for example US flag for british english. { countryCode = new RegionInfo((new CultureInfo(bookData.Language)).Name).TwoLetterISORegionName; } bookList.Add(new Book { Title = bookData.Title, Author = bookData.Author, Publisher = bookData.Publisher, CountryCode = countryCode, Published = bookData.Published, Description = bookData.Description, Series = bookData.Series, Categories = bookData.Categories, Size = Tools.BytesFormat(bookData.Size), Favorite = bookData.Favorite, Sync = bookData.Sync, Cover = bookData.Cover, BookFile = bookData.Path, }); } grid.ItemsSource = bookList.OrderBy(x => x.Title); //By default, the list is sorted by book titles. Note: this is much faster than ORDER BY Title directly in the sqlite query gridLoadStopwatch.Stop(); DebugConsole.WriteLine(String.Format("Book grid loaded. [Book count: {0}], [Grid load: {1}], [Categories query: {2}], [Books query: {3}]", bookList.Count, gridLoadStopwatch.Elapsed, sqlCategoriesQueryStopwatch.Elapsed, sqlbooksQueryStopwatch.Elapsed)); }