Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- private static DataSet dataSet;
- private const string tableName = "MyTable";
- private const string columnName1 = "Supplier"; //Column names
- private const string columnName2 = "Invoice";
- private const string columnName3 = "Item";
- private const string columnName4 = "Amount";
- private static DataTable GroupQueryA(DataTable dataTable)
- {
- DataTable groupedTable = dataTable.AsEnumerable()
- .GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
- .Select(g => new GroupSum
- {
- Key1 = g.Key.Key1,
- Key2 = g.Key.Key2,
- Sum = g.Sum(x => x.Field<double>(columnName4))
- }).PropertiesToDataTable<GroupSum>();
- return groupedTable;
- }
- private class GroupSum
- {
- public string Key1 { get; set; }
- public string Key2 { get; set; }
- public Double Sum { get; set; }
- }
- AddRow(dataTable, "SA", "INVA", "ITA", 10);
- AddRow(dataTable, "SA", "INVA", "ITB", 20);
- AddRow(dataTable, "SB", "INVB", "ITC", 50);
- "SA", "INVA", 30
- "SB", "INVB", 50
- public class GroupKeys
- {
- public string Key1 { get; set; }
- public string Key2 { get; set; }
- }
- private static DataTable GroupQueryB(DataTable dataTable)
- {
- DataTable groupedTable = dataTable.AsEnumerable()
- .GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
- .Select(g => new GroupSum
- {
- Key1 = g.Key.Key1,
- Key2 = g.Key.Key2,
- Sum = g.Sum(x => x.Field<double>(columnName4))
- }).PropertiesToDataTable<GroupSum>();
- return groupedTable;
- }
- "SA", "INVA", 10
- "SA", "INVA", 20
- "SB", "INVB", 50
- //QueryA
- .GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
- //QueryB
- .GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
- internal static class TestForStackOverflow
- {
- private static DataSet dataSet;
- private const string tableName = "MyTable";
- private const string columnName1 = "Supplier"; //Column names
- private const string columnName2 = "Invoice";
- private const string columnName3 = "Item";
- private const string columnName4 = "Amount";
- private class GroupKeys
- {
- public string Key1 { get; set; }
- public string Key2 { get; set; }
- }
- private class GroupSum
- {
- public string Key1 { get; set; }
- public string Key2 { get; set; }
- public Double Sum { get; set; }
- }
- public static void Test()
- {
- DataTable dataTable = InitializeDataTable();
- //DataTable groupedTable = GroupQueryA(dataTable); //Please uncomment to run test A
- DataTable groupedTable = GroupQueryB(dataTable); //Please uncomment to run test B
- DisplayData(groupedTable);
- }
- private static DataTable GroupQueryA(DataTable dataTable)
- {
- DataTable groupedTable = dataTable.AsEnumerable()
- .GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
- .Select(g => new GroupSum
- {
- Key1 = g.Key.Key1,
- Key2 = g.Key.Key2,
- Sum = g.Sum(x => x.Field<double>(columnName4))
- }).PropertiesToDataTable<GroupSum>();
- return groupedTable;
- }
- private static DataTable GroupQueryB(DataTable dataTable)
- {
- DataTable groupedTable = dataTable.AsEnumerable()
- .GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
- .Select(g => new GroupSum
- {
- Key1 = g.Key.Key1,
- Key2 = g.Key.Key2,
- Sum = g.Sum(x => x.Field<double>(columnName4))
- }).PropertiesToDataTable<GroupSum>();
- return groupedTable;
- }
- private static System.Data.DataTable PropertiesToDataTable<T>(this System.Collections.Generic.IEnumerable<T> source)
- {
- System.Data.DataTable dt = new System.Data.DataTable();
- //Weź listę właściwości typu <T> i dla każdej właściwości dodaj do tabeli kolumnę tego samego typu co właściwość
- var props = System.ComponentModel.TypeDescriptor.GetProperties(typeof(T));
- foreach (System.ComponentModel.PropertyDescriptor prop in props)
- {
- System.Data.DataColumn dc = dt.Columns.Add(prop.Name, prop.PropertyType);
- dc.Caption = prop.DisplayName;
- dc.ReadOnly = prop.IsReadOnly;
- }
- //Kopiuj rekordy z kwerendy do DataTable
- foreach (T item in source)
- {
- System.Data.DataRow dr = dt.NewRow();
- foreach (System.ComponentModel.PropertyDescriptor prop in props)
- {
- dr[prop.Name] = prop.GetValue(item);
- }
- dt.Rows.Add(dr);
- }
- return dt;
- }
- private static DataTable InitializeDataTable()
- {
- dataSet = new DataSet();
- DataTable dataTable = dataSet.Tables.Add(tableName);
- dataTable.Columns.Add( columnName1, typeof(string));
- dataTable.Columns.Add( columnName2, typeof(string));
- dataTable.Columns.Add( columnName3, typeof(string));
- dataTable.Columns.Add( columnName4, typeof(double));
- AddRow(dataTable, "SA", "INVA", "ITA", 10);
- AddRow(dataTable, "SA", "INVA", "ITB", 20);
- AddRow(dataTable, "SB", "INVB", "ITC", 50);
- return dataTable;
- }
- private static void AddRow( DataTable dataTable, string supplier, string invoice, string item, double amount)
- {
- DataRow row = dataTable.NewRow();
- row[columnName1] = supplier;
- row[columnName2] = invoice;
- row[columnName3] = item;
- row[columnName4] = amount;
- dataTable.Rows.Add(row);
- }
- private static void DisplayData(System.Data.DataTable table)
- {
- foreach (System.Data.DataRow row in table.Rows)
- {
- foreach (System.Data.DataColumn col in table.Columns)
- {
- Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
- }
- Console.WriteLine("============================");
- }
- }
- }
- private class GroupKeys
- {
- public string Key1 { get; set; }
- public string Key2 { get; set; }
- public override int GetHashCode()
- {
- unchecked
- {
- return ((Key1 != null ? Key1.GetHashCode() : 0) * 397) ^ (Key2 != null ? Key2.GetHashCode() : 0);
- }
- }
- public override bool Equals(object obj)
- {
- if (ReferenceEquals(null, obj))
- return false;
- if (ReferenceEquals(this, obj))
- return true;
- if (obj.GetType() != this.GetType())
- return false;
- return Equals((GroupKeys)obj);
- }
- public bool Equals(GroupKeys other)
- {
- if (ReferenceEquals(null, other))
- return false;
- if (ReferenceEquals(this, other))
- return true;
- return string.Equals(Key1, other.Key1)
- && string.Equals(Key2, other.Key2);
- }
- }
Add Comment
Please, Sign In to add comment