/// <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));
}