/// <summary>
/// Dodawanie rekordów do bazy danych
/// </summary>
/// <param name="sender">wysyłanie</param>
/// <param name="e">po wcisnieciu przicisku myszy</param>
private void button1_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection1 = new SqlConnection("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=Magazyn;Integrated Security=True");
SharedSqlCommand sharedSqlCommand = new SharedSqlCommand();
// warunek sprawdzajacy czy produkt o podanym kodzie i zamówienie o podanym ID istnieje jeśli nie wyskoczy powiadomienie
if (!sharedSqlCommand.IfProductsExists(textBox2.Text) || !sharedSqlCommand.IfOrderExists(textBox1.Text))
{
MessageBox.Show("Order or Product not exists");
}
// jeśli istnieją dodaje rekord do bazy
else
{
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = @"INSERT INTO [Magazyn].[dbo].[Orders_products]
([order_id]
,[ProductCode])
VALUES
('" + textBox1.Text + "','" + textBox2.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
// Wczytawanie bazy
LoadData();
}
}
//usuwanie rekordów (po order_id)
/// <summary>
/// Usuwanie rekordów z bazy danych
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection1 = new SqlConnection("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=Magazyn;Integrated Security=True");
var sqlQuery = "";
SharedSqlCommand sharedSqlCommand = new SharedSqlCommand();
if (sharedSqlCommand.IfOrderExists(textBox1.Text))
{
sqlConnection1.Open();
sqlQuery = @"DELETE FROM [Orders] WHERE [order_id] = '" + textBox1.Text + "'";
SqlCommand cmd = new SqlCommand(sqlQuery, sqlConnection1);
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else
{
MessageBox.Show("Record Not Exists");
}
// Wczytawanie bazy
LoadData();
}
// przycisk add
/// <summary>
/// Dodawanie rekordów do bazy danych
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection1 = new SqlConnection("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=Magazyn;Integrated Security=True");
SqlCommand cmd = new SqlCommand();
cmd.CommandType = System.Data.CommandType.Text;
SharedSqlCommand sharedSqlCommand = new SharedSqlCommand();
// sprawdzenie czy nie ma już zamówienia o takim id
if (sharedSqlCommand.IfOrderExists(textBox1.Text))
{
MessageBox.Show("Record With this ID already exists");
}
// sprawdzenie czy klient jest w bazie
else if (!sharedSqlCommand.IfClientExists(textBox2.Text))
{
MessageBox.Show("There is no client with this ID");
}
// sprawdzenie formatu daty
else if (!sharedSqlCommand.DateTimeValidation(textBox3.Text))
{
MessageBox.Show("Wrong date format");
}
// jeśli wszystko powyżej się zgadza, dodaje rekord do tabeli Orders
else
{
cmd.CommandText = @"INSERT INTO [Magazyn].[dbo].[Orders]
([order_id]
,[client_id]
,[order_date])
VALUES
('" + textBox1.Text + "','" + textBox2.Text + "','" + sharedSqlCommand.ConvertStringDateTime(textBox3.Text).ToString("MM-dd-yyyy") + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
// Wczytawanie bazy
LoadData();
}
/// <summary>
/// Usuwanie rekordów z bazy danych
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button2_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection1 = new SqlConnection("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=Magazyn;Integrated Security=True");
var sqlQuery = "";
SharedSqlCommand sharedSqlCommand = new SharedSqlCommand();
// warunek sprawdzający czy klient po zaznaczeniu w datagridzie jeśli tak usuwa rekord jeśli nie wyskakuje powiadomienie "Record Not Exists"
if (sharedSqlCommand.IfClientExists(dataGridView1.SelectedRows[0].Cells[0].Value.ToString()))
{
sqlConnection1.Open();
sqlQuery = $@"DELETE FROM [Clients] WHERE [client_id] = {dataGridView1.SelectedRows[0].Cells[0].Value.ToString()}";
SqlCommand cmd = new SqlCommand(sqlQuery, sqlConnection1);
cmd.ExecuteNonQuery();
sqlConnection1.Close();
}
else
{
MessageBox.Show("Record Not Exists");
}
// Wczytawanie bazy
LoadData();
}
/// <summary>
/// Metoda dodawania i updatowania rekordów do bazy danych
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=Magazyn;Integrated Security=True");
con.Open();
bool status = false;
if (comboBox1.SelectedIndex == 0)
{
status = true;
}
else
{
status = false;
}
var sqlQuery = "";
SharedSqlCommand sharedSqlCommand = new SharedSqlCommand();
if (!sharedSqlCommand.QuantityValidation(textBox3.Text))
{
MessageBox.Show("Quantity must be a number");
}
else if (sharedSqlCommand.IfProductsExists(textBox1.Text))
{
sqlQuery = @"UPDATE [Products] SET [Quantity] = '" + textBox3.Text + "' ,[ProductStatus] = '" + status + "' WHERE [ProductCode] = '" + textBox1.Text + "'";
}
else
{
sqlQuery = @"INSERT INTO [Magazyn].[dbo].[Products] ([ProductCode] ,[ProductName] ,[Quantity] ,[ProductStatus]) VALUES
('" + textBox1.Text + "','" + textBox2.Text + "','" + textBox3.Text + "','" + status + "')";
}
SqlCommand cmd = new SqlCommand(sqlQuery, con);
cmd.ExecuteNonQuery();
con.Close();
// Wczytawanie bazy
LoadData();
ResetRecords();
}