Computation on DataTable rows with DataTable Compute Method

Rajnilari2015
Posted by in C# category on for Beginner level | Points: 250 | Views : 1779 red flag
Rating: 5 out of 5  
 1 vote(s)

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.


 Download source code for Computation on DataTable rows with DataTable Compute Method

Recommendation
Read Mathematical Expression Evaluation with NCalc before this article.

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.

Recommendation
Read Map Domain Objects using Automapper after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Posted by: Sheonarayan on: 5/2/2016 | Points: 25
Wow, I had written DataTable article long back http://www.dotnetfunda.com/articles/show/131/datatable-adding-modifying-deleting-filtering-sorting-rows-readingwrit had no idea DataTable can do this computation also.

Very very strong and powerful method.

Thanks for sharing this Niladri.

Login to post response

Comment using Facebook(Author doesn't get notification)