Refreshing data in DataGridView elegantly

Question

I have a DataGridView in a Windows Form, and this grid contains close to realtime data - I'd want it updated at minimum every 20 seconds. My grid is bound to a DataTable I generate programatically, so I currently have a timer that generates this DataTable and assigns it to the grid every 20 seconds.

But the issue I have is that when the data is refreshed, any selections or sorts on the grid disappear, which is a big usability issue. Is there any other ways to refresh the contents of the grid, preserving the selections/sorts?

Answer 1

A rough guide to restoring sort order and row selection.

Do this before the refresh:

// Store the sort details.
ListSortDirection oldSortOrder;
switch (uiGrid.SortOrder)
{
    case SortOrder.Ascending:
        oldSortOrder = ListSortDirection.Ascending;
        break;
    case SortOrder.Descending:
        oldSortOrder = ListSortDirection.Descending;
        break;
    default:
        oldSortOrder = ListSortDirection.Ascending;
        break;
}

DataGridViewColumn oldSortColumn = uiGrid.SortedColumn;

// Store the selected rows
List<String> selectedRows = new List<String>();
foreach (DataGridViewRow row in uiGrid.SelectedRows)
{
    selectedRows.Add(row.Cells["SomeIndexColumn"].Value.ToString());
}

Do this after the refresh:

// Restore the sort
uiGrid.Sort(oldSortColumn, oldSortOrder);

// Restore Selected rows
foreach (DataGridViewRow row in uiGrid.SelectedRows)
{

    if (selectedRows.Contains(row.Cells["SomeIndexColumn"].Value.ToString()))
    {
        row.Selected = true;
    }
}

Answer 2

You would need to save your selections and then reload the data and reapply the saved selections.

This is actually a fairly simple process, but I don't code in Winforms to give a complete example, but the procedure would be similiar to;

Loop through DataGridViewItems. Store the index and/or key and selection criteria.

Reload data.

Loop through DataGridViewItems. Retrieve stored selection criteria where index/key matches and apply.

Answer 3

Why do you refresh the data so often? Are there many changes? Inserts?

In case of inserts updating the grid with the new records only would suffice, by appending them to the row collection of the grid.

Answer 4

I like the answer from Martin. However I find after refreshing the data source, the variable oldSortColumn somehow changes and the data gridview does not accept it in "uiGrid.Sort(oldSortColumn, oldSortOrder)". Furthermore uiGrid.Sort does not like oldSortColumn as null. I appreciate if anyone can let me know why oldSortColumn changes. I decide to use the sorting column index rather than the sorting column itself. Therefore here is my slightly modified version:

Do this before the refresh:

public static void GetDataGridViewUIInfo(this DataGridView dgv, out List<int> selectedIndices,
  out int? sortColumnIndex, out ListSortDirection sortOrder)
{
  selectedIndices = dgv.GetSelectedRowIndices();
  dgv.GetSortInfo(out sortColumnIndex, out sortOrder);
}

static List<int> GetSelectedRowIndices(this DataGridView dgv)
{
  List<int> selectedIndices = new List<int>();

  foreach (DataGridViewRow row in dgv.SelectedRows)
  {
    selectedIndices.Add(row.Index);
  }

  return selectedIndices;
}

static void GetSortInfo(this DataGridView dgv, out int? sortColumnIndex, out ListSortDirection sortOrder)
{
  // Store the sort details
  switch (dgv.SortOrder)
  {
    case SortOrder.Ascending:
      sortOrder = ListSortDirection.Ascending;
      break;
    case SortOrder.Descending:
      sortOrder = ListSortDirection.Descending;
      break;
    default:
      sortOrder = ListSortDirection.Ascending;
      break;
  }

  sortColumnIndex = dgv.SortedColumn == null ? null : (int?)dgv.SortedColumn.Index;
}

Do this after the refresh:

public static void SetDataGridViewUIInfo(this DataGridView dgv, List<int> selectedIndices,
  int? sortColumnIndex, ListSortDirection sortOrder)
{
  dgv.SetSelectedRowIndices(selectedIndices);
  dgv.SetSortInfo(sortColumnIndex, sortOrder);
}

static void SetSelectedRowIndices(this DataGridView dgv, List<int> selectedIndices)
{
  if (dgv.Rows.Count <= 0)
    // Early out if there is no row in the data grid view
    return;

  foreach (DataGridViewRow row in dgv.Rows)
  {
    row.Selected = false;
  }

  foreach (int index in selectedIndices)
  {
    if (index < dgv.Rows.Count)
      dgv.Rows[index].Selected = true;
    else
      dgv.Rows[dgv.Rows.Count - 1].Selected = true;
  }

  if (selectedIndices.Count > 0 && selectedIndices[0] < dgv.Rows.Count && dgv.DataSource is BindingSource)
    ((BindingSource)dgv.DataSource).Position = selectedIndices[0];
}

static void SetSortInfo(this DataGridView dgv, int? sortColumnIndex, ListSortDirection sortOrder)
{
  if (sortColumnIndex == null)
    // Early out if there was no column used to sort in the data grid view
    return;

  // Restore the sort details
  dgv.Sort(dgv.Columns[(int)sortColumnIndex], sortOrder);
}

Use this to invoke the above functions:

List<int> selectedIndices;
ListSortDirection sortOrder;
int? sortColumnIndex;
DataGridView dgv;

dgv.GetDataGridViewUIInfo(out selectedIndices, out sortColumnIndex, out sortOrder);

// Refresh the data source of the data gridview

dgv.SetDataGridViewUIInfo(selectedIndices, sortColumnIndex, sortOrder);