CODEWITHSUNDEEP
c#.netdatatable
chaya D
chaya D

Reputation: 161

how to sort with order by case cluase a data table in c#

i need to sort a datatable according to a case on cell value i tried this:

DataTable dt = new DataTable();
...
dt.DefaultView.Sort = "Case When Col6 = 'open' then 1 When Col6 = 'approved' then 2 When Col6 = 'awaitingApproval' then 3 else 999 end asc";

but this fails. how can i whise a case cluase on datatable like i can write in the sql:

order by 
Case 
   When Col6 = 'open' then 1 
   When Col6 = 'approved' then 2 
   When Col6 = 'awaitingApproval' then 3 
   else 999 
end asc

Upvotes: 0

Views: 2171

Answers (3)

Lucifer
Lucifer

Reputation: 1594

If you want more LinQ-ed way then you can try is 

  DataTable dt = new DataTable();
  List<string> sortString = new List<string>();
  sortString.Add("open");
  sortString.Add("approved");
  sortString.Add("awaitingApproval");

  var listsa = dt.AsEnumerable().GroupBy(x => x["Col6"].ToString()).ToList();
  sortString.ForEach(str => listsa.ForEach(x =>
                            {
                              if (x.Key == str)
                                dt.Rows.Add(x.ToList());
                            }));

Upvotes: 0

Magnus
Magnus

Reputation: 46947

How about using linq? This will not actually sort the datatable but return a new sorted collection of rows.

var result = 
    dt.AsEnumerable().OrderBy (x => 
    {
        switch (x.Field<string>("Col6"))
        {
            case "open" : return 1;
            case "approved" : return 2;
            case "awaitingApproval" : return 3;
        }   
        return 999;
    });

Upvotes: 2

Ctznkane525
Ctznkane525

Reputation: 7465

Create a Calculated Column in the DataTable with this expression:

IIF(Col6='open', 1, IIF(Col6='approved', 2, IIF(Col6='awaitingApproval', 3, 999)))

Then you can easily sort on the calculated column

dt.Columns.Add("CalculatedField", typeof(Int32));
dt.Columns["CalculatedField"].Expression = "IIF(Col6='open', 1, IIF(Col6='approved', 2, IIF(Col6='awaitingApproval', 3, 999)))";

dt.DefaultView.Sort = "CalculatedField";

Upvotes: 0

Related Questions