public static async void SearchOrd(TextBox tb, ListView lv)
{
ConnectToDB connect = new ConnectToDB();
await connect.ConnectDB();
if (!string.IsNullOrEmpty(tb.Text) && !string.IsNullOrWhiteSpace(tb.Text))
{
if (tb.Text == "Search...")
{
MessageBox.Show(
"Field must be filled!!!",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else
{
SqlDataAdapter daProd = new SqlDataAdapter();
DataTable dtProd = new DataTable();
SqlDataAdapter daEmp = new SqlDataAdapter();
DataTable dtEmp = new DataTable();
SqlDataAdapter daCust = new SqlDataAdapter();
DataTable dtCust = new DataTable();
SqlDataAdapter daDate = new SqlDataAdapter();
DataTable dtDate = new DataTable();
try
{
SqlCommand command1 = new SqlCommand("Select * FROM Orders " +
"WHERE IdProd LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daProd = new SqlDataAdapter
{
SelectCommand = command1
};
dtProd = new DataTable();
daProd.Fill(dtProd);
SqlCommand command2 = new SqlCommand("Select * FROM Orders " +
"WHERE IdEmpl LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daEmp = new SqlDataAdapter
{
SelectCommand = command2
};
dtEmp = new DataTable();
daEmp.Fill(dtEmp);
SqlCommand command3 = new SqlCommand("Select * FROM Orders " +
"WHERE IdCust LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daCust = new SqlDataAdapter
{
SelectCommand = command3
};
dtCust = new DataTable();
daCust.Fill(dtCust);
SqlCommand command4 = new SqlCommand("Select * FROM Orders " +
"WHERE DateOrder LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daDate = new SqlDataAdapter
{
SelectCommand = command4
};
dtDate = new DataTable();
daDate.Fill(dtDate);
lv.Items.Clear();
foreach (DataRow r in dtProd.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <int>(2).ToString());
list.SubItems.Add(r.Field <int>(3).ToString());
list.SubItems.Add(r.Field <string>(4));
}
foreach (DataRow r in dtEmp.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <int>(2).ToString());
list.SubItems.Add(r.Field <int>(3).ToString());
list.SubItems.Add(r.Field <string>(4));
}
foreach (DataRow r in dtCust.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <int>(2).ToString());
list.SubItems.Add(r.Field <int>(3).ToString());
list.SubItems.Add(r.Field <string>(4));
}
foreach (DataRow r in dtDate.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <int>(2).ToString());
list.SubItems.Add(r.Field <int>(3).ToString());
list.SubItems.Add(r.Field <string>(4));
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
daProd.Dispose();
daEmp.Dispose();
daCust.Dispose();
daDate.Dispose();
}
}
else
{
MessageBox.Show(
"Field must be filled!!!",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
public static async void SearchEmpl(TextBox tb, ListView lv)
{
ConnectToDB connect = new ConnectToDB();
await connect.ConnectDB();
if (!string.IsNullOrEmpty(tb.Text) && !string.IsNullOrWhiteSpace(tb.Text))
{
if (tb.Text == "Search...")
{
MessageBox.Show(
"Field must be filled!!!",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else
{
SqlDataAdapter daName = new SqlDataAdapter();
DataTable dtName = new DataTable();
SqlDataAdapter daPos = new SqlDataAdapter();
DataTable dtPos = new DataTable();
SqlDataAdapter daPhoneNumber = new SqlDataAdapter();
DataTable dtPhoneNumber = new DataTable();
SqlDataAdapter daBirthday = new SqlDataAdapter();
DataTable dtBirthday = new DataTable();
try
{
SqlCommand command1 = new SqlCommand("Select * FROM Employees " +
"WHERE FullNameEmp LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daName = new SqlDataAdapter
{
SelectCommand = command1
};
dtName = new DataTable();
daName.Fill(dtName);
SqlCommand command2 = new SqlCommand("Select * FROM Employees " +
"WHERE Birthday LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daBirthday = new SqlDataAdapter
{
SelectCommand = command2
};
dtBirthday = new DataTable();
daBirthday.Fill(dtBirthday);
SqlCommand command3 = new SqlCommand("Select * FROM Employees " +
"WHERE Position LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daPos = new SqlDataAdapter
{
SelectCommand = command3
};
dtPos = new DataTable();
daPos.Fill(dtPos);
SqlCommand command4 = new SqlCommand("Select * FROM Employees " +
"WHERE PhoneNumberEmp LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daPhoneNumber = new SqlDataAdapter
{
SelectCommand = command4
};
dtPhoneNumber = new DataTable();
daPhoneNumber.Fill(dtPhoneNumber);
lv.Items.Clear();
foreach (DataRow r in dtName.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <string>(1));
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <string>(4));
}
foreach (DataRow r in dtPos.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <string>(1));
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <string>(4));
}
foreach (DataRow r in dtPhoneNumber.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <string>(1));
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <string>(4));
}
foreach (DataRow r in dtBirthday.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <string>(1));
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <string>(4));
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
daName.Dispose();
daPos.Dispose();
daPhoneNumber.Dispose();
}
}
else
{
MessageBox.Show(
"Field must be filled!!!",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
public static async void SearchProd(TextBox tb, ListView lv)
{
ConnectToDB connect = new ConnectToDB();
await connect.ConnectDB();
if (!string.IsNullOrEmpty(tb.Text) && !string.IsNullOrWhiteSpace(tb.Text))
{
if (tb.Text == "Search...")
{
MessageBox.Show(
"Field must be filled!!!",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else
{
SqlDataAdapter daIdSuppl = new SqlDataAdapter();
DataTable dtIdSuppl = new DataTable();
SqlDataAdapter daNameProd = new SqlDataAdapter();
DataTable dtNameProd = new DataTable();
SqlDataAdapter daType = new SqlDataAdapter();
DataTable dtType = new DataTable();
SqlDataAdapter daCostP = new SqlDataAdapter();
DataTable dtCostP = new DataTable();
SqlDataAdapter daCostS = new SqlDataAdapter();
DataTable dtCostS = new DataTable();
SqlDataAdapter daQuantity = new SqlDataAdapter();
DataTable dtQuantity = new DataTable();
try
{
SqlCommand command1 = new SqlCommand("Select * FROM Products " +
"WHERE IdSupp LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daIdSuppl = new SqlDataAdapter
{
SelectCommand = command1
};
dtIdSuppl = new DataTable();
daIdSuppl.Fill(dtIdSuppl);
SqlCommand command2 = new SqlCommand("Select * FROM Products " +
"WHERE NameProduct LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daNameProd = new SqlDataAdapter
{
SelectCommand = command2
};
dtNameProd = new DataTable();
daNameProd.Fill(dtNameProd);
SqlCommand command3 = new SqlCommand("Select * FROM Products " +
"WHERE TypeProduct LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daType = new SqlDataAdapter
{
SelectCommand = command3
};
dtType = new DataTable();
daType.Fill(dtType);
SqlCommand command4 = new SqlCommand("Select * FROM Products " +
"WHERE CostPurchase LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daCostP = new SqlDataAdapter
{
SelectCommand = command4
};
dtCostP = new DataTable();
daCostP.Fill(dtCostP);
SqlCommand command5 = new SqlCommand("Select * FROM Products " +
"WHERE CostSale LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daCostS = new SqlDataAdapter
{
SelectCommand = command5
};
dtCostS = new DataTable();
daCostS.Fill(dtCostS);
SqlCommand command6 = new SqlCommand("Select * FROM Products " +
"WHERE Quantity LIKE '%" + tb.Text + "%'", connect.sqlConnection);
daQuantity = new SqlDataAdapter
{
SelectCommand = command6
};
dtQuantity = new DataTable();
daQuantity.Fill(dtQuantity);
lv.Items.Clear();
foreach (DataRow r in dtIdSuppl.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <int>(4).ToString());
list.SubItems.Add(r.Field <int>(5).ToString());
list.SubItems.Add(r.Field <bool>(6).ToString());
list.SubItems.Add(r.Field <int>(7).ToString());
}
foreach (DataRow r in dtNameProd.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <int>(4).ToString());
list.SubItems.Add(r.Field <int>(5).ToString());
list.SubItems.Add(r.Field <bool>(6).ToString());
list.SubItems.Add(r.Field <int>(7).ToString());
}
foreach (DataRow r in dtType.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <int>(4).ToString());
list.SubItems.Add(r.Field <int>(5).ToString());
list.SubItems.Add(r.Field <bool>(6).ToString());
list.SubItems.Add(r.Field <int>(7).ToString());
}
foreach (DataRow r in dtCostP.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <int>(4).ToString());
list.SubItems.Add(r.Field <int>(5).ToString());
list.SubItems.Add(r.Field <bool>(6).ToString());
list.SubItems.Add(r.Field <int>(7).ToString());
}
foreach (DataRow r in dtCostS.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <int>(4).ToString());
list.SubItems.Add(r.Field <int>(5).ToString());
list.SubItems.Add(r.Field <bool>(6).ToString());
list.SubItems.Add(r.Field <int>(7).ToString());
}
foreach (DataRow r in dtQuantity.Rows)
{
var list = lv.Items.Add(r.Field <int>(0).ToString());
list.SubItems.Add(r.Field <int>(1).ToString());
list.SubItems.Add(r.Field <string>(2));
list.SubItems.Add(r.Field <string>(3));
list.SubItems.Add(r.Field <int>(4).ToString());
list.SubItems.Add(r.Field <int>(5).ToString());
list.SubItems.Add(r.Field <bool>(6).ToString());
list.SubItems.Add(r.Field <int>(7).ToString());
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
daIdSuppl.Dispose();
daNameProd.Dispose();
daType.Dispose();
daCostP.Dispose();
daCostS.Dispose();
daQuantity.Dispose();
}
}
else
{
MessageBox.Show(
"Field must be filled!!!",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
private async void btnAdd_Click(object sender, EventArgs e)
{
if (!string.IsNullOrEmpty(textBoxIdProd.Text) && !string.IsNullOrWhiteSpace(textBoxIdProd.Text) &&
!string.IsNullOrEmpty(dateTimePickerOrd.Text) && !string.IsNullOrWhiteSpace(dateTimePickerOrd.Text) &&
!string.IsNullOrEmpty(textBoxIdEmp.Text) && !string.IsNullOrWhiteSpace(textBoxIdEmp.Text) &&
!string.IsNullOrEmpty(textBoxIdCust.Text) && !string.IsNullOrWhiteSpace(textBoxIdCust.Text))
{
Order ins = new Order
{
IdProd = Int32.Parse(textBoxIdProd.Text),
IdEmpl = Int32.Parse(textBoxIdEmp.Text),
IdCust = Int32.Parse(textBoxIdCust.Text),
DateOrder = dateTimePickerOrd.Value.ToShortDateString()
};
int temp = Int32.Parse(textBoxIdProd.Text);
Product prod = new Product
{
ID = temp
};
ConnectToDB connect = new ConnectToDB();
SqlDataReader sqlReader = null;
await connect.ConnectDB();
try
{
SqlCommand command = new SqlCommand("SELECT [Quantity] FROM [Products] " +
"WHERE [IdProduct] = " + @temp, connect.sqlConnection);
sqlReader = await command.ExecuteReaderAsync();
await sqlReader.ReadAsync();
int q = Int32.Parse(Convert.ToString(sqlReader["Quantity"]));
sqlReader.Close();
if (q > 0)
{
q--;
prod.Quantity = q;
SqlCommand command2 = new SqlCommand("UPDATE [Products] " +
"SET [Quantity] = " + q +
" WHERE [IdProduct] = " + @temp, connect.sqlConnection);
command2.Parameters.AddWithValue("ID", prod.ID);
command2.Parameters.AddWithValue("Quantity", prod.Quantity);
await command2.ExecuteNonQueryAsync();
await ins.InsertOrd();
}
else if (q == 0)
{
SqlCommand command3 = new SqlCommand("UPDATE [Products] " +
"SET [Availability] = " + 0 +
" WHERE [IdProduct] = " + @temp, connect.sqlConnection);
command3.Parameters.AddWithValue("ID", prod.ID);
command3.Parameters.AddWithValue("Availability", prod.Availability);
await command3.ExecuteNonQueryAsync();
MessageBox.Show(
"Quantity of product = 0",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
else
{
MessageBox.Show(
"Error!!!",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show(
"All fields must be filled!!!",
"Error",
MessageBoxButtons.OK,
MessageBoxIcon.Error);
}
textBoxIdProd.Clear();
textBoxIdCust.Clear();
textBoxIdEmp.Clear();
}