CODEWITHSUNDEEP
c#winformsdatagridviewfilterbindingsource
Chris Songer
Chris Songer

Reputation: 43

Filter a BindingSource based on the rows of another DataGridView

I have two DataGridViews in Winforms. DataGrid1 is connected to a table that contains a list of jobs that need to be completed. Once someone completes a job, it's entered into a separate table as completed, which is connected to DataGrid2.

I need to filter the binding source for DataGrid1 so that when a job shows up as completed in DataGrid2 it's filtered out of DataGrid1. The current code I'm using only filters the binding source by the last entry in DataGrid2 and I need it to filter by all of the entries.

How do I filter the BindingSource for DataGrid1 based on all values of the a column of DataGrid2? 

foreach (DataGridViewRow row in dataGrid2.Rows)
{
    DataGrid1BindingSource.Filter = 
        string.Format("ColumnName <> '{0}'", row.Cells[1].Value);
}

Here is an example of all jobs in a data table, then the first grid which contains incomplete jobs and the second grid which contains completed jobs. The jobs which should be shown in Incomplete grid, are those jobs which are not in Completed jobs grid:

 __________        ____________        ___________
| All Jobs |      | Incomplete |      | Completed |  
|――――――――――|      |――――――――――――|      |―――――――――――|  
| JobTitle |      | JobTitle   |      | JobTitle  |  
|――――――――――|      |――――――――――――|      |―――――――――――|  
| Job 1    |      | Job 1      |      | Job 3     |  
| Job 2    |      | Job 2      |      | Job 4     |  
| Job 3    |      |            |      |           |
| Job 4    |      |            |      |           |
 ‾‾‾‾‾‾‾‾‾‾        ‾‾‾‾‾‾‾‾‾‾‾‾        ‾‾‾‾‾‾‾‾‾‾‾

Upvotes: 4

Views: 2190

Answers (2)

Reza Aghaei
Reza Aghaei

Reputation: 125197

Before reading the answer, you should know if you don't have a bool field or something to detect which job is completed it's not a good design. You should have a single list of jobs. Then based on a bool field you should show incomplete jobs in first grid and completed jobs in second grid. Then the filter would be simply Completed = true and Completed = false.

Anyway, you can use IN in filter expression. It's enough to create a list of values which you want to use in filter, then create the filter this way:

var ids = this.dataGridView2.Rows.Cast<DataGridViewRow>()
                .Where(r => !r.IsNewRow)
                .Select(r => r.Cells[0].Value.ToString());
bs1.Filter = string.Format("Column1 NOT IN ({0})", string.Join(",", ids));

In the above example I supposed ids are int so for example "Column1 NOT IN (1,2,3)" will be the filter. For string ids, the filter would be "Column1 NOT IN ('a','b','c')". So you can change the select statement like below:

.Select(r => string.Format("'{0}'",r.Cells[0].Value.ToString()));

Upvotes: 4

lokusking
lokusking

Reputation: 7456

This snippet is pretty ugly, but it should give you a hint of what to do:

var colname = YOURGRIDTOFILTER.Columns[INDEXOFCOLUMNTOFILTER].HeaderText;
            var filterString = colname+" <> ";
            foreach (DataGridViewRow row in dataGrid2.Rows)
            {
                filterString += "'" + row.Cells[1].Value + "' OR "+colname+" <> ";
            }
            filterString = filterString.Substring(0, filterString.LastIndexOf("OR"));

Upvotes: 0

Related Questions