Post

# Compute Feature of DataTable

• 132.3k
• 0
• 1

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
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>
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.                   }
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
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
129.                   }
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. }

Recommended Free Ebook
Similar Articles