C#实现枚举(Enum)与ComboBox结合的图书分类选择问题

Hey there! Let's break this down into simple, actionable steps to get your ComboBox showing friendly category names while saving the correct CategoryID to your database. We'll also fix a critical security issue in your database code along the way.

Step 1: Bind Your BookCategory Enum to the ComboBox #

Your Enum is already defined, but we need to connect it to the ComboBox so it displays ID - CategoryName (like "1 - Comics") while storing the underlying CategoryID value.

Add this code right after InitializeComponent(); in your form's constructor:

// Populate the ComboBox with our BookCategory enum values
foreach (BookCategory category in Enum.GetValues(typeof(BookCategory)))
{
    int categoryId = (int)category;
    string categoryName = category.ToString();
    // Add an item that shows "ID - Name" and stores the ID as its value
    comboBoxCategory.Items.Add(new { Text = $"{categoryId} - {categoryName}", Value = categoryId });
}

// Tell the ComboBox which property to display to the user
comboBoxCategory.DisplayMember = "Text";
// Tell it which property holds the actual CategoryID we need for the database
comboBoxCategory.ValueMember = "Value";

// Optional: Set a default selected item so the user doesn't see an empty box
if (comboBoxCategory.Items.Count > 0)
{
    comboBoxCategory.SelectedIndex = 0;
}

Note: Replace comboBoxCategory with your actual ComboBox control name, and delete the old txtCategory text box—we won't need it anymore!

Step 2: Get the Selected CategoryID for Saving #

When the user clicks the save button, instead of reading from a text box, we'll pull the selected CategoryID directly from the ComboBox:

// Retrieve the selected CategoryID from the ComboBox's value property
int selectedCategoryId = (int)comboBoxCategory.SelectedValue;

Step 3: Fix the Database Code (Prevent SQL Injection!) #

Your current code uses string concatenation to build the SQL query, which is extremely unsafe (it allows SQL injection attacks) and can break if someone enters a name with a single quote (like "O'Neil"). We'll use SqlParameter instead to safely pass values to the database.

Here's the corrected save button code:

private void btnSave_Click(object sender, EventArgs e)
{
    string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename= C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Library System Project.mdf ;Integrated Security=True;Connect Timeout=30";
    
    // Use a parameterized query to avoid SQL injection and formatting errors
    string query = "INSERT INTO Books (BookName, BookAuthor, CategoryID, ClassificationID, BookAvailabilityQuantity, Price) " +
                   "VALUES (@BookName, @BookAuthor, @CategoryID, @ClassificationID, @BookAvailabilityQuantity, @Price);";

    // Use 'using' statements to automatically clean up database resources (best practice)
    using (SqlConnection dbCon = new SqlConnection(connectionString))
    using (SqlCommand dbCommand = new SqlCommand(query, dbCon))
    {
        // Add parameters with values from your form controls
        dbCommand.Parameters.AddWithValue("@BookName", txtName.Text.Trim());
        dbCommand.Parameters.AddWithValue("@BookAuthor", txtAuthor.Text.Trim());
        dbCommand.Parameters.AddWithValue("@CategoryID", (int)comboBoxCategory.SelectedValue);
        dbCommand.Parameters.AddWithValue("@ClassificationID", txtClassification.Text.Trim());
        dbCommand.Parameters.AddWithValue("@BookAvailabilityQuantity", int.Parse(txtAvailabilityQuantity.Text.Trim())); // Convert to int for quantity
        dbCommand.Parameters.AddWithValue("@Price", decimal.Parse(txtPrice.Text.Trim())); // Convert to decimal for currency

        try
        {
            dbCon.Open();
            // Use ExecuteNonQuery for INSERT/UPDATE/DELETE (we don't need a DataReader here)
            int rowsAffected = dbCommand.ExecuteNonQuery();
            if (rowsAffected > 0)
            {
                MessageBox.Show("New book record added to the system.", "Library System", MessageBoxButtons.OK);
                ResetFormFields(); // Clear the form after successful save
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error adding book: {ex.Message}");
        }
        // No need to manually close the connection—'using' does this automatically
    }
}

// Helper method to reset all form fields
private void ResetFormFields()
{
    txtName.ResetText();
    txtAuthor.ResetText();
    comboBoxCategory.SelectedIndex = 0; // Reset to first category
    txtClassification.ResetText();
    txtAvailabilityQuantity.ResetText();
    txtPrice.ResetText();
}

Key Improvements Here: #

• Parameterized Queries: Eliminates SQL injection risks and fixes issues with special characters in user input.
• Using Statements: Automatically cleans up database connections/commands to prevent resource leaks.
• ExecuteNonQuery: The right method for INSERT operations (we don't need a SqlDataReader since we're not reading data back).
• Type Parsing: Converts quantity and price to their correct data types (int and decimal) to match your database columns.

Full Modified Form Code #

Here's how your complete form class should look with all fixes applied:

public partial class AddBookRecordForm : Form
{
    public AddBookRecordForm()
    {
        InitializeComponent();
        PopulateCategoryComboBox();
    }

    enum BookCategory
    {
        Comics = 1, Crafts = 2, Education = 3, History = 4, Entertainment = 5,
        Thriller = 6, Religion = 7, Romance = 8, Fantasy = 9, Sports = 10
    }

    private void PopulateCategoryComboBox()
    {
        foreach (BookCategory category in Enum.GetValues(typeof(BookCategory)))
        {
            int categoryId = (int)category;
            string categoryName = category.ToString();
            comboBoxCategory.Items.Add(new { Text = $"{categoryId} - {categoryName}", Value = categoryId });
        }

        comboBoxCategory.DisplayMember = "Text";
        comboBoxCategory.ValueMember = "Value";

        if (comboBoxCategory.Items.Count > 0)
        {
            comboBoxCategory.SelectedIndex = 0;
        }
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
        string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename= C:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\Library System Project.mdf ;Integrated Security=True;Connect Timeout=30";
        
        string query = "INSERT INTO Books (BookName, BookAuthor, CategoryID, ClassificationID, BookAvailabilityQuantity, Price) " +
                       "VALUES (@BookName, @BookAuthor, @CategoryID, @ClassificationID, @BookAvailabilityQuantity, @Price);";

        using (SqlConnection dbCon = new SqlConnection(connectionString))
        using (SqlCommand dbCommand = new SqlCommand(query, dbCon))
        {
            dbCommand.Parameters.AddWithValue("@BookName", txtName.Text.Trim());
            dbCommand.Parameters.AddWithValue("@BookAuthor", txtAuthor.Text.Trim());
            dbCommand.Parameters.AddWithValue("@CategoryID", (int)comboBoxCategory.SelectedValue);
            dbCommand.Parameters.AddWithValue("@ClassificationID", txtClassification.Text.Trim());
            dbCommand.Parameters.AddWithValue("@BookAvailabilityQuantity", int.Parse(txtAvailabilityQuantity.Text.Trim()));
            dbCommand.Parameters.AddWithValue("@Price", decimal.Parse(txtPrice.Text.Trim()));

            try
            {
                dbCon.Open();
                int rowsAffected = dbCommand.ExecuteNonQuery();
                if (rowsAffected > 0)
                {
                    MessageBox.Show("New book record added to the system.", "Library System", MessageBoxButtons.OK);
                    ResetFormFields();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error adding book: {ex.Message}");
            }
        }
    }

    private void ResetFormFields()
    {
        txtName.ResetText();
        txtAuthor.ResetText();
        comboBoxCategory.SelectedIndex = 0;
        txtClassification.ResetText();
        txtAvailabilityQuantity.ResetText();
        txtPrice.ResetText();
    }
}

Quick Tips for New Developers #

• Always use parameterized queries for database work—never concatenate user input into SQL strings.
• using statements are essential for objects that use system resources (like database connections) because they automatically clean up those resources.
• When pairing Enums with ComboBoxes, DisplayMember and ValueMember let you show friendly text to users while keeping the underlying data (like CategoryID) accessible for your code.

内容的提问来源于stack exchange，提问作者avner1169