Save changes in binded DataGridView with filter active

Question

I have a DataGridView which is filled by a BindingSource. Everytime that the user modifys a row and he/she moves to another row (Everytime the row modified lose the focus) it triggers the event RowValidated.
In this event, the elements of the DataGridView.CurrentRow are modified in the SQLServer with a stored procedure.

When the BindingSource have a filter active this approach doesn't. Because if is modified the cell of the column which is filtered, the row dissapers (Because of the filter) and my method catch the DataGridView.CurrentRow as the next one.

I cannot use the method TableAdapter.Update() because the DataSet is a join of multiples tables and it doesn't autogenerates.

Is there any event that is triggerred before the effects of the filter? I would accept as answer any different approach.

Edit: When a cell is modified, is saved in a List the ID value of that row. The code in RowValidated is encapsulated in
if(ListOfIds.Contains(DataGridView.CurrentRow.Cells["ID"])){StoredProcedure();}

Answer 1

It is seldom a good idea to read and write the values of the cells yourself. Using DataBinding is usually an easier method.

It seems that you already use a BindingSource, but for some reason you read the changed row from the displayed data, not from the original BindingSource.

I'm not sure if it is wise to update your DataBase as soon as the operator moves to a new row. What if he doesn't know the value to fill in, and wants to scroll down to check another row? Consider to change the interface such that the operator can indicate that he finished editing all data. For instance a button. But this is a bit outside your question.

If you put your data in a BindingList, and assign this data to the DataSource of your DataGridView, then every change of the data in the DataGridView is automatically updated in the BindingList. Use event BindingList.ListChanged to get notified, or use the above mentioned button before processing the changed data.

BindingList<Customer> DisplayedCustomers {get; } = new BindingList<Customer>();

public MyForm()
{
    this.InitializeComponents();

    this.DataGridView1.DataSource = this.DisplayedCustomers();
    this.BindingListView.ListChanged += BindingListChanged;
}

An empty DataGridView is shown. To fill it with Customers:

private InitializeDataGridView()
{
    IEnumerable<Customer> customers = this.QueryCustomers();
    foreach (var customer in Customers)
        this.Customers.Add(customer);
}

This will automatically show all Customers in your DataGridView. Alas you will get the event once per customer. If you don't want that, consider to create a new BindingList and assign that to the DataSource of the DataGridView. Don't forget to unsubscribe the old BindingList and to subscribe to the event of the new BindingList

private void BindingListChanged(object sender, ListChangedEventArgs e)
{
     BindingList<Customer> bindingList = (BindingList<Customer>)sender;
     // use e.ListChangedType to detect what has changed:
     // item added / removed / moved to a new location / value changed?

     switch (e.ListChangedType)
     {
         case ListChangedType.ItemChanged:
             var changedCustomer = bindingList[e.NewIndex];
             ProcessChangedCustomer(changeCustomer);
             break;
         case ...
    }
}

The advantage of using the BindingList instead of fiddling with the values of the cells of the DataGridView, is that you decouple the data from how this data is communicated with the operator. This makes it easier to reuse (show it in a ComboBox instead of a DataGridView? Go ahead, changes are minimal), easier to unit test: BindingList also works if there is no form; and therefore easier to maintain and change.

One final advice: to facilitate filtering on properties (show only the Customers that live in New York), and to make sorting easy, consider to use nuget package Equin.ApplicationFramework.BindingListView

List<Customer> customers = GetCustomers();
BindingListView<Customer> view = new BindingListView<Customer>(customers);
dataGridView1.DataSource = view;

And Bingo: free sorting by mouse click the column header. It even remembers whether to sort ascending or descending and it shows the sorting glyph (the arrow that indicates the sorting direction).

Filtering:

void FilerCustomersByCity(string city)
{
    view.ApplyFilter(delegate(Customer) customer => customer.City == city);
}