/// <summary>
/// Query the DB for books matching the specified terms. Caller is expected to pre-validate that at least one term is populated.
/// </summary>
/// <param name="req"></param>
private void SearchForBooks(SearchRequest req)
{
var cmd = DbConnection.CreateCommand();
cmd.CommandText = "select b.ISBN, b.title, b.PublishDate, i.Barcode, i.Quantity, a.Fname, a.Minit, a.Lname " +
"from book b " +
"join inventory i on i.BookID = b.ISBN " +
"join book_author ba on ba.BookID = i.BookID " +
"join author a on a.id = ba.AuthorID " +
"where b.ISBN in ( " +
" select distinct(b.ISBN) " +
" from book b " +
" join book_author ba on ba.BookID = i.BookID " +
" join author a on a.id = ba.AuthorID " +
" where 1 = 1 ";
if (req.Title.Length > 0)
{
cmd.CommandText += " and upper(b.Title) like @title ";
cmd.Parameters.AddWithValue("title", "%" + req.Title.ToUpper() + "%");
}
if (req.Author.Length > 0)
{
cmd.CommandText += " and(upper(a.Fname) like @author or upper(a.Lname) like @author)";
cmd.Parameters.AddWithValue("author", "%" + req.Author.ToUpper() + "%");
}
//close the IN clause, set ordering
cmd.CommandText += ") order by b.Title";
Hashtable resultsHash = new Hashtable();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
//this PK will be our hash key
var ISBN = reader.GetString(0);
var item = (BookSearchResult)resultsHash[ISBN];
if (item == null)
{
item = new BookSearchResult()
{
ISBN = ISBN,
Title = reader.GetString(1),
PublishDate = reader.GetDateTime(2).ToString("MMM dd, yyyy"),
Barcode = reader.GetString(3),
Quantity = reader.GetInt32(4),
};
resultsHash[ISBN] = item;
}
item.Authors.Add(new Author()
{
Firstname = reader.GetString(5),
MiddleInitial = (reader.IsDBNull(6) ? null : reader.GetString(6)),
Lastname = reader.GetString(7)
});
}
}
foreach (var item in resultsHash.Values)
{
//this will fire the collection changed event
searchResults.Add((BookSearchResult)item);
}
}
/// <summary>
/// Checks if the user left the search inputs empty.
/// </summary>
/// <param name="req"></param>
/// <returns></returns>
private bool IsEmptySearch(SearchRequest req)
{
return(string.IsNullOrEmpty(req.Title) && string.IsNullOrEmpty(req.Author));
}
private bool IsSearchTooShort(SearchRequest req)
{
int minChars = Properties.Settings.Default.MEMBER_MIN_BOOK_SEARCH_CHARS;
return(req.Title.Length < minChars && req.Author.Length < minChars);
}