如何实现多列不同值查询及多表格列过滤的搜索按钮功能？

Hey there! Let's tackle this multi-column filtering feature you're building. From what you've shared, you've got separate queries for LastName, FirstName, and DriverID—now we just need to combine them into a single flexible query that can filter across all three columns. Here's how to do it properly, with parameterization (super important to avoid SQL injection!) and clean code:

1. Pick Your Filtering Logic #

First, decide how you want the search to behave:

• Option 1: Match the search term in ANY column (e.g., typing "Smith" returns rows where LastName is Smith, OR FirstName is Smith, OR DriverID includes "Smith" if it’s a numeric ID converted to string)
• Option 2: Match specific values for individual columns (e.g., filter by LastName="Smith" AND FirstName="John" AND DriverID=123, ignoring empty inputs)

Most general search buttons use Option 1, so we’ll start with that, but I’ll include both for flexibility.

2. Updated Parameterized SQL Queries #

Instead of separate single-column queries, we’ll write one query that checks all three columns. Parameterization is non-negotiable here—it keeps your code secure from SQL injection.

For Option 1 (Single Search Input, Match Any Column):

SELECT LastName, FirstName, DriverID 
FROM [Driver Table] 
WHERE LastName LIKE @searchTerm 
   OR FirstName LIKE @searchTerm 
   OR CAST(DriverID AS VARCHAR(20)) LIKE @searchTerm

The CAST converts numeric DriverID to a string so you can do fuzzy matches (e.g., searching "12" finds DriverID 123). If you only need exact matches for DriverID, replace that line with DriverID = @driverID and add a separate parameter.

For Option 2 (Separate Inputs for Each Column):

SELECT LastName, FirstName, DriverID 
FROM [Driver Table] 
WHERE (@lastName IS NULL OR LastName = @lastName)
  AND (@firstName IS NULL OR FirstName = @firstName)
  AND (@driverID IS NULL OR DriverID = @driverID)

This lets you filter by any combination of columns—if an input is empty, that condition is ignored entirely.

3. Full C# Implementation #

Let’s wrap this in clean, resource-safe code using using statements (they auto-dispose connections/commands to avoid leaks):

Option 1 Code (Single Search Box):

// Get the search term from your UI input (e.g., a TextBox)
string userSearchTerm = txtGlobalSearch.Text.Trim();
// Add wildcards for fuzzy partial matches
string parameterValue = $"%{userSearchTerm}%";

// Use using statements to ensure resources are cleaned up
using (SqlConnection con = new SqlConnection("Data Source=PC-PC\\MIKO; Initial Catalog=Caproj; Integrated Security=True;"))
{
    con.Open();
    string qry = @"SELECT LastName, FirstName, DriverID 
                   FROM [Driver Table] 
                   WHERE LastName LIKE @searchTerm 
                      OR FirstName LIKE @searchTerm 
                      OR CAST(DriverID AS VARCHAR(20)) LIKE @searchTerm";
    
    using (SqlCommand cmd = new SqlCommand(qry, con))
    {
        // Add the parameter to avoid SQL injection
        cmd.Parameters.AddWithValue("@searchTerm", parameterValue);
        
        // Load results into a DataTable for UI binding
        DataTable driverResults = new DataTable();
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            adapter.Fill(driverResults);
        }
        
        // Bind to your UI control (e.g., DataGridView)
        dgvDriverList.DataSource = driverResults;
    }
}

Option 2 Code (Separate Column Inputs):

// Get values from individual input fields (set to null if empty)
string lastNameFilter = txtLastName.Text.Trim() == "" ? null : txtLastName.Text.Trim();
string firstNameFilter = txtFirstName.Text.Trim() == "" ? null : txtFirstName.Text.Trim();
int? driverIDFilter = string.IsNullOrWhiteSpace(txtDriverID.Text) ? (int?)null : int.Parse(txtDriverID.Text);

using (SqlConnection con = new SqlConnection("Data Source=PC-PC\\MIKO; Initial Catalog=Caproj; Integrated Security=True;"))
{
    con.Open();
    string qry = @"SELECT LastName, FirstName, DriverID 
                   FROM [Driver Table] 
                   WHERE (@lastName IS NULL OR LastName = @lastName)
                     AND (@firstName IS NULL OR FirstName = @firstName)
                     AND (@driverID IS NULL OR DriverID = @driverID)";
    
    using (SqlCommand cmd = new SqlCommand(qry, con))
    {
        // Map parameters, use DBNull.Value for empty inputs
        cmd.Parameters.AddWithValue("@lastName", lastNameFilter ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@firstName", firstNameFilter ?? DBNull.Value);
        cmd.Parameters.AddWithValue("@driverID", driverIDFilter ?? DBNull.Value);
        
        DataTable driverResults = new DataTable();
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            adapter.Fill(driverResults);
        }
        
        dgvDriverList.DataSource = driverResults;
    }
}

4. Quick Best Practices #

• Never skip parameterization: Concatenating user input directly into SQL is a huge security risk—always use parameters like we did here.
• Handle empty inputs: For Option 2, using DBNull.Value ensures empty fields don’t break the query.
• Resource cleanup: using statements guarantee connections and commands are disposed even if an error occurs.

That should give you a fully working multi-column filter! Adjust the logic if you need exact matches only or other custom behavior.

内容的提问来源于stack exchange，提问作者D.Trump123