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

c# datatable sort multiple columns
c# sort datatable by column ascending
sort datatable in c# using linq
datatable sort c#
c# dataview sort
sort data in c#
sort datarow c
order by date in datatable 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 
   When Col6 = 'open' then 1 
   When Col6 = 'approved' then 2 
   When Col6 = 'awaitingApproval' then 3 
   else 999 
end asc

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;

Sorting with DataView (LINQ to DataSet), clauses, the resulting DataView would order the data by both columns specified. Expression-based sorting offers more powerful and complex  Using the above table syntax, we could simply ORDER BY currency_rank.This could be useful where the list to sort is too large. Closing Remarks. When the list of currencies (or other values to sort by) get much larger, it's better to have a separate column or table for sort-order.

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";

How To Sort Datatable in, Why you are using ID ASC . You should use Preferance ASC. Hide Copy Code. Datatable.DefaultView.Sort = "Preferance ASC";. ORDER BY is usually the last item in an SQL statement. You can include additional fields in the ORDER BY clause. Records are sorted first by the first field listed after ORDER BY. Records that have equal values in that field are then sorted by the value in the second field listed, and so on.

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

  DataTable dt = new DataTable();
  List<string> sortString = new List<string>();

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

Sorting Data in C# DataTable, Now, the table needs to be sorted in descending order, based on Total Column. Below are the steps to do the same: Create a clone of the  how to sort with order by case cluase a data table in c#. Ask Question Asked 1 year, Browse other questions tagged datatable or ask your own question.

SQLite Order By, If you use the SELECT statement to query data from a table, the order of rows in In case you want to sort the result set by multiple columns, you use a comma (  In case you want to sort the result set by multiple columns, you use a comma (,) to separate columns. The ORDER BY clause sorts rows using columns or expressions from left to right. In other words, the ORDER BY clause sorts the rows using the first column in the list.

Linq: Sorting a DataTable -Deborah's Developer MindScape, NET developer) asked me about sorting a DataTable by user-defined a Select/​Case statement was used in VB, but if/else if was used in C#. Sometimes I give parts of a solution to increase the play time to solve a problem. I didn’t anticipate a problem when showing how to perform a sort operation with a CASE statement. It’s a sweet solution when you need to sort something differently than a traditional ascending or descending sort. I gave my students this ORDER BY clause as an

12.2 Sorting and Filtering :: Chapter 12. DataViews and Data , NET sorting uses the same syntax as the ORDER BY clause in a SQL query. are sorted alphanumerically without regard to case (assuming the DataTable. Sorting with DataView (LINQ to DataSet) 03/30/2017; 5 minutes to read +7; In this article. The ability to sort data based on specific criteria and then present the data to a client through a UI control is an important aspect of data binding.

  • you can perform select on a datatable
  • thanks for quick answer, you mean to first select my values with case and then sort them?
  • yes c.d. first select them with case and then sort with
  • i am checking it in
  • It fails because Sort is a string that contains the column name followed by "ASC" (ascending) or "DESC" (descending). @Lucifer's suggestion should work.
  • how is the best way to keep with this and sort according to another column with second priority ?
  • @c.d. Add a .ThenBy(x => ) at the end of the expression.