R – Custom Sorting Of A Bound DataGridView

datagridviewnetsorting

I have a DataGridView bound to a DataTable. I have one column that's a pseudo-int — you know the kind, where most of the time it has integers but sometimes instead there's an N/A. This column is a varchar, but I want to have it sort like an int column, treating the N/A as a -1.

The DataGridView provides for this — if it's not bound to a DataTable. If it is bound, it uses the sorting mechanism of the bound object, and DataTables don't expose that functionality.

I can make a custom column in the DataTable with the behaviour I want, but because the DataGridView is bound to the DataTable, it sorts by the column it's displaying. I can make a custom column in the DataGridView, but I need to set the table to virtual mode to sort by that when I already have a solution that mostly works.

How do I make it sort my pseudo-int column as I want – where possible, sorting by int? This scenario seems like it's incredibly common, and I'm sure somewhere it's been provided for.

Best Answer

When I've had to deal with sorting problems similar to 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's binding you can simply hide that column of data, but because it's there you can still sort on it. It adds a little extra data to memory to do this, so depending on the performance hit and the size of your sorted data, this could be a potential issue. Also, anytime the data is modified, you'd need to make sure this extra column is kept in line.