Description
The following code is for implementing the least known feature of the DataTable, Compute() method. Furthermore, it shows how you can read data from the tab-delimited text file, and move this data into the DataTable object.
What does this method do?
It computes the given expression on the current rows that pass the filter criteria.
  1. object DataTable.Compute( string expression, string filter)
as you see, the function return "Object", so you have to cast it to your data type.
The following aggregate types are supported in an expression:
I commented inline, so it should be very easy to follow. I programmed a simple windows application, so you can test it. Do not forget to change "FilePath"
Source Code:
  1. using System;
  2. using System.Collections;
  3. using System.IO;
  4. using System.Data;
  5. using System.Data.SqlClient;
  6. namespace DataTableCompute {
  7. public class Compute {
  8. /// <summary>
  9. /// instance variables
  10. /// </summary>
  11. protected Decimal InterestSum; //sum
  12. protected Double InterestVar; // statistical variance
  13. protected Double InterestStDev; // standard deviation
  14. protected Int32 InterestCount; //count
  15. protected Decimal InterestMax; // maximum
  16. protected Decimal InterestMin; // minimum
  17. protected Decimal InterestAvg; // average
  18. protected DataTable myDataTable; //holds data from text file
  19. protected String FilePath; //holds filepath of text file
  20. /// <summary>
  21. /// DataTableCompute constructor
  22. /// </summary>
  23. /// <param name="FilePath"></param>
  24. public Compute(string FilePath) {
  25. // instantiate instance variables
  26. this.FilePath = FilePath;
  27. myDataTable = new DataTable("InterestRate");
  28. // read data from text file
  29. // then move this data to
  30. //myDataTable DataTable
  31. ReadData_FromTextFile_And_Process();
  32. //we have the data in datatable
  33. // now we can calculate stats
  34. ComputeStats();
  35. }
  36. /// <summary>
  37. /// returns sum
  38. /// </summary>
  39. public Decimal sum {
  40. get {
  41. return this.InterestSum;
  42. }
  43. }
  44. /// <summary>
  45. /// returns average
  46. /// </summary>
  47. public Decimal average {
  48. get {
  49. return this.InterestAvg;
  50. }
  51. }
  52. /// <summary>
  53. /// returns count
  54. /// </summary>
  55. public Int32 Count {
  56. get {
  57. return this.InterestCount;
  58. }
  59. }
  60. /// <summary>
  61. /// return maximum number
  62. /// </summary>
  63. public Decimal Maximum {
  64. get {
  65. return this.InterestMax;
  66. }
  67. }
  68. /// <summary>
  69. /// returns minimum number
  70. /// </summary>
  71. public Decimal Minimum {
  72. get {
  73. return this.InterestMin;
  74. }
  75. }
  76. /// <summary>
  77. /// returns statistical variance
  78. /// </summary>
  79. public Double Variance {
  80. get {
  81. return this.InterestVar;
  82. }
  83. }
  84. /// <summary>
  85. /// returns statistical standard deviation
  86. /// </summary>
  87. public Double Std_Deviation {
  88. get {
  89. return this.InterestStDev;
  90. }
  91. }
  92. /// <summary>
  93. /// this function reads data from tab deliminated text file
  94. /// and move them into DataTable object
  95. /// </summary>
  96. private void ReadData_FromTextFile_And_Process() {
  97. String input;
  98. DataRow myDataRow;
  99. //check whether the file is exist
  100. // if not, throw an exception
  101. if (!File.Exists(this.FilePath)) {
  102. throw new Exception("File does not Exist");
  103. }
  104. //read the text file into StreamReader
  105. StreamReader sr = File.OpenText(this.FilePath);
  106. ///we know that text file has two fields
  107. ///first field is date
  108. ///second field is interest rates in the U.S.
  109. ///this is real data coming from Federal Reserve Bank
  110. myDataTable.Columns.Add("Date", typeof(DateTime));
  111. myDataTable.Columns.Add("Interest_Rate", typeof(Decimal));
  112. ///loop until the end of file
  113. while ((input = sr.ReadLine()) != null) {
  114. String[] nodes;
  115. ///the following code shows you
  116. ///how you can split tab deliminated text
  117. ///new char[] {'\t'}
  118. ///\t means in C# is tab
  119. ///so we can use it for this purpose
  120. nodes = input.Split(new char[] { '\t' });
  121. ///add a new row to the datatable
  122. myDataRow = myDataTable.NewRow();
  123. ///parse values then
  124. ///assign the values from array to rows
  125. myDataRow["Date"] = DateTime.Parse(nodes[0]);
  126. myDataRow["Interest_Rate"] = Decimal.Parse(nodes[1]);
  127. /// add the row to the datatable
  128. myDataTable.Rows.Add(myDataRow);
  129. }
  130. ///close the streamreader
  131. sr.Close();
  132. }
  133. /// <summary>
  134. /// this function calculates some stats
  135. /// built in DataTable object
  136. /// myDataTable.Compute("Var(Interest_Rate)","Date>#6/1/1954#");
  137. /// first thing after compute is expression
  138. /// Var(Interest_Rate)
  139. /// Var means variance
  140. /// Interest rate is a column name as you see.
  141. /// "Date>#6/1/1954#" is called filter
  142. /// I know that the data starts 7/1/1954 so
  143. /// the whole expression states that
  144. /// after the date of 6/1/1954 compute the variance of interest_Rate column
  145. /// </summary>
  146. private void ComputeStats() {
  147. this.InterestVar = (double) myDataTable.Compute("Var Interest_Rate)", "Date>#6/1/1954#");
  148. this.InterestStDev = (double) myDataTable.Compute("StDev(Interest_Rate)", "Date>#6/1/1954#");
  149. this.InterestCount = (int) myDataTable.Compute("Count(Interest_Rate)", "Date>#6/1/1954#");
  150. this.InterestMax = (decimal) myDataTable.Compute("Max(Interest_Rate)", "Date>#6/1/1954#");
  151. this.InterestMin = (decimal) myDataTable.Compute("Min(Interest_Rate)", "Date>#6/1/1954#");
  152. this.InterestAvg = (decimal) myDataTable.Compute("Avg(Interest_Rate)", "Date>#6/1/1954#");
  153. this.InterestSum = (decimal) myDataTable.Compute("SUM(Interest_Rate)", "Date>#6/1/1954#");
  154. }
  155. }
  156. }