CODEWITHSUNDEEP
.netsortingdatagridview
Merus
Merus

Reputation: 8984

Custom Sorting Of A Bound DataGridView

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.

Upvotes: 4

Views: 4615

Answers (5)

John
John

Reputation: 2792

Along with Joel Etherton's answer there's an event that you can handle that will let you override the displayed value for a cell.

So, like he said, you'll want two columns. One is a strongly-typed numeric column with -1s for the text values. The other is a hidden text column with the real values (mixed numerics and text values). As-is it will sort properly, 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 though the value in the bound DataSource is just a numeric.

If you're going to do any filtering on the DataView behind you DataGridView, make sure you filter on the hidden column.

Upvotes: 0

Joel Etherton
Joel Etherton

Reputation: 37533

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.

Upvotes: 3

Adam Houldsworth
Adam Houldsworth

Reputation: 64467

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

Upvotes: 0

Sorin Comanescu
Sorin Comanescu

Reputation: 4867

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?

Upvotes: 1

gimel
gimel

Reputation: 86344

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.

Upvotes: 1

Related Questions