MainForm.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Inventory
{
    public partial class MainForm : Form
    {
        private string connectionString = "Server=127.0.0.1;Database=Inventory;User Id=sa;Password=sa;";
        //Create a DataTable to hold the data
        DataTable _productDataTable;
        DataTable _cboTable;
        SqlDataAdapter _productDataAdapter;
        SqlDataAdapter _cboAdapter;


        public MainForm()
        {
            InitializeComponent();
            fillGridView();
            //initCRUD_gridView();
            fillComboBox();
        }

        private void fillGridView()
        {
            string sql_query = "SELECT * FROM product;";
            _productDataTable = new DataTable();
            // Create a SqlDataAdapter to fetch the database data
            _productDataAdapter = new SqlDataAdapter(sql_query, connectionString);
            dataGridView1.Columns.Clear();

            // Fill the DataTable with data from the database
            _productDataAdapter.Fill(_productDataTable);

            // Bind the DataTable to the DataGridView
            dataGridView1.DataSource = _productDataTable;

            // Set the DataGridView to be read-only
            dataGridView1.ReadOnly = true;

        }

        // Description: Initializes the GridView by filling it
        // and initializes SqlDataAdapter to maintain CRUD operations
        //private void initCRUD_gridView()
        //{
        //    // Create a new DataTable Object to hold the data
        //    _productDataTable = new DataTable();
        //    // Create a SqlDataAdapter Object to fetch the database data and fill is DataTable
        //    string sql_query = "SELECT * FROM product;";
        //    _productDataAdapter = new SqlDataAdapter(sql_query, connectionString);

        //    // Fill the DataTable with data from the database
        //    _productDataAdapter.Fill(_productDataTable);

        //    // Bind the DataTable to the DataGridView
        //    dataGridView1.DataSource = _productDataTable;

        //}

        private void insertIntoGridView()
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string insertQuery = "INSERT INTO product(Name, Brand, Type, Size, Category) VALUES(@Name, @Brand, @Type, @Size, @Category);";
                    SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
                    if (string.IsNullOrWhiteSpace(txtNameInsert.Text))
                    {
                        MessageBox.Show("Name field cannot be empty.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txtNameInsert.Focus();
                        return;
                    }
                    insertCommand.Parameters.AddWithValue("@Name", txtNameInsert.Text);
                    insertCommand.Parameters.AddWithValue("@Brand", txtBrandInsert.Text);
                    insertCommand.Parameters.AddWithValue("@Type", cmb_TypeInsert.Text?.ToString() ?? string.Empty);
                    insertCommand.Parameters.AddWithValue("@Size", cmb_SizeInsert.Text?.ToString() ?? string.Empty);
                    insertCommand.Parameters.AddWithValue("@Category", cmb_CategoryInsert.Text?.ToString() ?? string.Empty);
                    
                    insertCommand.ExecuteNonQuery();
                    MessageBox.Show("Data Inserted Successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    clearInsertFields();
                    refreshGridView();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("Error!\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }

        }

        private void deleteFromGridView(string productID)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                try
                {
                    connection.Open();
                    string deleteQuery = "DELETE FROM product WHERE ID = @ID;";
                    SqlCommand deleteCommand = new SqlCommand(deleteQuery, connection);
                    deleteCommand.Parameters.AddWithValue("@ID", productID);
                    deleteCommand.ExecuteNonQuery();
                    MessageBox.Show("Data Deleted Successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);

                    txtDel.Clear();
                    refreshGridView();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }

            }
        }

        // Description: Updates the selected row in the GridView based on the ID.
        //private void updateGridView()
        //{
        //    using (SqlConnection connection = new SqlConnection(connectionString))
        //    {
        //        try
        //        {
        //            connection.Open();
        //            SqlCommand updateCommand = new SqlCommand("UPDATE product SET Name = @Name, Brand = @Brand, Type = @Type, Size = @Size, Category = @Category WHERE ID = @ID;", connection);
        //            if (string.IsNullOrWhiteSpace(txtIDUpdate.Text))
        //            {
        //                MessageBox.Show("ID field cannot be empty.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        //                txtIDUpdate.Focus();
        //                return;
        //            }
        //            updateCommand.Parameters.AddWithValue("@ID", txtIDUpdate.Text);
        //            updateCommand.Parameters.AddWithValue("@Name", txtNameUpdate.Text);
        //            updateCommand.Parameters.AddWithValue("@Brand", txtBrandUpdate.Text);
        //            updateCommand.Parameters.AddWithValue("@Type", cmb_TypeUpdate.Text?.ToString() ?? string.Empty);
        //            updateCommand.Parameters.AddWithValue("@Size", cmb_SizeUpdate.Text?.ToString() ?? string.Empty);
        //            updateCommand.Parameters.AddWithValue("@Category", cmb_CategoryUpdate.Text?.ToString() ?? string.Empty);
                    
        //            updateCommand.ExecuteNonQuery();
        //            MessageBox.Show("Data Updated Successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
        //            clearUpdateFields();
        //            refreshGridView();
        //        }
        //        catch (Exception ex)
        //        {
        //            MessageBox.Show("Error!\n" + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        //        }
        //    }
        //}

        private void updateGridView()
        {
            // updateStructure will hold the SQL placeholders for the fields to be updated
            List<string> updateStructure= new List<string>();

            // Create a SqlConnection to the database
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                
                // Validate that the ID field is not empty
                if (string.IsNullOrWhiteSpace(txtIDUpdate.Text))
                {
                    MessageBox.Show("ID field cannot be empty.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtIDUpdate.Focus();
                    return;
                }
                else
                {
                    // Replaces the placeholder with the actual value
                    command.Parameters.AddWithValue("@ID", txtIDUpdate.Text);
                }

                // Validate that the ID field is numeric.
                if (!int.TryParse(txtIDUpdate.Text, out _))
                {
                    MessageBox.Show("ID must be a numeric value.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtIDUpdate.Focus();
                    return;
                }

                if (!string.IsNullOrWhiteSpace(txtNameUpdate.Text))
                {
                    updateStructure.Add("Name = @Name");
                    // Replaces the placeholder with the actual value
                    command.Parameters.AddWithValue("@Name", txtNameUpdate.Text);
                }
                if (!string.IsNullOrWhiteSpace(txtBrandUpdate.Text))
                {
                    updateStructure.Add("Brand = @Brand");
                    command.Parameters.AddWithValue("@Brand", txtBrandUpdate.Text);
                }
                if (cmb_TypeUpdate.SelectedIndex > -1 || !string.IsNullOrWhiteSpace(cmb_TypeUpdate.Text))
                {
                    updateStructure.Add("Type = @Type");
                    command.Parameters.AddWithValue("@Type", cmb_TypeUpdate.Text);
                }
                if (cmb_SizeUpdate.SelectedIndex > -1 || !string.IsNullOrWhiteSpace(cmb_SizeUpdate.Text))
                {
                    updateStructure.Add("Size = @Size");
                    command.Parameters.AddWithValue("@Size", cmb_SizeUpdate.Text);
                }
                if (cmb_CategoryUpdate.SelectedIndex > -1 || !string.IsNullOrWhiteSpace(cmb_CategoryUpdate.Text))
                {
                    updateStructure.Add("Category = @Category");
                    command.Parameters.AddWithValue("@Category", cmb_CategoryUpdate.Text);
                }

                string updateQuery = string.Join(", ", updateStructure);
                
                if (string.IsNullOrWhiteSpace(updateQuery))
                {
                    MessageBox.Show("No fields to update.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                command.CommandText = $"UPDATE product SET {updateQuery} WHERE ID = @ID;";
                command.Connection = connection;
                command.ExecuteNonQuery();
                
                clearUpdateFields();
                refreshGridView();
            }
            
        }
        // Description: Binds a ComboBox to the database
        // sql_query: SQL query to fetch distinct values
        // displayMem: Column name to display in the ComboBox
        // valueMem: Column name to use as the value of the ComboBox items for logical control
        private void bindComboBox(ComboBox cboBox,string sql_query, string displayMem, string valueMem)
        {

            _cboAdapter = new SqlDataAdapter(sql_query, connectionString);
            _cboTable = new DataTable();
            _cboAdapter.Fill(_cboTable);
            
            cboBox.DataSource = _cboTable;
            cboBox.DisplayMember = displayMem;
            cboBox.ValueMember = valueMem;

            cboBox.SelectedIndex = -1;

        }

        //private void refreshComboBox()
        //{
        //    _cboTable.Clear();
        //    _cboAdapter.Fill(_cboTable);
        //}

        // Description: Creates a list of filters based on the
        // text in the TextBoxes and the selected items in the ComboBoxes
        private void applyFilter()
        {
            List<string> filters = new List<string>();

            if (!string.IsNullOrWhiteSpace(txtIDUpdate.Text))
            {
                filters.Add($"ID = '{txtIDUpdate.Text}'");
            }
            
            if (!string.IsNullOrWhiteSpace(txtName.Text))
            {
                filters.Add($"Name LIKE '%{txtName.Text}%'");
            }

            if (!string.IsNullOrWhiteSpace(txtBrand.Text))
            {
                filters.Add("Brand LIKE '%" + txtBrand.Text + "%'");
            }

            if (cmb_Type.SelectedIndex != -1)
            {
                filters.Add($"Type = '{cmb_Type.Text}'");
            }

            if (cmb_Size.SelectedIndex != -1)
            {
                filters.Add($"Size = '{cmb_Size.Text}'");
            }

            if (cmb_Category.SelectedIndex != -1)
            {
                filters.Add($"Category = '{cmb_Category.Text}'");
            }

            // If no filters are applied, show all products.
            string finalFilter = string.Join(" AND ", filters);
            _productDataTable.DefaultView.RowFilter = finalFilter;
        }

        // Fills the ComboBoxes with distinct values from the database
        private void fillComboBox()
        {
            // Bind the ComboBoxes to distinct values from the database
            // ISSUE: Empty values are not handled properly (e.g., if a product has no Type, Size, or Category)
            bindComboBox(cmb_Type, "SELECT DISTINCT Type FROM product;", "Type", "Type");
            bindComboBox(cmb_Size, "SELECT DISTINCT Size FROM product;", "Size", "Size");
            bindComboBox(cmb_Category, "SELECT DISTINCT Category FROM product;", "Category", "Category");
            
            // For the Insert section
            bindComboBox(cmb_TypeInsert, "SELECT DISTINCT Type FROM product;", "Type", "Type");
            bindComboBox(cmb_SizeInsert, "SELECT DISTINCT Size FROM product;", "Size", "Size");
            bindComboBox(cmb_CategoryInsert, "SELECT DISTINCT Category FROM product;", "Category", "Category");

            //For the Update section
            bindComboBox(cmb_TypeUpdate, "SELECT DISTINCT Type FROM product;", "Type", "Type");
            bindComboBox(cmb_SizeUpdate, "SELECT DISTINCT Size FROM product;", "Size", "Size");
            bindComboBox(cmb_CategoryUpdate, "SELECT DISTINCT Category FROM product;", "Category", "Category");
        }

        // Event handler for the "Reset" button
        private void btn_Reset_Click(object sender, EventArgs e)
        {
            clearSearchFields();
        }

        private void txtName_TextChanged(object sender, EventArgs e)
        {
            applyFilter();
        }
        private void txtBrand_TextChanged(object sender, EventArgs e)
        {
            applyFilter();
        }

        private void cmb_Type_SelectedIndexChanged(object sender, EventArgs e)
        {
            applyFilter();
        }

        private void cmb_Size_SelectedIndexChanged(object sender, EventArgs e)
        {
            applyFilter();
        }

        private void cmb_Category_SelectedIndexChanged(object sender, EventArgs e)
        {
            applyFilter();
        }


        private void refreshGridView()
        {
            _productDataTable.Clear();
            _productDataAdapter.Fill(_productDataTable);
        }

        private void btn_Insert_Click(object sender, EventArgs e)
        {
            insertIntoGridView();
            //refreshComboBox();
            fillComboBox();
        }

        private void clearInsertFields()
        {
            txtNameInsert.Clear();
            txtBrandInsert.Clear();
            cmb_TypeInsert.SelectedIndex = -1;
            cmb_SizeInsert.SelectedIndex = -1;
            cmb_CategoryInsert.SelectedIndex = -1;
        }

        private void clearUpdateFields()
        {
            txtIDUpdate.Clear();
            txtNameUpdate.Clear();
            txtBrandUpdate.Clear();
            cmb_TypeUpdate.SelectedIndex = -1;
            cmb_SizeUpdate.SelectedIndex = -1;
            cmb_CategoryUpdate.SelectedIndex = -1;
        }

        private void clearSearchFields()
        {
            txtName.Clear();
            txtBrand.Clear();

            // Reset the ComboBoxes to their default state (No selection)
            cmb_Type.SelectedIndex = -1;
            cmb_Size.SelectedIndex = -1;
            cmb_Category.SelectedIndex = -1;
        }

        private void btn_Del_Click(object sender, EventArgs e)
        {
            deleteFromGridView(txtDel.Text);
            //refreshComboBox();
            fillComboBox();
        }

        private void btn_Update_Click(object sender, EventArgs e)
        {
            updateGridView();
            refreshGridView();
        }

        private void txtIDUpdate_TextChanged(object sender, EventArgs e)
        {
            clearInsertFields();
            applyFilter();
        }
    }
}