/// <summary>
/// load author according to user selection
/// </summary>
private void LoadAuthor()
{
string sqlAuthor = "SELECT AuthorId, FirstName + ISNULL(' '+ MiddleName, '') + ' ' + LastName AS FullName FROM Author";
if (lstCategory.SelectedIndex > 0)
{
sqlAuthor += $" WHERE MainCategory = {lstCategory.SelectedValue}";
}
sqlAuthor += " ORDER BY FirstName, MiddleName, LastName";
DataTable dtAuthor = DataAccess.GetData(sqlAuthor);
if (dtAuthor.Rows.Count > 0)
{
UT.FillListControl(cmbAuthor, "FullName", "AuthorId", dtAuthor);
numberOfAuthors = dtAuthor.Rows.Count;
LoadAuthorDetail();
}
else
{
dgvBooks.Visible = false;
lbFullName.Text = string.Empty;
lbContactNumber.Text = string.Empty;
lbAward.Text = string.Empty;
lbNumOfBook.Text = string.Empty;
cmbAuthor.DataSource = null;
((mdiForm)this.MdiParent).StatusStipLabel.Text = "No records";
MessageBox.Show("There is no author in the category");
}
}
/// <summary>
/// Load books according to user selection
/// if the user selects the filter option, filter the result based on criteria
/// </summary>
private void LoadBook()
{
string sqlLoadBook = "SELECT BookId, Title + ' - ' + Edition AS Title FROM Book WHERE 0=0";
if (lstCategory.SelectedIndex > 0)
{
sqlLoadBook += $" AND CategoryId = {lstCategory.SelectedValue}";
}
if (cmbYear.SelectedIndex > 0)
{
sqlLoadBook += $" AND (PublicateDate BETWEEN '{cmbYear.SelectedItem}-01-01' AND '{cmbYear.SelectedItem}-12-31')";
}
if (chkAvailable.Checked)
{
sqlLoadBook += $" AND Available = 1";
}
sqlLoadBook += " ORDER BY Title";
DataTable dtBook = DataAccess.GetData(sqlLoadBook);
if (dtBook.Rows.Count > 0)
{
UT.FillListControl(cmbTitle, "Title", "BookId", dtBook);
numberOfBooks = dtBook.Rows.Count;
LoadBookDetail();
}
else
{
dgvAuthors.Visible = false;
lbTitle.Text = string.Empty;
lbISBN.Text = string.Empty;
lbCategory.Text = string.Empty;
lbPublisher.Text = string.Empty;
lbPrice.Text = string.Empty;
lbNumOfAuthor.Text = string.Empty;
cmbTitle.DataSource = null;
((mdiForm)this.MdiParent).StatusStipLabel.Text = "No records";
MessageBox.Show("There is no book in the category");
}
}
/// <summary>
/// fill the category list
/// </summary>
private void LoadCategory()
{
DataTable dtCategory = DataAccess.GetData("SELECT CategoryId, CategoryName FROM Category");
UT.FillListControl(lstCategory, "CategoryName", "CategoryId", dtCategory, true, "All");
}