When DataGridView is sorted, how do I update the data source to reflect the DataGridView's binding?

Question

I have these members on my DataGridView.

private DataTable _dataTable = new DataTable();
    
public DataTable DataTable {
    get { return _dataTable; }
    set {
        _dataTable = value;
        int rowIndex = -1;
        //want to preserve the location where the datagridview is scrolled to
        if (this.Rows.Count > 0) rowIndex = this.FirstDisplayedScrollingRowIndex;
        this.DataSource = new BindingSource() { DataSource = _dataTable };
        //-1 is out of bounds and the datasource could have been set
        //to a smaller amount of rows after the rowIndex was set
        if (rowIndex > -1 && rowIndex < _dataTable.Rows.Count) this.FirstDisplayedScrollingRowIndex = rowIndex;
    }
}

When rows are added, removed or edited they are done so in _dataTable. When the rows are changed, DataTable is set so that the DataGridView displays the changes by binding the data to it. My problem is when the DataGridView is sorted by clicking one of the column headers. This changes the display of the DataGridView, but not the order of the rows in the underlying DataTable. How can I get it so _dataTable is sorted like the DataGridView?

I have considered adding a column to keep track of row indexes, but it seems like there should be another way and I don't really want to add a column since this is an abstract class. I have tried handling it in the DataGridView.Sorted events but haven't come up with any solutions that would work.

Answer 1

There is no way to transfer the sorting of the view into the table - not any that are provided by the DataGridView or DataTable classes. Why would you want to? If you are sorting based on a column, that column is in the table, and will be in the table any time you want to iterate through or sort the table in the order of that column.

If you want to persist the user's choice of sort column for the view, that is a different kettle than trying to save the specific sort position for each row. I would recommend just saving what column the user chose. This would not belong in that same table, but in a separate settings storage. If you absolutely want to store the sort position of each row, then you do need a separate column that has the sort order for each row, and you will need to capture the header mouse click event and repopulate that column appropriately.

Also, the minute you add a sort-order column to that table, that is dependent on what column the user chose to sort the data, you de-normalize the data. This means you will run into problems of maintaining data integrity - you will have to capture all sort of events and re-update that column... for example, what if the user changes the value for a row in the middle of the table, such that it now belongs at the end... update the value of that sort order column for that row and every row between its old and new position. What if the user deletes a row - update the value of the sort order column for all rows between the deleted row and the last row of the table.... and on and on with all sorts of user operations.

Much better, store the column name in a setting, and whenever the table is loaded into the gridview, tell the gridview to sort on the column stored in that setting. I am not sure if I am being clear - sorry.