Custom Sorting Of A Bound DataGridView

Question

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.

Answer 1

Try binding to a DataView, not a DataTable, e.g.:

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

You have a few choices of dealing with the N/A data - SELECT statement, RowPrePaint event, and more.

Answer 2

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

Answer 3

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

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

Answer 4

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.