Sorting with DataView

Question

Hi All i got a problem with dataview that get data from datatabel (Col1 : ID,Col2: Time) and I'm sorting by Time in desc ... when the values for example {40.21,80.21,70.25,25.2} the dataview sorting them as I need but when one of values goes above 100 for example {40.21,80.21,100.25,25.2} the dataview always sort the highest number is the buttom, I don't know why .. This is a sample code

 Dim dt As New DataTable
        dt.Columns.Add("ID")
        dt.Columns.Add("Time")

        dt.Rows.Add(New String() {"1", "90.24"})
        dt.Rows.Add(New String() {"2", "80.25"})
        dt.Rows.Add(New String() {"3", "70.22"})
        dt.Rows.Add(New String() {"4", "102.12"})

        Dim dv As New DataView(dt)
        dv.Sort = "Time Desc"

Thanks in advance ...

Answer 1

Like Tim says, you are sorting by a string. I had to deal with a lot of mixed numbers and strings in multiple columns so I wrote a class to do the sorting (below). It properly sorts numbers as numbers, dates as dates and mixed number/string fields as users would expect.

I had columns with data like "Day 1 Timepoint 1" "Day 14 Timepoint 3" "Day 15 Timepoint 10" ... And mixed numbers and dates.

The class takes a data table and a list of columns to include in the sort. Most important column first in the list, add as many columns as you want to the sort.

using System;
using System.Data;
using System.Linq;

public class DataTableSorter
{
    public enum SortDirection
    {
        Ascending,
        Descending
    }

    private const string MC_TEMP_COL_NAME = "SorterXXXColumn";
    private const int MC_NUM_PAD_COLS = 12;
    private static string msPAD = new string('0', MC_NUM_PAD_COLS);

    public static DataTable SortTable(DataTable oDT, SortDirection eSortDir, params string[] asColumns)
    {

        //so DataView has limited sorting capability, this builds it out so numbers and strings work out well.
        oDT.Columns.Add(new DataColumn(MC_TEMP_COL_NAME, typeof(string)));

        foreach (DataRow oDR in oDT.Rows)
        {
            string sSortable = string.Empty;

            foreach(string sCol in asColumns)
                sSortable += Sortable(oDR[sCol]);

            oDR[MC_TEMP_COL_NAME] = sSortable;
        }

        //Using DataView for sorting DataTable's data
        using (DataView oSortedView = oDT.DefaultView)
        {
            oSortedView.Sort = string.Format("[{0}] {1}", MC_TEMP_COL_NAME, eSortDir == SortDirection.Ascending ? "ASC" : "DESC");

            using (DataTable oDTreturn = oSortedView.ToTable())
            {

                //remove special sort column
                oDTreturn.Columns.RemoveAt(oDTreturn.Columns.Count - 1);

                return oDTreturn;
            }
        }
    }

    private static string Sortable(object oValue)
    {
        DateTime oDtT;
        if (string.IsNullOrWhiteSpace(oValue.ToString()))
        {
            return string.Empty;
        }
        else if (DateTime.TryParse(oValue.ToString(), out oDtT))
        {
            System.Diagnostics.Debug.Print(oValue.ToString() + "\t" + String.Format("{0:yyyyMMddHHmmss.FFFF}", oDtT));
            return string.Format("{0:yyyyMMddHHmmss.FFFF}", oDtT);
        }
        else
        {
            //pad out all numbers with lots of zeros, so that numbers sort as numbers.
            char[] acVal = oValue.ToString().ToCharArray();
            string sBuff = string.Empty;
            string sRC = string.Empty;

            bool bAfterDecmal = false;

            int iCharCount = acVal.Length;
            for (int i = 0; i < iCharCount; i++)
            {
                char c = acVal[i];

                bool bIsNumeric = "0123456789".Contains(c);

                bool bEndSection = false;
                if (i == (iCharCount - 1))
                {
                    bEndSection = true;
                }
                else
                {
                    char cNext = acVal[i + 1];

                    if (bIsNumeric != "0123456789".Contains(cNext))
                    {
                        bEndSection = true;
                    }
                    else if (c == '.')
                    {
                        bEndSection = true;
                        bIsNumeric = false;
                    }
                    else if (cNext == '.')
                    {
                        bEndSection = true;
                    }
                }

                sBuff += c;

                if (bEndSection)
                {
                    if (bIsNumeric)
                    {
                        if (bAfterDecmal)
                        {
                            // FOR DECMALS, JUST RIGHT-PAD TO MC_NUM_PAD_COLS ZEROS:
                            sRC += (sBuff + msPAD).Substring(0, MC_NUM_PAD_COLS);
                            bAfterDecmal = true;
                        }
                        else
                        {
                            // for integers, left pad to MC_NUM_PAD_COLS zeros.
                            sRC += (msPAD + sBuff).Substring(sBuff.Length);
                        }
                    }
                    else
                    {
                        // upper case all strings, for better ordering.
                        sRC += sBuff.ToUpper();
                    }

                    sBuff = string.Empty;

                } // CHANGE IN NUMERIC STATUS

                if (c == '.')
                    bAfterDecmal = true;
            }

            System.Diagnostics.Debug.Print(oValue.ToString() + "\t" + sRC);
            return sRC;
        }
    }




}

Answer 2

You are sorting a String, so what have you expected? "10000" is lower than "2" because "1" is alphabetically lower than "2" just as "abbbb" would be lower than "b".

You need to use the correct data-type(in this case i assume Double) to get the correct(numeric) sorting:

Dim dt As New DataTable
dt.Columns.Add("ID", GetType(Int32))
dt.Columns.Add("Time", GetType(Double))

dt.Rows.Add(1, 90.24)
dt.Rows.Add(2, 80.25)
dt.Rows.Add(3, 70.22)
dt.Rows.Add(4, 102.12)

Dim dv As New DataView(dt)
dv.Sort = "Time Desc"

Result:

    4  102,12
    1   90,24
    2   80,25
    3   70,22

Answer 3

  dt.Rows.Add(New String() {"1", "90.24"})

change it to

    dt.Rows.Add(New Integer() {"1", "90.24"})