Custom Sorting of a DataSet Column

Question

I have a DataSet that contains a few columns. One of these columns is a number - most of the time. Because it's occasionally a string, in the database it's a varchar(10) column.

However, when you sort a varchar column, it sorts it like a string. What I want to do instead is to try and override this somehow so that it sorts the integers like integers; this isn't all that hard, and I've already got a function that does this elsewhere in my code. However, I don't think it's possible to give a typed DataSet like I have a custom type with its own sorting implementation, and from what I can see BindingSource doesn't think about columns at all, which makes it awful hard to sort on them. I can easily do it using the ListView/DataGridView sorting functionality -- but I'd like the display to be in virtual mode because of the quantity of data I have, and for that I need to provide my own sorting anyway.

Is there any way to do what I want to do?

Answer 1

I think your best bet is to add a new calculated column which converts the varchar(10) to an int, and sort on that.

myDataTable.Columns.Add("Sorter", typeof(System.Int32), "Convert(TextColumn, 'System.Int32')");

This will throw exceptions when the string in your varchar column (which I've referred to as "TextColumn" in the code above), but you may be able to work around that using an Iif() function call in the expression.