Datasource not reflecting sorting in DataGridView

Question

I am binding a DataTable as the DataSource of a DataGridView. I enabled the SortMode property to Automatic for all DataGridViewColumn. But when I am sorting on DataGridView the Sort change is not being reflected in the underlying DataSource which is the DataTable.
For example:

DataTable table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Age", typeof(int));
table.Rows.Add("Alex", 27);
table.Rows.Add("Jack", 65);
table.Rows.Add("Bill", 22);
dataGridView1.DataSource = table;

Now if I was to get the selected row like below it will always return the row at 0 Index in the DataTable even after sorting which is Alex, 27.

DataRow newRow = table.NewRow();
newRow.ItemArray = table.Rows[dataGridView1.Rows.IndexOf(dataGridView1.SelectedRows[0])].ItemArray.Clone() as object[];

Can someone please suggest how I should go about this situation?

Solution 1

First of all the DataTable object is detached from DataGridView even if you bind it to DataSource, that is, if you change your DataGridView it will not affect your DataTable. And in your case you want the sorting in DataGridView to be reflected in DataTable. And so for that reason you need to catch an event every time there is changes in ordering/sorting in DataGridView. You need to catch therefore for that regard the ColumnHeaderMouseClick event of the DataGridView.

Another important thing is, in achieving a synchronize sorting of both DataTable and DataGridView is the DefaultView method of Datable class that has the Sort property. So, what we are going to do is every time the DataGridView is changed sorting-wise we will sort the DataTable also.

First of all we need to make a DataTable object global so that we could access it anywhere later on.

DataTable table;

Secondly, we need to initialize an event listener for ColumnHeaderMouseClick and for our practical purpose we will set it at Form constructor.

InitializeComponent();
dataGridView1.ColumnHeaderMouseClick += new DataGridViewCellMouseEventHandler(dataGridView1_ColumnHeaderMouseClick);

We then have this empty Mouse event handler:

 void dataGridView1_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
 {            

 }

And if the above method doesn't automatically come out, just copy and paste in your code.

And for the sake of illustration we will add the DataTable during the Form Load and in this case I am going to use your own code:

table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Age", typeof(int));
table.Rows.Add("Alex", 27);
table.Rows.Add("Jack", 65);
table.Rows.Add("Bill", 22);
dataGridView1.DataSource = table;

And then finally we will put some code in the ColumnHeaderMouseClick event:

  void dataGridView1_ColumnHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
  {            
      if (dataGridView1.SortOrder.ToString() == "Descending") // Check if sorting is Descending
      {
          table.DefaultView.Sort = dataGridView1.SortedColumn.Name + " DESC"; // Get Sorted Column name and sort it in Descending order
      }
      else
      {
        table.DefaultView.Sort = dataGridView1.SortedColumn.Name + " ASC";  // Otherwise sort it in Ascending order
      }
      table = table.DefaultView.ToTable(); // The Sorted View converted to DataTable and then assigned to table object.
  }

You could now use table object and sorted according to the sorting order of the DataGridView.

Just to confirm my claim, we will make a button in your form named button1 and when clicked it will show the first row and the sorted column value, like:

  private void button1_Click(object sender, EventArgs e)
  {
    String sortedValue = dataGridView1.SortedColumn.Name == "Name" : table.Rows[0][0].ToString() ? table.Rows[0][1].ToString();
     MessageBox.Show(sortedValue);
  }

Solution 2

You can put your DataTable into BindingSource container and set this class to data source of DataGridView.

BindingSource bindingSource1 = new BindingSource();
bindingSource1.DataSource = dataTable;
dataGridView1.DataSource = bindingSource1;
// another grid view options...

To learn about BindingSort, see this link.

Solution 3

For others coming here for same reason as I.

I couldn't get the underlying data sorted either, thus when I was deleting a row, it was deleting the wrong one.

I found DataTable will not be sorted but only DataTable.DefaultView will be.

So instead of DataRow row = dataTable.Rows[0];

You would do DataRow row = dataTable.DefaultView[0].Row;

Solution 4

Old question, but still no easy solution mentioned, so I post it here for the people looking for the answer nowadays:

DataRow row = ((DataRowView)yourDataGridView.SelectedRows[0].DataBoundItem).Row;
int index = yourDataTable.Rows.IndexOf(row);

index will be the index of the row in your DataTable corresponding with the selected row in DataGridView, however you have sorted it.

View more solutions

Comments

[lbrahim]

So, just wrapping my DataTable to a BindingSource enables sorting from DataGridView to the DataTable? What is BindingSort btw?

[lbrahim]

I did like you said. Still no automatic sorting on the datatable.

[Edper]

@Md.Ibrahim check my answer above.

[ghiboz]

yes, but if you change the DataGridView sort order, the BindingSource reflect this change...