如何实现基于多文本框的SQL LIKE模糊查询？（附按钮代码）

Hey there! Let's get your multi-textbox fuzzy search working properly, and we'll also fix a critical security issue in your current code along the way.

First, let's address the big problem in your existing code: direct string concatenation of user input into SQL queries is extremely dangerous because it opens you up to SQL injection attacks. We'll use parameterized queries instead—this is non-negotiable for production code.

Here's how to implement multi-textbox LIKE search safely:

Step-by-Step Implementation #

1. Build a dynamic WHERE clause: Only include conditions for textboxes that have user input (so empty textboxes don't filter results).
2. Use parameterized queries: Each textbox input maps to a named SQL parameter to avoid injection.
3. Properly manage database connections: Use using statements to ensure connections are disposed correctly.

Full Code Example #

Let's assume you have additional textboxes like txtLastName and txtStudentID (adjust the names to match your actual controls):

private void btnStudentLookup_Click(object sender, EventArgs e)
{
    string strConnect = "Server=DESKTOP-2Q73COU\\SQLEXPRESS;Database=LoginApp;Trusted_Connection=True;";
    
    // Use a single connection wrapped in using to handle disposal
    using (SqlConnection conn = new SqlConnection(strConnect))
    {
        // Build the base SQL query
        StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM Main_Information WHERE 1=1");
        SqlCommand command = new SqlCommand();
        command.Connection = conn;

        // Add condition for First Name if textbox isn't empty
        if (!string.IsNullOrWhiteSpace(txtFirstName.Text))
        {
            sqlBuilder.Append(" AND [First Name] LIKE @FirstName");
            command.Parameters.AddWithValue("@FirstName", $"%{txtFirstName.Text}%");
        }

        // Add condition for Last Name (adjust control name to match yours)
        if (!string.IsNullOrWhiteSpace(txtLastName.Text))
        {
            sqlBuilder.Append(" AND [Last Name] LIKE @LastName");
            command.Parameters.AddWithValue("@LastName", $"%{txtLastName.Text}%");
        }

        // Add condition for Student ID (if applicable)
        if (!string.IsNullOrWhiteSpace(txtStudentID.Text))
        {
            sqlBuilder.Append(" AND [Student ID] LIKE @StudentID");
            command.Parameters.AddWithValue("@StudentID", $"%{txtStudentID.Text}%");
        }

        // Assign the final SQL to the command
        command.CommandText = sqlBuilder.ToString();

        conn.Open();
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataTable dt = new DataTable();
        adapter.Fill(dt);
        
        // Bind the DataTable to your UI control (e.g., DataGridView)
        dataGridView1.DataSource = dt;
    }
}

Key Notes #

• WHERE 1=1 trick: This lets us easily append AND [Condition] clauses without having to check if it's the first condition (avoids messy logic for leading WHERE vs AND).
• Parameterization: Each user input is passed as a parameter, so SQL treats it as a literal value instead of executable code—no more injection risks.
• Empty textboxes: If a textbox is empty, we skip its condition entirely, so it doesn't affect the search results.
• Connection management: The using statement ensures the connection is closed and disposed even if an error occurs.

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