Compute Feature of DataTable

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:
  • Sum (Sum)
  • Avg (Average)
  • Min (Minimum)
  • Max (Maximum)
  • Count (Count)
  • StDev (Statistical standard deviation)
  • Var (Statistical variance)
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. }