sorting and updating datatable not producing expected results

Question

EDIT: Solved this myself - obviously won't work as sorting the dataTable doesn't sort the underlying data - created a dataView from the table, works fine.

I have a datatable which I am sorting and then iterating through to remove duplicate values in one column, however the output is not as expected.

Datatable structure:

infoRow["Title"]
infoRow["QuickLink"]
infoRow["Description"]
infoRow["Date"]
infoRow["MonthName"]

I'm sorting like this, which works fine, and produces a table ordered in ascending month order:

dataTable = dataTable.DefaultView.ToTable(true);
dataTable.DefaultView.Sort = "Date asc";

After the sort, I'm using the code below to compare each row to the previous, and if the MonthName value is the same, replaced it with an empty string:

string prevMonthName = "";
foreach (DataRow row in dtEvents.Rows)
{
    string strMonthName = row["MonthName"].ToString();
    if (strMonthName == prevMonthName)
    {
        row["MonthName"] = "";
        row.AcceptChanges();
    }
    prevMonthName = strMonthName;
}           

So, the problem I'm having is that even when I run the MonthName loop after the sort, it appears to be running against the unsorted data. It's like DefaultView.Sort only affects the rendered output without physically reordering the table, hence the second part of the code doesn't produce the result I need. Should I maybe be using DataView or am I just way off track...

Answer 1

I was actually having a similar, but slightly different problem and your question gave me an idea. As it turns out, your code was incredibly close to what you (and I) need. All you need to do is flip those two lines of sorting code like so:

dataTable.DefaultView.Sort = "Date ASC";
dataTable = dataTable.DefaultView.ToTable(true);

Now, the first line of code sorts the DefaultView. This would be enough for your DataGridView or ComboBox or whatever you're using for display, because they make use of the DefaultView. However, the DataTable, itself, remains unsorted. Therefore, the second line sets the DataTable to look exactly like the sorted DefaultView.

I just noticed your edit at the top which says you've solved it. That 'solution' seems to be more of a workaround. Seeing as how you had the right code but in the wrong order, I figured you would be interested in this answer.

Answer 2

Assuming that dtEvents is referencing the same object as datatable, you could try this:

string prevMonthName = "";
foreach (DataRowView row in dtEvents.DefaultView)
{
    string strMonthName = row["MonthName"].ToString();
    if (strMonthName == prevMonthName)
    {
        row["MonthName"] = "";
        row.AcceptChanges();
    }
    prevMonthName = strMonthName;
} 

Answer 3

Just for fun I figured out how to do this using Linq to SQL (assuming I had a sql table with your above schema). Since I spent the time figuring it out, I thought I might as well share it.

// Order the table and add an index column
var ordered = MonthTests.OrderBy(mt => mt.Date)
                        .AsEnumerable()
                        .Select((mt, index) => new
                        {
                            OrderId = index,
                            Record = mt
                        });

// Select out what we want
var query = from item in ordered
            let prev = ordered.FirstOrDefault (q => q.OrderId == (item.OrderId-1))
            select new 
            { 
                Title = item.Record.Title,
                QuickLink = item.Record.QuickLink,
                Date = item.Record.Date,
                MonthName = (prev != null && prev.Record.MonthName == item.Record.MonthName) ? "" : item.Record.MonthName
            };

Have fun.