/// <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(); }