Winform C#：如何将数据查询与用户表单分离并优化字段获取

First off, kudos for thinking about separation of concerns early on—this is exactly the kind of habit that makes code maintainable and secure in the long run. Let’s break down how to shift that field-extraction logic out of your form and into your data access layer (or a small middle layer, if needed).

First: Fix That Critical SQL Injection Risk #

Before we get to the UI cleanup, let’s address the biggest issue in your current code: string concatenation in your SQL query. This exposes you to SQL injection attacks, which is a massive security hole. We’ll fix this with parameterized queries first—this is non-negotiable, even before you move to stored procedures.

Step 1: Refactor Your DataAccess Class #

Instead of returning a List<Customer> and making the UI guess which element to pick, have your data layer return a single Customer object (or null if no match is found). This puts the data-related logic where it belongs:

public class DataAccess {
    // Return a single Customer (or null if not found)
    public Customer GetCustomerByName(string name) {
        using (IDbConnection connection = new MySql.Data.MySqlClient.MySqlConnection(Helper.CnnVal("FineCreteDB"))) {
            // Use parameterized query to avoid SQL injection!
            // Add % around the name if you want fuzzy matching (like your original LIKE)
            var customer = connection.QueryFirstOrDefault<Customer>(
                "SELECT * FROM `Customers` WHERE `Cust_Name` LIKE @CustomerName",
                new { CustomerName = $"%{name}%" }
            );
            return customer;
        }
    }

    // Keep your existing AllCustomersList method
    public List<Customer> AllCustomersList() {
        // Your existing implementation here
    }
}

Step 2: Simplify Your Form’s RetrieveData Method #

Now your UI only needs to call the data layer, check if a customer was found, and use the object’s properties directly. No more manual list indexing or field extraction:

private void RetrieveData() {
    try {
        DataAccess db = new DataAccess();
        var customer = db.GetCustomerByName(CustomerNameTxtBox_AutoComplete.Text);

        if (customer == null) {
            // Explicitly handle "no customer found" case
            throw new InvalidOperationException("Customer not found");
        }

        // Directly use the customer object's properties—no messy extraction!
        ntn_num = customer.Cust_NTN;
        strn_num = customer.Cust_STRN;
        address = customer.Cust_Address;
        phone_num = customer.Cust_Phone;
        id_num = customer.Cust_ID;

        // Optional: Bind these values directly to form controls here
    }
    catch (InvalidOperationException ex) {
        MessageBox.Show($"{ex.Message}. If customer was recently added, try updating DB.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        ResetCustomerData();
    }
    catch (MySqlException ex) {
        // Handle database-specific errors (e.g., connection issues)
        MessageBox.Show($"Database error: {ex.Message}", "Database Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        ResetCustomerData();
    }
    catch (Exception ex) {
        // Catch-all for unexpected errors (keep this broad but informative)
        MessageBox.Show($"Unexpected error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        ResetCustomerData();
    }
}

// Helper method to avoid repeating reset logic
private void ResetCustomerData() {
    DataAccess db = new DataAccess();
    filteredcustomers = db.AllCustomersList();
    
    ntn_num = "";
    strn_num = "";
    address = "";
    phone_num = "";
    id_num = "";
}

Why This Works Better #

• Separation of Concerns: The data layer handles fetching and returning the right customer object. The UI only needs to use that object, not manipulate raw query results.
• Security: Parameterized queries eliminate SQL injection risks—your original code was vulnerable to attackers entering malicious text into the text box.
• Readability: The form code is now focused on UI logic, not data extraction. Anyone reading it immediately understands what’s happening.
• Robust Error Handling: By catching specific exceptions (like InvalidOperationException for missing customers, or MySqlException for database issues), you can give users more accurate feedback.

Bonus: For Future Scalability #

If your project grows, you might want to add a service layer (e.g., CustomerService) between your UI and data access layer. This layer would handle business rules (like validating customer names before querying) and keep your data access layer purely focused on database interactions. But for your current mini-project, the above refactor is perfect.

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