DataGridView多列筛选时前序条件失效，如何实现递进叠加过滤效果

核心问题 #

你当前的每个筛选方法均独立对数据库全表执行单条件查询，每次触发筛选都会覆盖上一次的数据源，因此无法保留之前的筛选条件。

推荐实现方案（小数据量场景，性能最优） #

一次性拉取全表数据到本地内存，通过DataView.RowFilter实现多条件叠加筛选，无需每次筛选都请求数据库。

步骤1：修改全局变量与窗体加载逻辑 #

SqlConnection baglan = new SqlConnection("Data Source=DESKTOP-27K77RI\\WINCC;Initial Catalog=lazer;Integrated Security=True");
// 新增：存储全量原始数据
DataTable originalTable = new DataTable();

private void CWARA_Load(object sender, EventArgs e)
{
    // 窗体加载时一次性拉取全量数据
    string query = "Select Beğeni,[1.Malz],[2.Malz],[3.Malz],KaynakTürü FROM CW";
    using (SqlCommand command = new SqlCommand(query, baglan))
    {
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            adapter.Fill(originalTable);
        }
    }
    cwGrid.DataSource = originalTable;
    // 可以删掉原来的四个search方法调用
}

步骤2：新增统一筛选方法 #

private void UpdateGridFilter()
{
    List<string> filterConditions = new List<string>();

    // 收集四个输入框的非空筛选条件
    if (!string.IsNullOrWhiteSpace(comboBox1.Text))
    {
        // 替换单引号避免语法错误
        filterConditions.Add($"[1.Malz] LIKE '%{comboBox1.Text.Replace("'", "''")}%'");
    }
    if (!string.IsNullOrWhiteSpace(comboBox2.Text))
    {
        filterConditions.Add($"[2.Malz] LIKE '%{comboBox2.Text.Replace("'", "''")}%'");
    }
    if (!string.IsNullOrWhiteSpace(comboBox3.Text))
    {
        filterConditions.Add($"[3.Malz] LIKE '%{comboBox3.Text.Replace("'", "''")}%'");
    }
    if (!string.IsNullOrWhiteSpace(comboBox4.Text))
    {
        filterConditions.Add($"[KaynakTürü] LIKE '%{comboBox4.Text.Replace("'", "''")}%'");
    }

    DataView dataView = originalTable.DefaultView;
    // 拼接筛选条件，多条件自动用AND关联实现叠加
    dataView.RowFilter = filterConditions.Count > 0 ? string.Join(" AND ", filterConditions) : string.Empty;
    cwGrid.DataSource = dataView;
}

步骤3：修改所有下拉框的触发事件 #

将四个comboBox的TextChanged事件全部改为调用统一筛选方法，删掉原来的单条件筛选方法即可：

private void comboBox1_TextChanged(object sender, EventArgs e) => UpdateGridFilter();
private void comboBox2_TextChanged(object sender, EventArgs e) => UpdateGridFilter();
private void comboBox3_TextChanged(object sender, EventArgs e) => UpdateGridFilter();
private void comboBox4_TextChanged(object sender, EventArgs e) => UpdateGridFilter();

大数据量场景适配 #

如果表数据量超过10万条，不适合一次性拉取全量数据，可采用参数化SQL拼接方案，每次请求数据库时携带所有已输入的筛选条件，避免SQL注入风险：

private void UpdateGridFilterFromDB()
{
    List<string> whereConditions = new List<string>();
    List<SqlParameter> sqlParams = new List<SqlParameter>();

    if (!string.IsNullOrWhiteSpace(comboBox1.Text))
    {
        whereConditions.Add("[1.Malz] LIKE @val1");
        sqlParams.Add(new SqlParameter("@val1", $"%{comboBox1.Text}%"));
    }
    if (!string.IsNullOrWhiteSpace(comboBox2.Text))
    {
        whereConditions.Add("[2.Malz] LIKE @val2");
        sqlParams.Add(new SqlParameter("@val2", $"%{comboBox2.Text}%"));
    }
    if (!string.IsNullOrWhiteSpace(comboBox3.Text))
    {
        whereConditions.Add("[3.Malz] LIKE @val3");
        sqlParams.Add(new SqlParameter("@val3", $"%{comboBox3.Text}%"));
    }
    if (!string.IsNullOrWhiteSpace(comboBox4.Text))
    {
        whereConditions.Add("[KaynakTürü] LIKE @val4");
        sqlParams.Add(new SqlParameter("@val4", $"%{comboBox4.Text}%"));
    }

    string query = "SELECT Beğeni,[1.Malz],[2.Malz],[3.Malz],KaynakTürü FROM CW";
    if (whereConditions.Count > 0)
    {
        query += " WHERE " + string.Join(" AND ", whereConditions);
    }

    using (SqlCommand command = new SqlCommand(query, baglan))
    {
        command.Parameters.AddRange(sqlParams.ToArray());
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        {
            DataTable resultTable = new DataTable();
            adapter.Fill(resultTable);
            cwGrid.DataSource = resultTable;
        }
    }
}

所有下拉框事件改为调用UpdateGridFilterFromDB即可。

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