Post
Ask Question

DataTable Compute Method

There may be times when we may need perform some mathematical operations in-memory to display in our UI. For example, if you have a rate and amount columns and need to calculate interest on the fly, you can use that within the DataTable. The DataTable.Compute() method can be used in such operations.
 
What is DataTable? 
 
A DataTable object in ADO.NET data classes represents an in-memory database table. The data in the table is usually loaded from either a database, a data source, or from a collection. A data table is a collection of columns and rows. A column in a DataTable represents the column name of a database table or a property of an object. A row in a DataTable reresents a record of data.  
  • A DataTable represents a single table in-memory.
  • DataTable is used for storing data. This data might be read from the database or dynamically generated.
  • Each row in DataTable represents a DataRow. 
Creating an Empty DataTable
 
The following code snippet creates a DataTable with no rows. 
  1. DataTable dt = new DataTable();  
  2. dt.TableName = "EMP";  
  3. dt.Columns.Add("Name",typeof(string)); // Datatype string  
  4. dt.Columns.Add("Salary",typeof(int)); // DataType int  
  5. dt.Columns.Add("Commission", typeof(int));  
Here we create a DataTable dt with three columns, i.e., Name, Salary, and Commission.
 
Inserting Rows to DataTable
 
A DataTable is a collection of DataRow objects. The following code snippet creates a DataRow, that must have same items as DataColumn of a DataTable.
 
DataTable.Rows is a collection of all rows in a DataTable. The DataRowCollection.Add() method adds a DataRow to the collection.
  
The following code snippet creates a DataRow, sets its column values, and adds to the collection. 
  1. DataRow dr = dt.NewRow(); //Creating a New Row  
  2. dr["Name"] = "Arnold";  
  3. dr["Salary"] = 10000;  
  4. dr["Commission"] = 20;  
  5. dt.Rows.Add(dr); // Add the Row to the DataTable  
  6. dr = dt.NewRow();  
  7. dr["Name"] = "Arnold";  
  8. dr["Salary"] = 3000;  
  9. dr["Commission"] = 15;  
  10. dt.Rows.Add(dr);  
DataTable Compute Method
 
The DataTable.Compute method is used to perform operations on a DataTable data. It It can be used to perform aggregate operations on a row data.
 
Syntax 
  1. public Object Compute(  
  2. string expression,  
  3. string filter  
  4. )  
Here expression is the expression to compute and filter is similar to the where condition clause of SQL. 
 
Performing Operations using Compute Method
 
Let's find out average of salary in of a DataTable. 
 
Snippet: 
  1. // Declare an object variable.  
  2. object AvgTotal;  
  3. AvgTotal = dt.Compute("Avg(Salary)", "");  
  4. MessageBox.Show("Average Salary is : " + AvgTotal.ToString());  
Here, the Filter condition is empty indicating that all rows are computed.
 
There might be situation, where you need to perform computations based on one or columns, in such cases you need to create a DataColumn.
 
Snippet:
  1. // Create a DataColumn and Set Properties  
  2. DataColumn column = new DataColumn();  
  3. column.DataType = typeof(int);  
  4. column.Expression = "Salary * Commission";  
  5. column.ColumnName = "Total";  
  6. dt.Columns.Add(column);  
  7. // Declare an object variable.  
  8. object sumTotal;  
  9. sumTotal = dt.Compute("Sum(Total)", "");  
  10. MessageBox.Show("Total Salary is : " + sumTotal.ToString());  
Thanks for reading.