none
EMERGENCY PLEASE: Sorting Dates in DataView which are strings RRS feed

  • Question

  • Hi guys

    I really need some help on this one. I have a DataView which i use to conduct various sorting. The dataview comes from a typed DateSet. The dataset has serval different columns which are either int, string or bool. Date data types have not been used because of the need to have nulls (C#). When i sort of the DataView columns that contains a string representation of a date it sorts but not correctly as you would expect.

     

    I need a way of being able to keep the data type as string but have the sorting operate correctly. Can anyone help.

    Thanks a lot.

    Anthony

     

    Wednesday, December 12, 2007 12:04 PM

All replies

  • Hi Anthony,

     

    I don't really understand the reason you don't use date type. Anyway, there is a simple solution for your case, store another integer field for any date such as 1-1-2007 -> 20070101, 11-28-2007 -> 20071128, then sort by this integer field.

     

    Hope it helps.

     

     

     

    Thursday, December 13, 2007 4:08 AM
  • Thanks for the reply.

    I believe the system was originally designed this way to handle nullable dates. So that’s why they are strings and not dates.

     

    As far as the integer date goes I had thought of that but the problem is that when have about 250 date columns in total that this  needs to go across. Thus it is a non trivial task to produce an integer equivalent.

     

    Thus I was wondering, is there a way to tell .net to sort the data view in a particular way for convert the data columns in the data view to dates just for the sorting or something similar?

    Thanks

    Anthony

    Thursday, December 13, 2007 2:23 PM
  • In a typed DataSet, your date columns ought to be of type System.DateTime, and you should set their AllowDBNull property to true.  Storing dates in a DataSet as strings because C#'s DateTime type isn't nullable is reinventing the square wheel.

    Thursday, December 13, 2007 10:01 PM
  • Thanks for the reply

    This may be the case but changing the datatypes of these columns in the dataset like what you are suggesting is not really an option. I need an alternative to get the sorting working.

    Any ideas???

    Thanks

    Anthony

    Friday, December 14, 2007 3:47 AM
  • One way or another, you're going to have to convert those strings to dates.  You should fix the actual problem if at all possible, because before long you're going to encounter another problem caused by these columns having the incorrect type, and another, and another, and the cumulative effort of implementing workarounds to all of those problems and maintaining this broken design will far exceed the effort required to fix the design.

     

    Here's a workaround that's relatively easy to implement.  Make a copy of the in-memory DataTable for sorting purposes, and convert the date columns to the correct type.  This code should work:

     

    Code Block

    private DataTable SortableTable(DataTable badTable, List<string> dateColumns)

    {

    DataTable goodTable = new DataTable();

     

    foreach (DataColumn c in badTable.Columns)

    {

       if (dateColumns.Contains(c.ColumnName))

       {

          goodTable.Columns.Add(c.ColumnName, typeof(System.DateTime));

          goodTable.Columns[c.ColumnName].AllowDBNull = true;

       }

       else

       {

          goodTable.Columns.Add(c.ColumnName, c.DataType);

       }

    }

     

    foreach (DataRow badRow in badTable.Rows)

    {

       DataRow goodRow = goodTable.NewRow();

       foreach (DataColumn c in goodTable.Columns)

       {

          if (dateColumns.Contains(c.ColumnName))

          {

             goodRow[c.ColumnName] =

                (badRow[c.ColumnName] is System.DBNull) ?

                System.DBNull.Value : DateTime.Parse(badRow[c.ColumnName].ToString());

          }

       else

       {

          goodRow[c.ColumnName] = badRow[c.ColumnName];

       }

       goodTable.Rows.Add(goodRow);

    }

    return goodTable;

    }

     

    Of course, it doubles the memory used by the bad table, and (at least) doubles the time it takes to read the table in the first place, and leaves you with the thorny problem of synchronizing changes between the two tables if either can change.  There are probably other defects in this fix that I'm not thinking about.
    Saturday, December 15, 2007 3:33 AM