Convert to decimal and do OrderBy

Question

My data table contains all string columns but in some columns we are filling numeric values. when I do orderby on datatable of that numeric column, it is not ordering properly. before order my table looks

Name    Account Department
Kiran   1100    CSC
Subbu   900     CSC
Ram     500     CSC
Raj     800     CSC
Joy     400     CSC

after order it looks like

Name    Account Department
Kiran   1100    CSC
Joy     400     CSC
Ram     500     CSC
Raj     800     CSC
Subbu   900     CSC

My code:

public DataTable sortData(string columnName)
{
   DataTable dt1=new DataTable();
   return  dt1=dataMgr[DatabaseFileNames.ControlDatabase]["OrderedTableName"]
              .Select("Department='CSC'")
              .OrderBy(x=>!string.IsNullOrEmpty(x.Field<string>(columnName)))
              .CopyToDataTable();
}

Answer 1

Your OrderBy should look somewhat like this:

.OrderBy(x => double.Parse(x.Field<string>(columName)))

Of course that assumes that all your values are actually parsable as double.

If you need to have it work for non-numerical columns also, I don't think you'll get around having to differentiate based upon the column name. (I. e. you'd have to have some mapping between columnName and an IComparer.)

That might look somewhat like this:

IComparer comparer = null;
// instead of a switch/case you could also have a Dictionary<string, IComparer> for example...
switch(columName)
{
    case "Account": comparer = Comparer<double>.Default;
                    break;
    default: comparer = Comparer<string>.Default;
                    break;
}

// so your OrderBy would look like this:
.OrderBy(x => x, comparer)

The cleaner solution would definitely be having the correct data types for the columns in the table.

Answer 2

You can simply pad string containing int value with leading 0 for sorting to work properly:

.OrderBy(x => x.Field<string>(columnName)?.PadLeft(10, '0') ?? "0")

You will have to define size to be big enough (10?) and some value for null to sort it as you prefer (in case of using "0" they will appears first).

Answer 3

I thought about this quite some time and the solution i came up with is shockingly obvious: Essentially ordering is all about comparing - so why don't we just write a custom Comparer...

public class MyComparer : IComparer<string>
{
    private readonly string _columnName;
    public MyComparer(string columnName)
    {
        _columnName = columnName;
    }

    public int Compare(string leftValue, string rightValue)
    {
        double leftDouble, rightDouble;

        switch (_columnName)
        {
            case "Account":
                if (Double.TryParse(leftValue, out leftDouble) && Double.TryParse(rightValue, out rightDouble))
                {
                    return leftDouble.CompareTo(rightDouble);
                }
                else
                {
                    return String.Compare(leftValue, rightValue);   
                }
            default:
                return String.Compare(leftValue, rightValue);
        }
    }
}

...and then simply add it to the OrderBy...

public DataTable sortData(string columnName)
{
   DataTable dt1=new DataTable();
   return  dt1=dataMgr[DatabaseFileNames.ControlDatabase]["OrderedTableName"]
              .Select("Department='CSC'")
              .OrderBy(x => x.Field<string>(columnName) ?? String.Empty,
                            new MyComparer(columnName)
                      )
              .CopyToDataTable();
}

... and we're done and can even extend that solution to use other data types e.g. DateTime.