Guest User

Untitled

a guest
May 24th, 2018
85
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.14 KB | None | 0 0
  1. private static DataSet dataSet;
  2. private const string tableName = "MyTable";
  3. private const string columnName1 = "Supplier"; //Column names
  4. private const string columnName2 = "Invoice";
  5. private const string columnName3 = "Item";
  6. private const string columnName4 = "Amount";
  7.  
  8. private static DataTable GroupQueryA(DataTable dataTable)
  9. {
  10. DataTable groupedTable = dataTable.AsEnumerable()
  11. .GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
  12. .Select(g => new GroupSum
  13. {
  14. Key1 = g.Key.Key1,
  15. Key2 = g.Key.Key2,
  16. Sum = g.Sum(x => x.Field<double>(columnName4))
  17. }).PropertiesToDataTable<GroupSum>();
  18.  
  19. return groupedTable;
  20. }
  21.  
  22. private class GroupSum
  23. {
  24. public string Key1 { get; set; }
  25. public string Key2 { get; set; }
  26. public Double Sum { get; set; }
  27. }
  28.  
  29. AddRow(dataTable, "SA", "INVA", "ITA", 10);
  30. AddRow(dataTable, "SA", "INVA", "ITB", 20);
  31. AddRow(dataTable, "SB", "INVB", "ITC", 50);
  32.  
  33. "SA", "INVA", 30
  34. "SB", "INVB", 50
  35.  
  36. public class GroupKeys
  37. {
  38. public string Key1 { get; set; }
  39. public string Key2 { get; set; }
  40. }
  41.  
  42. private static DataTable GroupQueryB(DataTable dataTable)
  43. {
  44. DataTable groupedTable = dataTable.AsEnumerable()
  45. .GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
  46. .Select(g => new GroupSum
  47. {
  48. Key1 = g.Key.Key1,
  49. Key2 = g.Key.Key2,
  50. Sum = g.Sum(x => x.Field<double>(columnName4))
  51. }).PropertiesToDataTable<GroupSum>();
  52.  
  53. return groupedTable;
  54. }
  55.  
  56. "SA", "INVA", 10
  57. "SA", "INVA", 20
  58. "SB", "INVB", 50
  59.  
  60. //QueryA
  61. .GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
  62. //QueryB
  63. .GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
  64.  
  65. internal static class TestForStackOverflow
  66. {
  67. private static DataSet dataSet;
  68. private const string tableName = "MyTable";
  69. private const string columnName1 = "Supplier"; //Column names
  70. private const string columnName2 = "Invoice";
  71. private const string columnName3 = "Item";
  72. private const string columnName4 = "Amount";
  73.  
  74. private class GroupKeys
  75. {
  76. public string Key1 { get; set; }
  77. public string Key2 { get; set; }
  78. }
  79.  
  80. private class GroupSum
  81. {
  82. public string Key1 { get; set; }
  83. public string Key2 { get; set; }
  84. public Double Sum { get; set; }
  85. }
  86.  
  87. public static void Test()
  88. {
  89. DataTable dataTable = InitializeDataTable();
  90.  
  91. //DataTable groupedTable = GroupQueryA(dataTable); //Please uncomment to run test A
  92. DataTable groupedTable = GroupQueryB(dataTable); //Please uncomment to run test B
  93.  
  94. DisplayData(groupedTable);
  95. }
  96.  
  97.  
  98. private static DataTable GroupQueryA(DataTable dataTable)
  99. {
  100. DataTable groupedTable = dataTable.AsEnumerable()
  101. .GroupBy(r => new { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
  102. .Select(g => new GroupSum
  103. {
  104. Key1 = g.Key.Key1,
  105. Key2 = g.Key.Key2,
  106. Sum = g.Sum(x => x.Field<double>(columnName4))
  107. }).PropertiesToDataTable<GroupSum>();
  108.  
  109. return groupedTable;
  110. }
  111.  
  112. private static DataTable GroupQueryB(DataTable dataTable)
  113. {
  114. DataTable groupedTable = dataTable.AsEnumerable()
  115. .GroupBy(r => new GroupKeys { Key1 = r.Field<string>(columnName1), Key2 = r.Field<string>(columnName2) })
  116. .Select(g => new GroupSum
  117. {
  118. Key1 = g.Key.Key1,
  119. Key2 = g.Key.Key2,
  120. Sum = g.Sum(x => x.Field<double>(columnName4))
  121. }).PropertiesToDataTable<GroupSum>();
  122.  
  123. return groupedTable;
  124. }
  125.  
  126. private static System.Data.DataTable PropertiesToDataTable<T>(this System.Collections.Generic.IEnumerable<T> source)
  127. {
  128. System.Data.DataTable dt = new System.Data.DataTable();
  129.  
  130. //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ść
  131. var props = System.ComponentModel.TypeDescriptor.GetProperties(typeof(T));
  132. foreach (System.ComponentModel.PropertyDescriptor prop in props)
  133. {
  134. System.Data.DataColumn dc = dt.Columns.Add(prop.Name, prop.PropertyType);
  135. dc.Caption = prop.DisplayName;
  136. dc.ReadOnly = prop.IsReadOnly;
  137. }
  138. //Kopiuj rekordy z kwerendy do DataTable
  139. foreach (T item in source)
  140. {
  141. System.Data.DataRow dr = dt.NewRow();
  142. foreach (System.ComponentModel.PropertyDescriptor prop in props)
  143. {
  144. dr[prop.Name] = prop.GetValue(item);
  145. }
  146. dt.Rows.Add(dr);
  147. }
  148. return dt;
  149. }
  150.  
  151. private static DataTable InitializeDataTable()
  152. {
  153. dataSet = new DataSet();
  154. DataTable dataTable = dataSet.Tables.Add(tableName);
  155.  
  156. dataTable.Columns.Add( columnName1, typeof(string));
  157. dataTable.Columns.Add( columnName2, typeof(string));
  158. dataTable.Columns.Add( columnName3, typeof(string));
  159. dataTable.Columns.Add( columnName4, typeof(double));
  160.  
  161. AddRow(dataTable, "SA", "INVA", "ITA", 10);
  162. AddRow(dataTable, "SA", "INVA", "ITB", 20);
  163. AddRow(dataTable, "SB", "INVB", "ITC", 50);
  164. return dataTable;
  165. }
  166. private static void AddRow( DataTable dataTable, string supplier, string invoice, string item, double amount)
  167. {
  168. DataRow row = dataTable.NewRow();
  169. row[columnName1] = supplier;
  170. row[columnName2] = invoice;
  171. row[columnName3] = item;
  172. row[columnName4] = amount;
  173. dataTable.Rows.Add(row);
  174. }
  175. private static void DisplayData(System.Data.DataTable table)
  176. {
  177. foreach (System.Data.DataRow row in table.Rows)
  178. {
  179. foreach (System.Data.DataColumn col in table.Columns)
  180. {
  181. Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
  182. }
  183. Console.WriteLine("============================");
  184. }
  185. }
  186. }
  187.  
  188. private class GroupKeys
  189. {
  190. public string Key1 { get; set; }
  191. public string Key2 { get; set; }
  192.  
  193. public override int GetHashCode()
  194. {
  195. unchecked
  196. {
  197. return ((Key1 != null ? Key1.GetHashCode() : 0) * 397) ^ (Key2 != null ? Key2.GetHashCode() : 0);
  198. }
  199. }
  200.  
  201. public override bool Equals(object obj)
  202. {
  203. if (ReferenceEquals(null, obj))
  204. return false;
  205. if (ReferenceEquals(this, obj))
  206. return true;
  207. if (obj.GetType() != this.GetType())
  208. return false;
  209.  
  210. return Equals((GroupKeys)obj);
  211. }
  212.  
  213. public bool Equals(GroupKeys other)
  214. {
  215. if (ReferenceEquals(null, other))
  216. return false;
  217. if (ReferenceEquals(this, other))
  218. return true;
  219.  
  220. return string.Equals(Key1, other.Key1)
  221. && string.Equals(Key2, other.Key2);
  222. }
  223. }
Add Comment
Please, Sign In to add comment