DataView.Sort - more than just asc/desc (need custom sort)
Ok, I just whipped this up real quick, and didn't do all the neccessary error handling and null checking, but it should give you an idea and should be enough to get you started:
public static class DataTableExtensions
{
public static DataView ApplySort(this DataTable table, Comparison<DataRow> comparison)
{
DataTable clone = table.Clone();
List<DataRow> rows = new List<DataRow>();
foreach (DataRow row in table.Rows)
{
rows.Add(row);
}
rows.Sort(comparison);
foreach (DataRow row in rows)
{
clone.Rows.Add(row.ItemArray);
}
return clone.DefaultView;
}
}
Usage:
DataTable table = new DataTable();
table.Columns.Add("IntValue", typeof(int));
table.Columns.Add("StringValue");
table.Rows.Add(11, "Eleven");
table.Rows.Add(14, "Fourteen");
table.Rows.Add(10, "Ten");
table.Rows.Add(12, "Twelve");
table.Rows.Add(13, "Thirteen");
//Sort by StringValue:
DataView sorted = table.ApplySort((r, r2) =>
{
return ((string)r["StringValue"]).CompareTo(((string)r2["StringValue"]));
});
Result:
11 Eleven
14 Fourteen
10 Ten
13 Thirteen
12 Twelve
//Sort by IntValue:
DataView sorted = table.ApplySort((r, r2) =>
{
return ((int)r["IntValue"]).CompareTo(((int)r2["IntValue"]));
});
Result:
10 Ten
11 Eleven
13 Thirteen
12 Twelve
14 Fourteen
EDIT: Changed it to extension method.
Now in your Lambda, (or you can create a full blown Comparison method) you can do any kind of custom sorting logic that you need. Remember, -1 is less than, 0 is equal to, and 1 is greater than.
c# doing a custom sort on a datatable
You'll want to do a custom sort. See the following question for hints: DataView.Sort - more than just asc/desc (need custom sort)
You might want to break the first column into two separate columns.
C# datatable custom sort, one field first, then the rest
Using an Expression field on the DataTable, you can contain the sort within the DataTable itself (should you be passing it along to other consumers):
dt.Columns.Add("xxSort",
(123).GetType(),
"IIF([Module] = 'Intro', 0, Convert([Module], 'System.Int32'))");
dt.DefaultView.Sort = "xxSort ASC";
Sorting a DataView keeping the 0 values to the last
You could use Linq-To-DataSet
which is also more powerful, less error-prone and more readable.
DataTable orderedTable = ds.Tables[0].AsEnumerable()
.OrderBy(row => row.Field<int>("Value") == 0)
.ThenBy(row => row.Field<int>("Value"))
.CopyToDataTable();
This works because row.Field<int>("Value") == 0
returns a bool
if the value is 0. Enumerable.OrderBy
orders ascending(as opposed to OrderByDescending
), since true
can be understood as 1 and false
as 0 all Value=0
are coming last.
How can I customize the sorting of a DataTable column
I think you should use natural sorting and make your own IComparer
The best algo I found was here
http://www.davekoelle.com/files/AlphanumComparator.cs.
Just make it a generic class(as linq uses as Linq order by takes IComparer) , like following
public class AlphanumComparator<T> : IComparer<T>
{
private enum ChunkType { Alphanumeric, Numeric };
private bool InChunk(char ch, char otherCh)
{
ChunkType type = ChunkType.Alphanumeric;
if (char.IsDigit(otherCh))
{
type = ChunkType.Numeric;
}
if ((type == ChunkType.Alphanumeric && char.IsDigit(ch))
|| (type == ChunkType.Numeric && !char.IsDigit(ch)))
{
return false;
}
return true;
}
public int Compare(T x, T y)
{
String s1 = x as string;
String s2 = y as string;
if (s1 == null || s2 == null)
{
return 0;
}
int thisMarker = 0, thisNumericChunk = 0;
int thatMarker = 0, thatNumericChunk = 0;
while ((thisMarker < s1.Length) || (thatMarker < s2.Length))
{
if (thisMarker >= s1.Length)
{
return -1;
}
else if (thatMarker >= s2.Length)
{
return 1;
}
char thisCh = s1[thisMarker];
char thatCh = s2[thatMarker];
StringBuilder thisChunk = new StringBuilder();
StringBuilder thatChunk = new StringBuilder();
while ((thisMarker < s1.Length) && (thisChunk.Length == 0 || InChunk(thisCh, thisChunk[0])))
{
thisChunk.Append(thisCh);
thisMarker++;
if (thisMarker < s1.Length)
{
thisCh = s1[thisMarker];
}
}
while ((thatMarker < s2.Length) && (thatChunk.Length == 0 || InChunk(thatCh, thatChunk[0])))
{
thatChunk.Append(thatCh);
thatMarker++;
if (thatMarker < s2.Length)
{
thatCh = s2[thatMarker];
}
}
int result = 0;
// If both chunks contain numeric characters, sort them numerically
if (char.IsDigit(thisChunk[0]) && char.IsDigit(thatChunk[0]))
{
thisNumericChunk = Convert.ToInt32(thisChunk.ToString());
thatNumericChunk = Convert.ToInt32(thatChunk.ToString());
if (thisNumericChunk < thatNumericChunk)
{
result = -1;
}
if (thisNumericChunk > thatNumericChunk)
{
result = 1;
}
}
else
{
result = thisChunk.ToString().CompareTo(thatChunk.ToString());
}
if (result != 0)
{
return result;
}
}
return 0;
}
}
Now to apply this, use linq
DataTable dt = new DataTable();
dt.TableName = "Sort";
dt.Columns.Add("Check");
DataRow dr = dt.NewRow();
dr["Check"] = "12";
dt.Rows.Add(dr);
DataRow dr2 = dt.NewRow();
dr2["Check"] = "1283";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["Check"] = "store 1283";
dt.Rows.Add(dr3);
DataRow dr4 = dt.NewRow();
dr4["Check"] = "23";
dt.Rows.Add(dr4);
DataView dv = new DataView();
dv.Table = dt;
AlphanumComparator<string> comparer = new AlphanumComparator<string>();
//DataTable dtNew = dv.Table;
DataTable dtNew = dv.Table.AsEnumerable().OrderBy(x => x.Field<string>("Check"), comparer).CopyToDataTable();
dtNew.TableName = "NaturalSort";
dv.Table = dtNew;
Result 12, 23, 1283, store 1283
Sort System.Data.DataTable with multiple sort criteria
Try following which is similar to your last request :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication11
{
class Program
{
static void Main(string[] args)
{
List<string> probabilityStr = new List<string>() { "Hight", "Mid", "Low" };
DataTable dt = new DataTable();
dt.Columns.Add("Sector", typeof(string)); //1
dt.Columns.Add("BBG IPC", typeof(double));
dt.Columns.Add("Analyst", typeof(string));
dt.Columns.Add("Issuer Group", typeof(string)); //3
dt.Columns.Add("Seniority", typeof(string));
dt.Columns.Add("Mkt Value", typeof(double));
dt.Columns.Add("Nom Value", typeof(double));
dt.Columns.Add("Issue Group Rating", typeof(string));
dt.Columns.Add("Current SBR", typeof(string));
dt.Columns.Add("Notches", typeof(int));
dt.Columns.Add("Forward SBR", typeof(string));
dt.Columns.Add("Probability of Downgrade", typeof(string)); //2
dt.Columns.Add("Risk Category", typeof(string));
dt.Columns.Add("Comment", typeof(string));
dt.Columns.Add("Restricted Message", typeof(string));
dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "BB", -1, "YY", "Hight", "R1", "Comment", "gvbhjnnijnibj" });
dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "CC", 2, "II", "Low", "R2", "Bergtrgrt", "Other" });
dt.Rows.Add(new object[] { "Agencies", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "EE", 3, "LL", "Mid", "R1", "vggvbhjjnnjoioion", "ggvbhibniujbuhvg uvvugvghV" });
dt.Rows.Add(new object[] { "Consumer", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "OO", -1, "SS", "Higth", "R3", "vgvgyhvgvjhbkj", "bibhjbhjbjhb ubuyhbuyhb hbuhbuhbhb" });
dt.Rows.Add(new object[] { "Energy", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "PP", -2, "QQ", "Higth", "R1", "ertyuiop", "tfcgvhb Uvugvugvuhvh" });
dt.Rows.Add(new object[] { "Energy", 180969, "MUSTO", "Caisse des Depots", "Senior", 10114481, 1000000, "AA", "GG", -3, "FF", "Low", "R2", "gvgvgvvgfccfdxdxrtf xrtfvgh tdctfgv trcfygvh tcfyghv b ygvhb ", "ygvgubiujb" });
DataTable dt2 = dt.Clone();
var sectors = dt.AsEnumerable()
.OrderBy(x => x.Field<string>("Sector"))
.ThenBy(x => probabilityStr.IndexOf(x.Field<string>("Probability of Downgrade")))
.ThenBy(x => x.Field<string>("Issuer Group"))
.GroupBy(x => x.Field<string>("Sector"))
.ToList();
for (int si = 0; si < sectors.Count(); si++)
{
var probabilities = sectors[si].GroupBy(x => x.Field<string>("Probability of Downgrade")).ToList();
for (int pi = 0; pi < probabilities.Count(); pi++)
{
var groups = probabilities[pi].GroupBy(x => x.Field<string>("Issuer Group")).ToList();
for (int gr = 0; gr < groups.Count(); gr++)
{
for (int r = 0; r < groups[gr].Count(); r++)
{
DataRow row = dt2.Rows.Add();
for (int i = 0; i < dt2.Columns.Count; i++)
{
switch (dt2.Columns[i].ColumnName)
{
case "Sector" :
if (pi == 0)
{
row["Sector"] = sectors[si].Key;
}
else
{
row["Sector"] = DBNull.Value;
}
break;
case "Probability of Downgrade" :
if (gr == 0)
{
row["Probability of Downgrade"] = probabilities[pi].Key;
}
else
{
row["Probability of Downgrade"] = DBNull.Value;
}
break;
case "Issuer Group" :
if (r == 0)
{
row["Issuer Group"] = groups[gr].Key;
}
else
{
row["Issuer Group"] = DBNull.Value;
}
break;
default :
row[i] = groups[r].First()[dt2.Columns[i].ColumnName];
break;
}
}
}
}
}
}
}
}
}
How to sort a list neither asc or desc but custom
Weird requirement. :)
Anyways, assuming you support only 1, 2 and 3, as others have suggested you can write your own IComparer<T>
. e.g.
class Program
{
static void Main(string[] args)
{
List<CustomClass> list = new List<CustomClass>()
{
new CustomClass(){MyField = 3},
new CustomClass(){MyField = 2},
new CustomClass(){MyField = 1},
};
PrintList(list);
list.Sort(new CustomClassComparer());
PrintList(list);
Console.ReadLine();
}
private static void PrintList(List<CustomClass> list)
{
foreach (var item in list)
{
if (item != null)
Console.WriteLine("MyField: {0}", item.MyField);
else
Console.WriteLine("MyField: null");
}
}
}
public class CustomClass
{
public int MyField { get; set; }
}
public class CustomClassComparer : IComparer<CustomClass>
{
public int Compare(CustomClass x, CustomClass y)
{
if (x == null)
{
if (y == null)
return 0;
return -1;
}
if (y == null)
return 1;
if (x.MyField == y.MyField)
return 0;
if (x.MyField == 2)
return 1;
if (x.MyField == 1)
if (y.MyField == 3)
return 1;
else
return -1;
return -1;
}
}
Alternatively, you can make your CustomClass
IComparable<T>
.
Related Topics
Why Does Timespan.Parseexact Not Work
How to Target Attributes for a Record Class
Class List Keeps Printing Out as Class Name in Console
How to Find All the Classes Which Implement a Given Interface
Entity Framework Code First Lazy Loading
Convert Datetime to Date Format Dd/Mm/Yyyy
How to Keep Console Window Open
Determining If File Exists Using C# and Resolving Unc Path
Difference Between Lookup() and Dictionary(Of List())
Wait Some Seconds Without Blocking UI Execution
Why Is It Impossible to Override a Getter-Only Property and Add a Setter