DataTable sorting does not sort properly after merging

Question

I have 2 DataTables that I'm combining using the .Merge method.

The column type for the 2 DataTables are System.Decimal. But it does not seem to sort properly. The data for the 1st DataTable will be sorted first and then subsequently the data from the 2nd DataTable.

So if you see the picture above, it will display the 1st DataTable data, and then 2nd DataTable, sorted by the start time.

If I reverse the sorting, it will sort the 2nd DataTable data in descending order then the 1st DataTable will appear.

This is the code for my sorting

DataView dv = new DataView(dataTableResult);
dv.Sort = string.Format("{0} {1}", ViewState["sortexp"].ToString(), 
GetSortDirection());
return dv;

ViewState["sortexp"] contains Start Time in this case, and GetSortDirection is either "ASC" or "DESC".

My query:

select  ....
        to_number(to_char([start_field],'hh24mi')) as start_time, 
        to_number(to_char([end_field], 'hh24mi')) as end_time,
        ...
from    [table]
where   [condition]

and another one I am getting the result from webservice and populate the datarow manually.

foreach (DTResult dtr in result)
{
    DataRow dr = dtTable2.NewRow();
    ...
    dr["start_time"] = Decimal.Parse(dtr.start_time.Replace(":", ""));
    dr["end_time"] = Decimal.Parse(dtr.end_time.Replace(":", ""));
    ...
    dtTable2.Rows.Add(dr);
}

And the merging code

dataTableResult.Merge(dtTable1);
dataTableResult.Merge(dtTable2);

Defintion for dtTable column:

...
dtTable2.Columns.Add("start_time", typeof(decimal));
dtTable2.Columns.Add("end_time", typeof(decimal));
...

How to solve the issue?

You probably think that all the Fields type is decimal. There's the chance that the Start Time field (or all fields) of those tables is of type string. — Jimi
Yes, well, what is dtTable? And its fields object type? This sorting result is quite probably coming from strings evaluation. Check twice, while debugging. — Jimi
Allright. Have you really checked, in debug mode, the merged table content => dataTableResult -> Rows -> Result View -> [0] -> ItemArray, to eyeball the actual content of the rows before setting the DataSource of something? If the values are not all of the same type, you should see it there. If they're all the same object data type (decimal, here), I don't know why they shouldn't sort correctly. — Jimi

rcs rcs · Accepted Answer · 2019-01-08T07:38:19

After debugging, I found out that it is due to dataTableResult.Merge(dtTable2); command creates new columns in dataTableResult, so that the number of columns become double.

So the content of the dataTableResult is something like the following:

--------------------------------------------------------------------
|Rows              | start_time | end_time | start_time | end_time |
--------------------------------------------------------------------
|0                 | 1200       | 1400     | NULL       | NULL     | --> 1st data table merge
|1                 | 800        | 1000     | 800        | 1000     | --> 2nd data table merge
|2                 | 1400       | 1600     | 1400       | 1600     | --> 2nd data table merge
|3                 | 1600       | 1800     | 1600       | 1800     | --> 2nd data table merge
--------------------------------------------------------------------

The column name is just for illustration, since I cannot see the actual column name. So once the 2nd datatable is merged, it creates new columns. The new columns for the 1st datatable contains NULL. And the column content for the 2nd datatable will be repeated (like both start_time columns will have the same value). It's quite weird though.

So here is how I solve the problem:

In the dataTableResult, I will define all the columns using capital letters as the column name. For some reason, the previous query is using normal letter and this causes it to double.

dataTableResult.Columns.Add("START_TIME", typeof(decimal));
dataTableResult.Columns.Add("END_TIME", typeof(decimal));

Then in the Database Query, I will also use capital letters as the column name alias

select  ....
        to_number(to_char([start_field],'hh24mi')) as START_TIME, 
        to_number(to_char([end_field], 'hh24mi')) as END_TIME,
        ...
from    [table]
where   [condition]

And similarly for the 2nd DataTable

dtTable2.Columns.Add("START_TIME", typeof(decimal));
dtTable2.Columns.Add("END_TIME", typeof(decimal));

By doing it this way, when calling dataTableResult.Merge(dtTable2);, the resulting datatable will not have duplicated columns, and the sorting issue is fixed.

DataTable sorting does not sort properly after merging

1 Answers