Dataview.Sort - More Than Just Asc/Desc (Need Custom Sort)

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



Leave a reply



Submit