DataTable Compute Method

In this blog, we will learn how to use the compute Method in DataTable to perform common Aggregate Operations such as Avg, Sum, Min, Max, and Count Etc.

What is DataTable?

1. A DataTable represents a single Table in-memory.
2. DataTable is used for storing data. This data might be read from the database or dynamically generated.
3. Each row in DataTable represents a DataRow.

Creating an Empty DataTable

The Code Snippet below 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 Name, Salary, Commission.

Inserting Rows to DataTable

Prior Inserting, as you know each row in DataTable represent a DataRow. So we add rows to the DataTable using DataRow and DataTable NewRow() Method.

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 the Rows.

It can be used to perform aggregate operations on the DataTable.

Syntax

public Object Compute(
                    string expression,
                    string filter
)

Here expression is the expression to compute

Filter – The filter is similar to where condition clause in SQL Server to restrict rows.

Performing Operations using Compute Method

Let us Perform Avg of Salary on the DataTable which we have created.

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