Change order of rows in DataTable in C#

Question

Is it possible to change order of rows in DataTable so for example the one with current index of 5 moves to place with index of 3, etc.?

I have this legacy, messy code where dropdown menu get it's values from DataTable, which get it's values from database. It is impossible to make changes in database, since it has too many columns and entries. My original though was to add new column in db and order by it's values, but this is going to be hard.

So since this is only matter of presentation to user I was thinking to just switch order of rows in that DataTable. Does someone knows the best way to do this in C#?

This is my current code:

    DataTable result = flokkurDao.GetMCCHABAKflokka("MSCODE");

    foreach (DataRow row in result.Rows)
    {
         m_cboReasonCode.Properties.Items.Add(row["FLOKKUR"].ToString().Trim() + " - " + row["SKYRING"]);
    }

For example I want to push row 2011 - Credit previously issued to the top of the DataTable.

SOLUTION:

For those who might have problems with ordering rows in DataTable and working with obsolete technology that doesn't supports Linq this might help:

DataRow firstSelectedRow = result.Rows[6];
DataRow firstNewRow = result.NewRow();
firstNewRow.ItemArray = firstSelectedRow.ItemArray; // copy data
result.Rows.Remove(firstSelectedRow);
result.Rows.InsertAt(firstNewRow, 0);

You have to clone row, remove it and insert it again with a new index. This code moves row with index 6 to first place in the DataTable.

Do you really want random order? If so, random only once or always different? Since you wanted to add a column, is there a business logic which you want to use for your ordering? — Tim Schmelter, Apr 07 '16 at 07:29
@TimSchmelter Hi Tim, random only once. I want to be it like that all the time. Simply users want to have some of the entries on the top of that dropdown. (user will be users) I've attached picture with more explanation on what I'm trying to accomplish. — nemo_87, Apr 07 '16 at 07:31
this may help you http://stackoverflow.com/questions/7801714/how-to-extract-10-random-rows-from-datatable — sujith karivelil, Apr 07 '16 at 07:38
@nemo_87 Regarding your solution: Be carful if you want to persist the datatable back to the database. Even if you think you did not change anything other than the order, a dataadapter would create a delete and an insert query which can be dangerous if you have foreign key contraints. — Jürgen Steinblock, Apr 07 '16 at 09:18

lysp · Answer 1 · 2016-04-07T07:35:56.027

1

You can use linq to order rows:

DataTable result = flokkurDao.GetMCCHABAKflokka("MSCODE");

foreach (DataRow row in result.Rows.OrderBy(x => x.ColumnName))
{
     m_cboReasonCode.Properties.Items.Add(row["FLOKKUR"].ToString().Trim() + " - " + row["SKYRING"]);
}

To order by multiple columns:

result.Rows.OrderBy(x => x.ColumnName).ThenBy(x => x.OtherColumnName).ThenBy(x.YetAnotherOne)

To order by a specific value:

result.Rows.OrderBy(x => (x.ColumnName == 2001 or x.ColumnName == 2002) ? 0 : 1).ThenBy(x => x.ColumName)

You can use the above code to "pin" certain rows to the top, if you want more granular than that you can use a switch for example to sort specific values into sorted values of 1, 2, 3, 4 and use a higher number for the rest.

edited Apr 07 '16 at 07:35

answered Apr 07 '16 at 07:28

lysp

36
3

1

Does not care about the "random"-part of the question – Tim Schmelter Apr 07 '16 at 07:29
@lysp It's not a problem to sort it by column name... But the thing is I need to do it by row :( – nemo_87 Apr 07 '16 at 07:33
"Random" is addressed by specific value section in answer (3rd code block). – lysp Apr 07 '16 at 07:33

score 1 · Answer 2 · answered Apr 07 '16 at 07:32

1

If you really want randomness you could use Guid.NewGuid in LINQ's OrderBy:

DataTable result = flokkurDao.GetMCCHABAKflokka("MSCODE");
var randomOrder = result.AsEnumerable().OrderBy(r => Guid.NewGuid());
foreach (DataRow row in randomOrder)
{
    // ...
}

If you actually don't want randomness but you want specific values at the top, you can use:

var orderFlokkur2011 = result.AsEnumerable()
    .OrderBy(r => r.Field<int>("FLOKKUR") == 2011 ? 0 : 1);

answered Apr 07 '16 at 07:32

Tim Schmelter

450,073
74
686
939

I've probably didn't explained well. Will have to edit my question, I don't want complete randomness, I need to sort them in a unconventional way so for example: row with index 5 is always on the top of the DataTable, and row with index two is switched to index 3, etc. I need to make this order only once and that is how it should stay.... @TimSchmelter – nemo_87 Apr 07 '16 at 07:36
Have you seen my second approach? – Tim Schmelter Apr 07 '16 at 07:36
I just did, when I add it it's missing AsEnumerable and OrderBy for DataTable, do you maybe know which namespace I have to include to get those? It is even possible that I cannot do it, since this is super old software that's using WinForms... @TimSchmelter – nemo_87 Apr 07 '16 at 07:45
It's `System.Linq`, you can use it with visual studio 2008/.NET 3.5 – Tim Schmelter Apr 07 '16 at 07:48
I've added that one right away but it wasn't but still it stays unused and AsEnumerable is unrecognized. @TimSchmelter – nemo_87 Apr 07 '16 at 07:51

score 0 · Answer 3 · answered Apr 07 '16 at 07:30

0

You can not change the order or delete a row in a foreach loop, you should create a new datatable and randomly add the rows to new datatable, you should also track the inserted rows not to duplicate

answered Apr 07 '16 at 07:30

Okan SARICA

347
4
15

score 0 · Answer 4 · answered Apr 07 '16 at 07:31

Use a DataView

DataTable result = flokkurDao.GetMCCHABAKflokka("MSCODE");
DateView view = new DateView(result);
view.Sort = "FLOKKUR";
view.Filter = "... you can even apply an in memory filter here ..."

foreach (DataRowView row in view.Rows)
{
    ....

Every data table comes with a view DefaultView which you can use, this way you can apply the default sorting / filtering in your datalayer.

public DataTable GetMCCHABAKflokka(string tableName, string sort, string filter)
{
    var result = GetMCCHABAKflokka(tableName);
    result.DefaultView.Sort = sort;
    result.DefaultView.Filter = filter;
    return result;
}    

// use like this
foreach (DataRowView row in result.DefaultView)

Dont forget that the DataTable remains unchanged. Only your view is sorted. You can convert the view back to a DataTable with result.DefaultView.ToTable() — CathalMF, Apr 07 '16 at 08:19

Change order of rows in DataTable in C#

4 Answers4