Computation on DataTable rows with DataTable Compute Method

Introduction

A Datatable is a single in-memory table.It provides various methods out of which one is Compute which evaluates the given expression on the current rows that pass the filter criteria. In this article, we will look into various kinds of computation that the Compute method supports.

Using the code

Let us first open a console application and have the below data table in place with some random data populated

using System;
using System.Data;
using System.Linq;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var dtSource = GetSource(); //get the data source
        }


        /// <summary>
        /// Function: GetSource()
        /// Purpose: Create a datatable with some sample data
        /// </summary>
        /// <returns></returns>
        private static DataTable GetSource()
        {
            var dt = new DataTable("RNADataTable");           
            dt.Columns.Add("Name", typeof(string)); // datatype: string
            dt.Columns.Add("EmpId", typeof(int)); // datatype: int
            dt.Columns.Add("Age", typeof(int));  // datatype: int
            dt.Columns.Add("Salary", typeof(int)); // datatype: int

            //insert 20 rows
            Enumerable
                .Range(1, 20)
                .ToList()
                .ForEach(i =>
                {
                    var dr = dt.NewRow(); //create a new row
                    dr["Name"] = "Name" + i.ToString();
                    dr["EmpId"] = 100+i;
                    dr["Age"] = 18+i;
                    dr["Salary"] = 50000 + i * 100;
                    dt.Rows.Add(dr);
                });

            return dt;
        }
    }
}

Example of Aggregate functions

1. Find the Average Salary of all Employees

   Console.WriteLine("Average Salary is : " + dtSource.Compute("Avg(Salary)", "").ToString());

   Result: Average Salary is : 51050

2. Find the Average Salary of those Employees whose EmpID > 115 i.e. for EmpID in (116,117,118,119,120)

    Console.WriteLine("Average Salary is : " + dtSource.Compute("Avg(Salary)", "EmpID > 115").ToString());

   Result: Average Salary is : 51800

   The Compute method's signature is

   public Object Compute(string expression,string filter)
   			

   where, expression is the expression to compute and filter is the criteria to be specify for restricting the rows.

3. Find the Total Salary of all Employees

   Console.WriteLine("Total Salary is : " + dtSource.Compute("Sum(Salary)", "").ToString());

   Result: Total Salary is : 1021000

4. Find the min Salary of all Employees

   Console.WriteLine("Miminum Salary is : " + dtSource.Compute("Min(Salary)", "").ToString());

   Result: Miminum Salary is : 50100

5. Find the max Salary of all Employees

   Console.WriteLine("Maximum Salary is : " + dtSource.Compute("Max(Salary)", "").ToString());

   Result: Maximum Salary is : 52000

6. Find the count employees

   Console.WriteLine("Total Employee : " + dtSource.Compute("Count(EmpId)", "").ToString());

   Result: Total Employee : 20

Example of Bitwise Operations

Though it is not directly possible to perform the bitwise operations inside the Compute function but we can how ever apply some trick to do so e.g.

1. Left shift

   int a = 60;   
   Console.WriteLine("Left shift : " + dtSource.Compute("Convert(" + a + "*4, 'System.Int32')", "").ToString());

   Result: Left shift : 240

2. Right shift

   int a = 60;   
   Console.WriteLine("Right shift : " + dtSource.Compute("Convert(" + a + "/4, 'System.Int32')", "").ToString());

   Result: Right shift : 15

3. Bitwise OR

   Console.WriteLine("Bitwise OR : " + dtSource.Compute("IIF(Convert(60/14, 'System.Int32')%2 = 0, 60+2, 60)", "").ToString());

   Result: Bitwise OR : 62

Example of Arithmetic Operations

Console.WriteLine("Result : " + dtSource.Compute("((99*9) + (77+89)*20) / 45", "").ToString()); //Result: 93.5777777777778

Example of Relational Operations

int x = 21;
int y = 10;           
Console.WriteLine(dtSource.Compute(x + "=" + y, "").ToString()); //Result:  False
Console.WriteLine(dtSource.Compute(x + "<" + y, "").ToString()); //Result: False
Console.WriteLine(dtSource.Compute(x + "<=" + y, "").ToString()); //Result: False
Console.WriteLine(dtSource.Compute(x + ">" + y, "").ToString()); //Result: True
Console.WriteLine(dtSource.Compute(x + ">=" + y, "").ToString()); //Result: True
Console.WriteLine(dtSource.Compute(x + "<>" + y, "").ToString()); //Result: True

Example of Logical Operations

Console.WriteLine(dtSource.Compute("True Or False And True And True", "").ToString()); //Result:  True

Example of In Operator

Find the total Salary of those Employees whose EmpID in (116,117,118,119,120)

Console.WriteLine("Total Salary is : " + dtSource.Compute("Sum(Salary)", "EmpID in (116,117,118,119,120)").ToString());

Example of Like Operator

Find the total Salary of those Employees whose Name contains 2 e.g. Name2, Name12, Name20

Console.WriteLine("Total Salary is : " + dtSource.Compute("Sum(Salary)", " Name LIKE '*2*'").ToString());

Result: Total Salary is : 153400

Example of IIF, AND, OR

Console.WriteLine("Result: " + dtSource.Compute(" iif( 100 > 20,((( 19 / 1 ) > 10) AND (( 1 / 1 ) <= 3500) OR (( 500 / -34 ) > 1234)),iif(1=0,1,0) )", null).ToString());

Result: True

References

DataTable.Compute Method

Conclusion

In this article, we have seen how powerful the Compute method of Datatable is and we can do multiple mathematical operations with this.Hope this will be helpful.Thanks for reading.Zipped file attached.