DataTable Compute Method (C#)

Note to self: Going forward use DataTable Compute when summing data in a DataTable (C#)

I am a little embarrassed to admit the number of times, prior to today, that I have used a loop to get summary information from a DataTable. Needless to say, in retrospect, one time was too many times.

So today while looking for a better way to sum data from a very large amount of rows I ran across the DataTable Compute Method, which allows you to get aggregated data out of data tables efficiently. The following aggregate types are supported in expression:

•  Sum (Sum)
•  Avg (Average)
•  Min (Minimum)
•  Max (Maximum)
•  Count (Count)
•  StDev (Statistical standard deviation)
•  Var (Statistical variance)

Example code and screen cap of the results shown below.

static void Main()
{
    DataTable dt = new DataTable();
    dt = GetPeopleTable();

    // Get total number of records in a dataset (no filter)
    object countRows = dt.Compute("Count(FirstName)", "");
    Console.WriteLine("Record Count = " + countRows);

    // Get total number of records where the birthdates after 1/1/2000 (filtered) 
    object birthsAfter2k = dt.Compute("Count(FirstName)", "BirthDate > '1/1/2011'");
    Console.WriteLine("# of birth dates > 1/1/2000: " + birthsAfter2k);

    // Get the earliest birth date in data table
    object earliestBirthDate = dt.Compute("Min(BirthDate)", "");
    Console.WriteLine("Earliest birth date = " + earliestBirthDate);

    // Get the latest birth date in data table
    object latestBirthDate = dt.Compute("Max(BirthDate)", "");
    Console.WriteLine("Latest birth date = " + latestBirthDate);

    // Get the number of records where last name = Ford  (filtered)
    object lastNameIsFord = dt.Compute("Count(LastName)", "LastName = 'Ford'");
    Console.WriteLine("# of names that equal Ford = " + lastNameIsFord);

    // Get the sum of an entire column in a data table
    object combinedAges = dt.Compute("Sum(Age)", "");
    Console.WriteLine("Combined Ages = " + combinedAges);

    Console.ReadLine();
}

private static DataTable GetPeopleTable()
{
    DataTable table = new DataTable();
    table.Columns.Add("FirstName", typeof(string));
    table.Columns.Add("LastName", typeof(string));
    table.Columns.Add("BirthDate", typeof(DateTime));
    table.Columns.Add("Age", typeof(int));

    table.Rows.Add("Joe", "Dirt", "12/1/1966", 49);
    table.Rows.Add("Bob", "Oldheart", "1/11/1970", 45);
    table.Rows.Add("Stan", "Ford", "3/3/1958", 57);
    table.Rows.Add("Dan", "Dunn", "8/7/1981", 34);
    table.Rows.Add("Sue", "Clay", "11/11/2011", 4);
    table.Rows.Add("Wendy", "Lisa", "7/5/2014", 1);
    return table;
}

And the results look like this

Lear more about the DataTable Compute method at MSDN.