private void btn_CL_Filter_Click(object sender, EventArgs e)
{
string CL_FH_NAME = comboBox_CL_FH.Text;
string CL_DES_NAME = comboBox_CL_DES.Text;
string CL_CAT = comboBox_CL_CAT.Text;
string CL_COLOR = comboBox_CL_COLOR.Text;
int CL_FH = -1;
if (!FHexists(CL_FH_NAME) && CL_FH_NAME != String.Empty)
{
FashionHousesDataSet.ClothesDataTable cl = new FashionHousesDataSet.ClothesDataTable();
dataGridViewCL.DataSource = cl;
return;
}
string query = "SELECT * FROM Clothes";
SqlDataAdapter adapter;
DataTable table = new DataTable();
if (CL_DES_NAME != String.Empty)
{
query += " INNER JOIN (SELECT * FROM Designers WHERE Designers.DES_FULLNAME = @DES_NAME) T ON Clothes.CL_DES = T.DES_ID";
}
bool firstParameter = true;
if (CL_FH_NAME != String.Empty)
{
if (!firstParameter)
{
query += " AND";
}
else
{
query += " WHERE";
firstParameter = false;
}
CL_FH = get_FH_ID_by_FH_NAME(CL_FH_NAME);
query += " CL_FH = @CL_FH";
}
if (CL_CAT != String.Empty)
{
if (!firstParameter)
{
query += " AND";
}
else
{
query += " WHERE";
firstParameter = false;
}
query += " CL_CATEGORY = @CL_CAT";
}
if (CL_COLOR != String.Empty)
{
if (!firstParameter)
{
query += " AND";
}
else
{
query += " WHERE";
firstParameter = false;
}
query += " CL_COLOR = @CL_COLOR";
}
query += ";";
adapter = new SqlDataAdapter(query, clothesTableAdapter.Connection);
adapter.SelectCommand.Parameters.AddWithValue("@DES_NAME", CL_DES_NAME);
adapter.SelectCommand.Parameters.AddWithValue("@CL_FH", CL_FH);
adapter.SelectCommand.Parameters.AddWithValue("@CL_CAT", CL_CAT);
adapter.SelectCommand.Parameters.AddWithValue("@CL_COLOR", CL_COLOR);
adapter.Fill(table);
dataGridViewCL.DataSource = table;
}
private void btn_FILTER_Click(object sender, EventArgs e)
{
string DES_NAME = comboBox_DES_NAME.Text;
string DES_GENDER = comboBox_DES_GENDER.Text;
string DES_FH_NAME = comboBox_DES_FH.Text;
string DES_BIRTHDAY = comboBox_DES_BIRTHDAY.Text;
int DES_PASSPORT;
if (!FHexists(DES_FH_NAME) && DES_FH_NAME != String.Empty)
{
FashionHousesDataSet.ClothesDataTable cl = new FashionHousesDataSet.ClothesDataTable();
dataGridViewCL.DataSource = cl;
return;
}
if (!Int32.TryParse(comboBox_DES_PASSPORT.Text, out DES_PASSPORT) && comboBox_DES_PASSPORT.Text != String.Empty)
{
FashionHousesDataSet.ClothesDataTable cl = new FashionHousesDataSet.ClothesDataTable();
dataGridViewCL.DataSource = cl;
return;
}
string query = "SELECT * FROM Designers";
SqlDataAdapter adapter;
bool firstParameter = true;
int FH_ID = 0;
if (comboBox_DES_FH.Text != String.Empty)
{
FH_ID = get_FH_ID_by_FH_NAME(DES_FH_NAME);
if (firstParameter)
{
query += " WHERE";
firstParameter = false;
}
else
{
query += " AND";
}
query += " DES_FH = @FH_ID";
}
if (DES_NAME != String.Empty)
{
if (firstParameter)
{
query += " WHERE";
firstParameter = false;
}
else
{
query += " AND";
}
query += " DES_FULLNAME = @DES_NAME";
}
if (DES_GENDER != String.Empty)
{
if (firstParameter)
{
query += " WHERE";
firstParameter = false;
}
else
{
query += " AND";
}
query += " DES_GENDER = @DES_GENDER";
}
if (DES_BIRTHDAY != String.Empty)
{
if (firstParameter)
{
query += " WHERE";
firstParameter = false;
}
else
{
query += " AND";
}
query += " DES_BIRTHDAY = @DES_BIRTHDAY";
}
if (comboBox_DES_PASSPORT.Text != String.Empty)
{
if (firstParameter)
{
query += " WHERE";
firstParameter = false;
}
else
{
query += " AND";
}
query += " DES_PASSPORT = @DES_PASSPORT";
}
adapter = new SqlDataAdapter(query, designersTableAdapter.Connection);
adapter.SelectCommand.Parameters.AddWithValue("@FH_ID", FH_ID);
adapter.SelectCommand.Parameters.AddWithValue("@DES_NAME", DES_NAME);
adapter.SelectCommand.Parameters.AddWithValue("@DES_GENDER", DES_GENDER);
adapter.SelectCommand.Parameters.AddWithValue("@DES_BIRTHDAY", DES_BIRTHDAY);
adapter.SelectCommand.Parameters.AddWithValue("@DES_PASSPORT", DES_PASSPORT);
DataTable table = new DataTable();
adapter.Fill(table);
dataGridViewDES.DataSource = table;
}
