Convert to decimal and make OrderBy

Question

My data table contains all the columns of the rows, but in some columns we fill in the numerical values. when I do an orderby on the datatable of this numeric column, it does not order correctly. before ordering my table looks

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

after order looks like

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

My code is:

 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

I thought about this a long time ago, and the solution I came up with is amazingly obvious: in essence, ordering is all about comparison - 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 just add it to 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 are done and we can even extend this solution to use other data types, for example. DateTime

Answer 2

Your OrderBy should look something like this:

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

Of course, it is assumed that all of your values ​​actually look like double .

If you want this to work for non-numeric columns as well, I don't think you will have to differentiate depending on the column name. (I. for example, you will need to have some mapping between columnName and IComparer .)

It might look something 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) 

A clean solution should definitely have the correct data types for the columns in the table.

Answer 3

You can simply insert a string with an int value with a leading 0 for the sort to work correctly:

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

You will need to determine the size large enough (10?) And some null value for sorting as you wish (in the case of using "0" they will be displayed first).