如何用两个ComboBox筛选DataGridView？附现有单条件筛选代码

Hey there! Let's break this down step by step so you understand how to add the section filter and why it works.

First, the core idea here is that you don't need to filter the DataGridView after the first query—instead, you can build a single SQL query that combines both your year and section filters. This is more efficient (especially with large datasets) and keeps your code cleaner than filtering rows in memory after loading them.

Your current code handles the year filter by splitting into two cases ("All Students" vs specific year). We can extend this to include the section filter by dynamically building the SQL WHERE clause based on both ComboBox selections.

Revised Code with Dual Filter Support #

Here's the updated method that handles both filters, with explanations embedded:

private void LoadFilteredStudents()
{
    guna2DataGridView1.Rows.Clear();
    int rowNumber = 0;

    // Base SQL query with fixed condition (only active students)
    string sqlQuery = @"SELECT s.studentID, s.studentNo, s.Lname, s.Fname, s.MI, s.gender, s.yearLevel, s.section, s.studImage, 
                               g.name, g.contactNo, g.address 
                        FROM Student s 
                        INNER JOIN Guardian g ON g.studentNo = s.studentNo 
                        WHERE s.isActive = 'true'";

    // Create a SQL command with the base query
    using (SqlCommand cm = new SqlCommand(sqlQuery, cn))
    {
        // Add year filter if user didn't select "All Students"
        if (cboYear.Text != "All Students")
        {
            sqlQuery += " AND s.yearLevel = @yearLevel";
            cm.Parameters.AddWithValue("@yearLevel", cboYear.Text);
        }

        // Add section filter if user didn't select "All Sections" (adjust this text if your ComboBox uses a different label)
        if (cboSection.Text != "All Sections")
        {
            sqlQuery += " AND s.section = @section";
            cm.Parameters.AddWithValue("@section", cboSection.Text);
        }

        // Update the command with the dynamically built query
        cm.CommandText = sqlQuery;

        try
        {
            cn.Open();
            using (SqlDataReader dr = cm.ExecuteReader())
            {
                while (dr.Read())
                {
                    rowNumber++;
                    guna2DataGridView1.Rows.Add(
                        rowNumber,
                        dr["studentID"].ToString(),
                        dr["studentNo"].ToString(),
                        dr["Lname"].ToString(),
                        dr["Fname"].ToString(),
                        dr["MI"].ToString(),
                        dr["gender"].ToString(),
                        dr["yearLevel"].ToString(),
                        dr["section"].ToString(),
                        dr["name"].ToString(),
                        dr["contactNo"].ToString(),
                        dr["address"].ToString(),
                        dr["studImage"]
                    );
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show($"Error loading students: {ex.Message}");
        }
        finally
        {
            // Ensure the connection always closes, even if an error occurs
            if (cn.State == ConnectionState.Open)
                cn.Close();
        }
    }
}

Key Principles Explained #

1. Dynamic WHERE Clause
   We start with a base condition (only active students), then add year and section filters only if the user hasn't selected the "All" option for that ComboBox. This lets the query adapt to any combination of selections:

   • "Grade 1" + "Section A"
   • "All Students" + "Section B"
   • "Grade 3" + "All Sections"

2. Parameterized Queries
   Instead of directly inserting ComboBox text into the SQL string (which is risky for SQL injection attacks), we use placeholders like @yearLevel and @section. The database safely handles inserting the actual values, so you don't have to worry about special characters or malicious input.

3. Cleaner, More Maintainable Code
   We removed duplicate data-reading logic (no more repeating the while (dr.Read()) block twice) and added a finally block to guarantee the database connection closes, even if an error happens during the query.

Trigger the Filter on Both ComboBoxes #

Make sure to call this new method from both ComboBoxes' SelectedIndexChanged events so filtering updates whenever the user changes either selection:

private void cboYear_SelectedIndexChanged(object sender, EventArgs e)
{
    LoadFilteredStudents();
}

private void cboSection_SelectedIndexChanged(object sender, EventArgs e)
{
    LoadFilteredStudents();
}

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