public static IList<Book> GetBooksList(BookFilter filter, SQLiteConnection connection)
{
List<Book> list = new List<Book>();
using (SQLiteCommand mycommand = new SQLiteCommand(connection))
{
mycommand.CommandText =
@"SELECT c.name as CategoryName, b.id as Id, b.category_id as CategoryId, b.title as Title, b.author as Author, b.isbn as isbn, b.additionalInfoLine1 as additionalInfoLine1,b.additionalInfoLine2 as additionalInfoLine2, b.entryDate as entryDate, b.photo as cover from book b
Left outer join category c on c.id=b.category_id
where b.category_id=@category_id";
if (filter.HasTextFilter)
{
mycommand.CommandText += " and (";
List<string> searchList = new List<string>();
if (!String.IsNullOrWhiteSpace(filter.Author))
searchList.Add(" upper(b.author) like upper(@author)");
if (!String.IsNullOrWhiteSpace(filter.Title))
searchList.Add(" upper(b.title) like upper(@title) ");
if (!String.IsNullOrWhiteSpace(filter.AdditionalInfo))
searchList.Add(" upper(b.additionalInfoLine1) like upper(@additionalInfo) ");
if (!String.IsNullOrWhiteSpace(filter.AdditionalInfo))
searchList.Add(" upper(b.additionalInfoLine2) like upper(@additionalInfo) ");
if (!String.IsNullOrWhiteSpace(filter.ISBN))
searchList.Add(" upper(b.isbn) like upper(@isbn) ");
mycommand.CommandText += string.Join("or", searchList) + ")";
}
mycommand.Parameters.AddWithValue("@category_id", filter.RootCategoryId);
if (!String.IsNullOrWhiteSpace(filter.Author))
mycommand.Parameters.AddWithValue("@author", "%" + filter.Author + "%");
if (!String.IsNullOrWhiteSpace(filter.Title))
mycommand.Parameters.AddWithValue("@title", "%" + filter.Title + "%");
if (!String.IsNullOrWhiteSpace(filter.AdditionalInfo))
mycommand.Parameters.AddWithValue("@additionalInfo", "%" + filter.AdditionalInfo + "%");
if (!String.IsNullOrWhiteSpace(filter.ISBN))
mycommand.Parameters.AddWithValue("@isbn", "%" + filter.ISBN + "%");
IDataReader reader = mycommand.ExecuteReader();
DataTable table = new DataTable();
table.Load(reader);
if (table.Rows.Count > 0)
{
list.AddRange(from DataRow row in table.Rows select CreateBookRecord(row));
}
reader.Close();
}
foreach (Category subCategory in GetSubCategories(filter.RootCategoryId, connection).ToList())
{
BookFilter newFilter = new BookFilter(filter) { RootCategoryId = subCategory.Id };
list.AddRange(GetBooksList(newFilter, connection));
}
return list;
}
public static IList<Book> GetBooksList(long selectedCategoryId)
{
SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
connection.Open();
BookFilter filter = new BookFilter { RootCategoryId = selectedCategoryId };
IList<Book> list = GetBooksList(filter, connection);
connection.Close();
return list;
}
public static IList<Book> GetBooksList(BookFilter filter)
{
SQLiteConnection connection = new SQLiteConnection(String.Format(connectionString, Config.DatabaseName));
connection.Open();
IList<Book> list = GetBooksList(filter, connection);
connection.Close();
return list;
}
private void RefreshBooksList(BookFilter filter)
{
uxBooksList.ItemsSource = BooksManager.BooksManager.GetBooksList(filter);
}