private PublicationDateCondition GetPublicationDateCondition()
{
if (comboBoxPublicationDate.SelectedItem == null)
{
return(null);
}
PublicationDateCondition pdc = new PublicationDateCondition
{
Condition = comboBoxPublicationDate.SelectedItem.ToString(),
Month = comboBoxMonth.SelectedIndex + 1,
Year = Convert.ToInt32(numericUpDownYear.Value.ToString())
};
return(pdc);
}
private void UpdateDataGridViewSearchResult()
{
dataGridViewSearchResult.DataSource = null;
dataGridViewSearchResult.Rows.Clear();
dataGridViewSearchResult.Columns.Clear();
dataGridViewSearchResult.AutoGenerateColumns = false;
AddColumnsToDataGridViewSearchResult();
string title = textBoxTitle.Text.Trim();
int authorID = GetSelectedAuthorID();
int publisherID = GetSelectedPublisherID();
List <int> categoriesID = GetSelectedCategoriesID();
PublicationDateCondition pdc = GetPublicationDateCondition();
List <Book> books = BookDAO.SearchBooks(title, authorID, publisherID, categoriesID, pdc);
dataGridViewSearchResult.DataSource = books;
}
private static string AddPublicationDateConditionToSearchBookQuery(string sql, PublicationDateCondition pdc)
{
if (pdc == null)
{
return(sql);
}
string condition = pdc.Condition;
string firstDayOfMonth = "'" + pdc.Year + "-" + pdc.Month + "-01'";
if (condition.Equals("During"))
{
string lastDayOfMonth = "EOMONTH(" + firstDayOfMonth + ")";
sql += "and [PublicationDate] between " + firstDayOfMonth + " and " + lastDayOfMonth;
}
else if (condition.Equals("Before"))
{
sql += "and [PublicationDate] < " + firstDayOfMonth;
}
else if (condition.Equals("After"))
{
sql += "and [PublicationDate] >= " + firstDayOfMonth;
}
return(sql += "\n");
}
public static List <Book> SearchBooks(string title, int authorID, int publisherID, List <int> categoriesID, PublicationDateCondition pdc)
{
List <Book> books = new List <Book>(0);
string sql = "select [BookID],[Title],\n" +
"([FirstName] + ' ' + [LastName]) AS [AuthorName],\n" +
"[PublisherName],[PublicationDate],[UnitPrice],[UnitsInStock] from [Books]\n" +
"inner join [Authors] on [Books].[AuthorID]=[Authors].[AuthorID]\n" +
"inner join [Publishers] on [Books].[PublisherID]=[Publishers].[PublisherID]\n" +
"where [Books].[Title] like '%' + @title + '%'\n";
sql = AddAuthorConditionToSearchBookQuery(sql, authorID);
sql = AddPublisherConditionToSearchBookQuery(sql, publisherID);
sql = AddCategoryConditionToSearchBookQuery(sql, categoriesID);
sql = AddPublicationDateConditionToSearchBookQuery(sql, pdc);
SqlParameter param1 = new SqlParameter("@title", SqlDbType.NVarChar)
{
Value = title
};
SqlParameter param2 = new SqlParameter("@authorID", SqlDbType.Int)
{
Value = authorID
};
SqlParameter param3 = new SqlParameter("@publisherID", SqlDbType.Int)
{
Value = publisherID
};
SqlParameter param4 = new SqlParameter("@numCategory", SqlDbType.Int)
{
Value = categoriesID.Count
};
DataTable dataTable = ExecuteReader(sql, param1, param2, param3, param4);
foreach (DataRow dataRow in dataTable.Rows)
{
Book book = new Book()
{
BookID = Convert.ToInt32(dataRow["BookID"].ToString()),
Title = dataRow["Title"].ToString(),
AuthorName = dataRow["AuthorName"].ToString(),
PublisherName = dataRow["PublisherName"].ToString(),
PublicationDate = Convert.ToDateTime(dataRow["PublicationDate"].ToString()),
UnitPrice = Convert.ToDecimal(dataRow["UnitPrice"].ToString()),
UnitsInStock = Convert.ToInt32(dataRow["UnitsInStock"].ToString())
};
books.Add(book);
}
return(books);
}