Custom sorting of bound DataGridView

Question

I have a DataGridView associated with a DataTable. I have one column that is pseudo-int - you know what type, where most of the time it has integers, but sometimes there is N / A instead. This column is varchar, but I want it to look like an int column, treating N / A as -1.

DataGridView provides this - if it is not bound to a DataTable. If it is bound, it uses the sorting mechanism of the linked object, and DataTables does not reveal this functionality.

I can create a custom column in a DataTable with the behavior I want, but since the DataGridView is bound to a DataTable, it is sorted by the column that it displays. I can create a custom column in a DataGridView, but I need to set up the table in virtual mode to sort it when I already have a solution that basically works.

How do I sort it by my pseudo-int column, how do I want - where possible, sorting by int? This scenario seems unbelievable, and I'm sure it was provided somewhere.

Answer 1

When I had to deal with sorting problems like this, my favorite method is to add a column to the DataTable and parse the pseudo-int into the sortable int that I want. Then, in the DataGridView binding, you can just hide this data column, but because there you can sort it anyway. It adds a little extra data to memory to do this, so this may be a potential problem depending on the performance and size of your sorted data. In addition, at any time when the data is changed, you need to make sure that this additional column is supported in the queue.

Answer 2

Try binding to a DataView, not a DataTable, for example:

private void SortByTwoColumns() { DataView myDataView = DataTable1.DefaultView; myDataView.Sort = "State, ZipCode DESC"; myGridView.DataSource = myDataView; } 

You have several options for working with N / A data - SELECT, RowPrePaint event, etc.

Answer 3

Why don't you just modify the SQL query to return this column as int (-1 for "N / A"), and then use a custom formatter for the displayed column?

Answer 4

Perhaps you can adapt the code in your blog post to provide IComparer specific to your int column:

http://adamhouldsworth.blogspot.com/2010/01/bound-datagridview-sorting.html

Answer 5

Along with Joel Etherton, answer that an event you can handle will allow you to override the displayed value for the cell.

So, as he said, you will need two columns. One is a strongly typed numeric column with -1s for text values. The other is a hidden text column with real values ​​(mixed numbers and text values). As-it will sort correctly, but the user will see -1s instead of the actual value.

To fix this, we need this code:

 Private Sub dgDisplay_CellFormatting(ByVal sender As Object, _ ByVal e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) _ Handles dgDisplay.CellFormatting If Not (e.ColumnIndex = dgDisplay.Columns("NumericColumn").Index _ AndAlso e.RowIndex >= 0) Then Exit Sub Dim newVal As String = dgDisplay.Item("ActualColumn", e.RowIndex).Value e.Value = newVal End Sub 

This will show the actual text value you want to see, even if the value in the DataSource is only numeric.

If you intend to filter on a DataView behind you a DataGridView , make sure you filter the hidden column.