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.

DataTable dt = new DataTable();
dt.TableName = "EMP";
dt.Columns.Add("Name",typeof(string)); // Datatype string
dt.Columns.Add("Salary",typeof(int)); // DataType int
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.

DataRow dr = dt.NewRow(); //Creating a New Row
dr["Name"] = "Arnold";
dr["Salary"] = 10000;
dr["Commission"] = 20;
dt.Rows.Add(dr); // Add the Row to the DataTable
dr = dt.NewRow();
dr["Name"] = "Arnold";
dr["Salary"] = 3000;
dr["Commission"] = 15;
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

public Object Compute(
string expression,
string filter
)

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:

// Declare an object variable.
object AvgTotal;
AvgTotal = dt.Compute("Avg(Salary)", "");
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:

// Create a DataColumn and Set Properties
DataColumn column = new DataColumn();
column.DataType = typeof(int);
column.Expression = "Salary * Commission";
column.ColumnName = "Total";
dt.Columns.Add(column);
// Declare an object variable.
object sumTotal;
sumTotal = dt.Compute("Sum(Total)", "");
MessageBox.Show("Total Salary is : " + sumTotal.ToString());

Thanks for reading.

2 Comments

Join the conversation! Your thoughts help the community grow.

Code Alone9y ago
Can I use Math functions here like ROUND(SUM([Field Name]),0). We are getting error "The expression contains undefined function call ROUND()."
Shahzan Murtuza12y ago
What if I compute avg and query result a single row with null Salary??