Разработка БД и приложения учета товаров на складе

h#pragma once#include "DBConnection.h"namespace InventoryApp {using namespace System;using namespace System::ComponentModel;using namespace System::Collections;using namespace System::Windows::Forms;using namespace System::Data;using namespace System::Drawing;using namespace System::Data::SqlClient;///

/// Summary for Customers///

public ref class Customers : public System::Windows::Forms::Form{public:Customers(void){InitializeComponent();loadCustomerDataFromDB();}private: System::Windows::Forms::DataGridViewTextBoxColumn^ CustomerID;public:private: System::Windows::Forms::DataGridViewTextBoxColumn^ FirstName;private: System::Windows::Forms::DataGridViewTextBoxColumn^ LastName;private: System::Windows::Forms::DataGridViewTextBoxColumn^ Phone;private: System::Windows::Forms::DataGridViewTextBoxColumn^ Email;private: System::Windows::Forms::DataGridViewTextBoxColumn^ City;private: System::Windows::Forms::DataGridViewTextBoxColumn^ Address;private: System::Windows::Forms::DataGridViewTextBoxColumn^ Balance;DataTable^ customersDataTable = gcnew DataTable("Customers");protected:///

/// Clean up any resources being used.///

~Customers(){if (components){delete components;}}private: System::Windows::Forms::Panel^ panel1;protected:private: System::Windows::Forms::Button^ btnRefreshCustomers;private: System::Windows::Forms::DataGridView^ dataGridCustomers;private: System::Windows::Forms::Label^ label6;private: System::Windows::Forms::TextBox^ txtCustomerSearch;private: System::Windows::Forms::GroupBox^ groupBox5;private: System::Windows::Forms::Button^ btnClearFields;private: System::Windows::Forms::TextBox^ txtCustomerBalance;private: System::Windows::Forms::Button^ btnCreateNewCustomer;private: System::Windows::Forms::Label^ label29;private: System::Windows::Forms::TextBox^ txtCustomerAddress;private: System::Windows::Forms::Label^ label21;private: System::Windows::Forms::TextBox^ txtCustomerCity;private: System::Windows::Forms::Label^ label26;private: System::Windows::Forms::TextBox^ txtCustomerEmail;private: System::Windows::Forms::Label^ label27;private: System::Windows::Forms::Button^ btnDeleteCustomer;private: System::Windows::Forms::Button^ btnUpdateCustomer;private: System::Windows::Forms::TextBox^ txtCustomerPhone;private: System::Windows::Forms::Label^ label22;private: System::Windows::Forms::TextBox^ txtCustomerLastName;private: System::Windows::Forms::Label^ label23;private: System::Windows::Forms::TextBox^ txtCustomerFirstName;private: System::Windows::Forms::Label^ label24;private: System::Windows::Forms::TextBox^ txtCustomerID;private: System::Windows::Forms::Label^ label25;protected:private:///

/// Required designer variable.///

System::ComponentModel::Container^ components;private: System::Void btnRefreshCustomers_Click(System::Object^ sender, System::EventArgs^ e) {customersDataTable->Clear();loadCustomerDataFromDB();}private: System::Void Customers_Load(System::Object^ sender, System::EventArgs^ e) {}private: System::Void txtCustomerSearch_TextChanged(System::Object^ sender, System::EventArgs^ e) {customersDataTable->DefaultView->RowFilter = String::Format("FirstName LIKE '%{0}%'", txtCustomerSearch->Text);}private: System::Void btnClearFields_Click(System::Object^ sender, System::EventArgs^ e) {txtCustomerID->Text = "";txtCustomerFirstName->Text = "";txtCustomerLastName->Text = "";txtCustomerPhone->Text = "";txtCustomerEmail->Text = "";txtCustomerCity->Text = "";txtCustomerAddress->Text = "";txtCustomerBalance->Text = "";txtCustomerSearch->Text = "";}private: System::Void btnCreateNewCustomer_Click(System::Object^ sender, System::EventArgs^ e) {if (validCustomer()){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "INSERT INTO customers(FirstName, LastName, Phone, Email, City, Address, Balance) VALUES(@FirstName, @LastName, @Phone, @Email, @City, @Address, @Balance)";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();sqlCommand->CommandType = CommandType::Text;sqlCommand->Parameters->AddWithValue("@FirstName", txtCustomerFirstName->Text);sqlCommand->Parameters->AddWithValue("@LastName", txtCustomerLastName->Text);sqlCommand->Parameters->AddWithValue("@Phone", txtCustomerPhone->Text);sqlCommand->Parameters->AddWithValue("@Email", txtCustomerEmail->Text);sqlCommand->Parameters->AddWithValue("@City", txtCustomerCity->Text);sqlCommand->Parameters->AddWithValue("@Address", txtCustomerAddress->Text);sqlCommand->Parameters->AddWithValue("@Balance", double::Parse(txtCustomerBalance->Text));sqlCommand->ExecuteNonQuery();customersDataTable->Clear();loadCustomerDataFromDB();MessageBox::Show("Новый пользователь успешно добавлен");}catch (Exception^ ex){MessageBox::Show("Ошибка при создании пользователя");}finally{connection->Close();}}}}else{MessageBox::Show("Ошибка в полях ввода");}}private: System::Void btnUpdateCustomer_Click(System::Object^ sender, System::EventArgs^ e) {if (validCustomer()){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "UPDATE customers SET FirstName=@FirstName, LastName=@LastName, Phone=@Phone, Email=@Email, City=@City, Address=@Address, Balance=@Balance WHERE CustomerID=@CustomerID";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();sqlCommand->CommandType = CommandType::Text;sqlCommand->Parameters->AddWithValue("@FirstName", txtCustomerFirstName->Text);sqlCommand->Parameters->AddWithValue("@LastName", txtCustomerLastName->Text);sqlCommand->Parameters->AddWithValue("@Phone", txtCustomerPhone->Text);sqlCommand->Parameters->AddWithValue("@Email", txtCustomerEmail->Text);sqlCommand->Parameters->AddWithValue("@City", txtCustomerCity->Text);sqlCommand->Parameters->AddWithValue("@Address", txtCustomerAddress->Text);sqlCommand->Parameters->AddWithValue("@Balance", double::Parse(txtCustomerBalance->Text));sqlCommand->Parameters->AddWithValue("@CustomerID", int::Parse(txtCustomerID->Text));sqlCommand->ExecuteNonQuery();customersDataTable->Clear();loadCustomerDataFromDB();MessageBox::Show("Данные пользователя успешно обновлены");}catch (Exception^ ex){MessageBox::Show("Ошибка обновления данных");}finally{connection->Close();}}}}else{MessageBox::Show("Ошибка в полях ввода");}}private: System::Void btnDeleteCustomer_Click(System::Object^ sender, System::EventArgs^ e) {if (validCustomer()){System::Windows::Forms::DialogResult dialogResult = MessageBox::Show("Вы действительно желаете удалить пользователя?", "Предупреждение", MessageBoxButtons::YesNo);if (dialogResult == System::Windows::Forms::DialogResult::Yes){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "DELETE FROM customers WHERE CustomerID=@CustomerID";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();sqlCommand->CommandType = CommandType::Text;sqlCommand->Parameters->AddWithValue("@CustomerID", int::Parse(txtCustomerID->Text));sqlCommand->ExecuteNonQuery();customersDataTable->Clear();loadCustomerDataFromDB();MessageBox::Show("Пользователь успешно удален");}catch (Exception^ ex){MessageBox::Show("Ошибка удаления");}finally{connection->Close();}}}}else if (dialogResult == System::Windows::Forms::DialogResult::No){}}}private: void loadCustomerDataFromDB(){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "SELECT * FROM customers";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();SqlDataAdapter^ adapter = gcnew SqlDataAdapter(sqlCommand);adapter->Fill(customersDataTable);dataGridCustomers->DataSource = customersDataTable;}catch (Exception^ ex){MessageBox::Show("Ошибка при работе с базой данных");}finally{connection->Close();}}}}private: System::Void dataGridCustomers_CellClick(System::Object^ sender, System::Windows::Forms::DataGridViewCellEventArgs^ e) {if (dataGridCustomers->SelectedRows->Count == 1){txtCustomerID->Text = dataGridCustomers->SelectedCells[0]->Value->ToString();txtCustomerFirstName->Text = dataGridCustomers->SelectedCells[1]->Value->ToString();txtCustomerLastName->Text = dataGridCustomers->SelectedCells[2]->Value->ToString();txtCustomerPhone->Text = dataGridCustomers->SelectedCells[3]->Value->ToString();txtCustomerEmail->Text = dataGridCustomers->SelectedCells[4]->Value->ToString();txtCustomerCity->Text = dataGridCustomers->SelectedCells[5]->Value->ToString();txtCustomerAddress->Text = dataGridCustomers->SelectedCells[6]->Value->ToString();txtCustomerBalance->Text = dataGridCustomers->SelectedCells[7]->Value->ToString();}else{MessageBox::Show("Выберите строку");}}private: Boolean^ validCustomer(){if (txtCustomerFirstName->Text != "" && txtCustomerPhone->Text != "" && txtCustomerLastName->Text != "" && txtCustomerEmail->Text != "" && txtCustomerCity->Text != "" && txtCustomerAddress->Text != "" && txtCustomerBalance->Text != ""){return true;}else{return false;}}};}Inventory.h#pragma once#include "DBConnection.h"namespace InventoryApp {using namespace System;using namespace System::ComponentModel;using namespace System::Collections;using namespace System::Windows::Forms;using namespace System::Data;using namespace System::Drawing;using namespace System::Data::SqlClient;///

/// Summary for Inventory///

public ref class Inventory : public System::Windows::Forms::Form{public:Inventory(void){InitializeComponent();this->dataGridProducts->AutoGenerateColumns = false;loadProductsFromDB();loadCategoriesFromDB();}private: System::Windows::Forms::DataGridViewTextBoxColumn^ ProductID;public:private: System::Windows::Forms::DataGridViewTextBoxColumn^ ProductName;private: System::Windows::Forms::DataGridViewTextBoxColumn^ ProductPrice;private: System::Windows::Forms::DataGridViewTextBoxColumn^ ProductQuantity;private: System::Windows::Forms::DataGridViewTextBoxColumn^ CategoryName;DataTable^ productsDataTable = gcnew DataTable("Products");protected:///

/// Clean up any resources being used.///

~Inventory(){if (components){delete components;}}private: System::Windows::Forms::Label^ label2;protected:private: System::Windows::Forms::Button^ btnRefreshProducts;private: System::Windows::Forms::Button^ btnClearFields;private: System::Windows::Forms::Button^ btnCreateNewProduct;private: System::Windows::Forms::Label^ label1;private: System::Windows::Forms::ComboBox^ comboCategories;private: System::Windows::Forms::Button^ btnDeleteProduct;private: System::Windows::Forms::Button^ btnUpdateProduct;private: System::Windows::Forms::TextBox^ txtProductQuantity;private: System::Windows::Forms::Label^ label6;private: System::Windows::Forms::TextBox^ txtProductPrice;private: System::Windows::Forms::Label^ label7;private: System::Windows::Forms::TextBox^ txtProductName;private: System::Windows::Forms::Label^ label8;private: System::Windows::Forms::TextBox^ txtProductID;private: System::Windows::Forms::Label^ label9;private: System::Windows::Forms::DataGridView^ dataGridProducts;private: System::Windows::Forms::GroupBox^ groupBox2;private: System::Windows::Forms::TextBox^ txtProductSearch;private:///

/// Required designer variable.///

System::ComponentModel::Container^ components;private: System::Void label2_Click(System::Object^ sender, System::EventArgs^ e) {}private: System::Void txtProductSearch_TextChanged(System::Object^ sender, System::EventArgs^ e) {productsDataTable->DefaultView->RowFilter = String::Format("ProductName LIKE '%{0}%'", txtProductSearch->Text);}private: System::Void dataGridProducts_CellClick(System::Object^ sender, System::Windows::Forms::DataGridViewCellEventArgs^ e) {if (dataGridProducts->SelectedRows->Count == 1){txtProductID->Text = dataGridProducts->SelectedCells[0]->Value->ToString();txtProductName->Text = dataGridProducts->SelectedCells[1]->Value->ToString();txtProductPrice->Text = dataGridProducts->SelectedCells[2]->Value->ToString();txtProductQuantity->Text = dataGridProducts->SelectedCells[3]->Value->ToString();comboCategories->Text = dataGridProducts->SelectedCells[4]->Value->ToString();}else{MessageBox::Show("Выберите строку");}}private: System::Void btnRefreshProducts_Click(System::Object^ sender, System::EventArgs^ e) {productsDataTable->Clear();loadProductsFromDB();loadCategoriesFromDB();}private: System::Void btnClearFields_Click(System::Object^ sender, System::EventArgs^ e) {txtProductID->Text = "";txtProductName->Text = "";txtProductPrice->Text = "";txtProductQuantity->Text = "";comboCategories->Text = "";txtProductSearch->Text = "";}private: System::Void btnCreateNewProduct_Click(System::Object^ sender, System::EventArgs^ e) {if (validateProduct()){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "INSERT INTO products(ProductName, ProductPrice, ProductQuantity, CategoryID) VALUES(@ProductName, @ProductPrice, @ProductQuantity, @CategoryID)";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();sqlCommand->CommandType = CommandType::Text;sqlCommand->Parameters->AddWithValue("@ProductName", txtProductName->Text);sqlCommand->Parameters->AddWithValue("@ProductPrice", double::Parse(txtProductPrice->Text));sqlCommand->Parameters->AddWithValue("@ProductQuantity", int::Parse(txtProductQuantity->Text));sqlCommand->Parameters->AddWithValue("@CategoryID", int::Parse(comboCategories->SelectedValue->ToString()));sqlCommand->ExecuteNonQuery();productsDataTable->Clear();loadProductsFromDB();MessageBox::Show("Новый товар успешно добавлен");}catch (Exception^ ex){MessageBox::Show("Ошибка загрузки данных из базы данных");}finally{connection->Close();}}}}else{MessageBox::Show("Ошибка в полях ввода");}}private: System::Void btnUpdateProduct_Click(System::Object^ sender, System::EventArgs^ e) {if (validateProduct()){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "UPDATE products SET ProductName=@ProductName, ProductPrice=@ProductPrice, ProductQuantity=@ProductQuantity, CategoryName=@CategoryName WHERE ProductID=@ProductID";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();sqlCommand->CommandType = CommandType::Text;sqlCommand->Parameters->AddWithValue("@ProductName", txtProductName->Text);sqlCommand->Parameters->AddWithValue("@ProductPrice", double::Parse(txtProductPrice->Text));sqlCommand->Parameters->AddWithValue("@ProductQuantity", int::Parse(txtProductQuantity->Text));sqlCommand->Parameters->AddWithValue("@CategoryName", comboCategories->Text);sqlCommand->Parameters->AddWithValue("@ProductID", int::Parse(txtProductID->Text));sqlCommand->ExecuteNonQuery();productsDataTable->Clear();loadProductsFromDB();MessageBox::Show("Данные товара успешно обновлены");}catch (Exception^ ex){MessageBox::Show("Ошибка базы данных");}finally{connection->Close();}}}}else{MessageBox::Show("Ошибка в полях ввода");}}private: System::Void btnDeleteProduct_Click(System::Object^ sender, System::EventArgs^ e) {if (validateProduct()){System::Windows::Forms::DialogResult dialogResult = MessageBox::Show("Вы действительно желаете удалить товар?", "Предупреждение", MessageBoxButtons::YesNo);if (dialogResult == System::Windows::Forms::DialogResult::Yes){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "DELETE FROM products WHERE ProductID=@ProductID";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();sqlCommand->CommandType = CommandType::Text;sqlCommand->Parameters->AddWithValue("@ProductID", int::Parse(txtProductID->Text));sqlCommand->ExecuteNonQuery();productsDataTable->Clear();loadProductsFromDB();MessageBox::Show("Товар удален");}catch (Exception^ ex){MessageBox::Show("Ошибка работы с базой данных");}finally{connection->Close();}}}}else if (dialogResult == System::Windows::Forms::DialogResult::No){}}else{MessageBox::Show("Ошибка в полях ввода");}}private: void loadProductsFromDB(){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "SELECT products.*, categories.CategoryName FROM products left join categories on products.CategoryID=categories.CategoryID";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();SqlDataAdapter^ adapter = gcnew SqlDataAdapter(sqlCommand);adapter->Fill(productsDataTable);dataGridProducts->DataSource = productsDataTable;}catch (Exception^ ex){MessageBox::Show("Ошибка работы с базой данных");}finally{connection->Close();}}}}private: void loadCategoriesFromDB(){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "SELECT * FROM categories";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();comboCategories->ValueMember = "CategoryID";comboCategories->DisplayMember = "CategoryName";DataTable^ dataTable = gcnew DataTable();SqlDataAdapter^ sqlDataAdapter = gcnew SqlDataAdapter(query, connection);sqlDataAdapter->Fill(dataTable);comboCategories->DataSource = dataTable;comboCategories->Refresh();}catch (Exception^ ex){MessageBox::Show("Ошибка загрузки списка категорий из базы данных");}finally{connection->Close();}}}}private: Boolean^ validateProduct(){double price;int qty;if (txtProductName->Text != "" && comboCategories->Text != "" && Double::TryParse(txtProductPrice->Text, price) && int::TryParse(txtProductQuantity->Text, qty)){return true;}else{return false;}}private: System::Void Inventory_Load(System::Object^ sender, System::EventArgs^ e) {}};}ManageOrders.h#pragma once#include "DBConnection.h"namespace InventoryApp {using namespace System;using namespace System::ComponentModel;using namespace System::Collections;using namespace System::Windows::Forms;using namespace System::Data;using namespace System::Drawing;using namespace System::Data::SqlClient;///

/// Summary for ManageOrders///

public ref class ManageOrders : public System::Windows::Forms::Form{public:ManageOrders(void){InitializeComponent();//this->dataGridOrders->AutoGenerateColumns = false;this->dataGridSales->AutoGenerateColumns = false;//loadOrdersFromDatabase();loadSalesFromDatabase();}DataTable^ ordersDataTable = gcnew DataTable("Orders");private: System::Windows::Forms::Panel^ panel1;public:private: System::Windows::Forms::Label^ label2;private: System::Windows::Forms::TextBox^ txtSearchOrderIDs;private: System::Windows::Forms::DataGridView^ dataGridSales;private: System::Windows::Forms::DataGridViewTextBoxColumn^ dgsSaleID;private: System::Windows::Forms::DataGridViewTextBoxColumn^ dgsOrderID;private: System::Windows::Forms::DataGridViewTextBoxColumn^ dgsFirstName;private: System::Windows::Forms::DataGridViewTextBoxColumn^ dgsProductName;private: System::Windows::Forms::DataGridViewTextBoxColumn^ dgsQuantity;private: System::Windows::Forms::DataGridViewTextBoxColumn^ dgsTotalPrice;public:DataTable^ salesDataTable = gcnew DataTable("Sales");protected:///

/// Clean up any resources being used.///

~ManageOrders(){if (components){delete components;}}protected:private:///

/// Required designer variable.///

System::ComponentModel::Container^ components;private: System::Void ManageOrders_Load(System::Object^ sender, System::EventArgs^ e) {}/*private: System::Void txtSearchOrderID_TextChanged(System::Object^ sender, System::EventArgs^ e) {ordersDataTable->DefaultView->RowFilter = String::Format("convert(OrderID, 'System.String') LIKE '%{0}%'", txtSearchOrderID->Text);}*/private: System::Void txtSearchOrderIDs_TextChanged(System::Object^ sender, System::EventArgs^ e) {salesDataTable->DefaultView->RowFilter = String::Format("convert(OrderID, 'System.String') LIKE '%{0}%'", txtSearchOrderIDs->Text);}//private: System::Void dataGridOrders_CellClick(System::Object^ sender, System::Windows::Forms::DataGridViewCellEventArgs^ e) {//if (dataGridOrders->SelectedRows->Count == 1)//{//}//else//{//MessageBox::Show("Ошибка");//}//}//private: void loadOrdersFromDatabase()//{//SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());//{//String^ query = "SELECT * FROM orders";//SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);//{//try//{//connection->Open();//SqlDataAdapter^ adapter = gcnew SqlDataAdapter(sqlCommand);//adapter->Fill(ordersDataTable);//dataGridOrders->DataSource = ordersDataTable;//}//catch (Exception^ ex)//{//MessageBox::Show("Ошибка работы с базой данных");//}//finally//{//connection->Close();//}//}//}//}private: void loadSalesFromDatabase(){SqlConnection^ connection = gcnew SqlConnection(InventoryApp::DBConnection::ConnectionString());{String^ query = "SELECT sales.*, products.ProductName FROM sales inner join products on sales.ProductID=products.ProductID";SqlCommand^ sqlCommand = gcnew SqlCommand(query, connection);{try{connection->Open();SqlDataAdapter^ adapter = gcnew SqlDataAdapter(sqlCommand);adapter->Fill(salesDataTable);dataGridSales->DataSource = salesDataTable;}catch (Exception^ ex){MessageBox::Show("Ошибка загрузки данных из базы данных");}finally{connection->Close();}}}}};}