What is the most efficient method to calculate total numbers in a DataTable Column

Question

I have a DataTable with a Column of numbers called x value

I want to return the sum value that column.

The column will always have a number in it (so no need to worry about blank / null values).

My code:

            int x = 0;

            foreach (DataRow dr in SqlCon.ds.Tables[3].Rows)
            {
                foreach (DataColumn dc in  SqlCon.ds.Tables[3].Columns)
                {
                    if (dc.ColumnName == "x value")
                    {
                        //calc value
                    }

                }
            }

Many Thanks,

ps. have looked on SO already but cannot find the duplicate (I assume it is about but it's Monday morning and my google skill aren't the best!)

The most concise: `int sum = dataTable.AsEnumerable().Sum(row => (int)row["x"]);` — Alexander Petrov, Jul 03 '17 at 10:38
@AlexanderPetrov submit as answerand ill mark it as correct as this has resolved my issue. Not that educated on Linq but seems to of worked! Thanks — SCramphorn, Jul 03 '17 at 10:43
@AlexanderPetrov https://stackoverflow.com/questions/3156059/is-a-linq-statement-faster-than-a-foreach-loop foreach is more efficient than linq, although less readable. — Zein Makki, Jul 03 '17 at 10:49
If you are really interested in performance (*most efficient* is just as unclear as *best*), since you have a `SqlCon` you should probably ditch the DataTable alltogether and go for native SQL. It will surely be faster than anything with DataTable. — grek40, Jul 03 '17 at 10:53
Impressive if the "Community" bot now can close duplicates successfully (as in "closing them correct"). Have never seen it before. — davidkonrad, Jul 03 '17 at 10:57

score 1 · Accepted Answer · answered Jul 03 '17 at 10:43

1

You'll need to loop over each row, that's for sure. However, you can get the column index once to avoid unnecessary looping for the columns:

int sum = 0;
DataSet ds = new DataSet();

DataTable dt = ds.Tables[3];
int columnIndex = dt.Columns["x column"].Ordinal;
foreach (DataRow dr in dt.Rows)
{
    sum += Convert.ToInt32(dr[columnIndex]);
}

answered Jul 03 '17 at 10:43

Zein Makki

29,485
6
52
63

What's the advantage of using that over: `int sum = dataTable.AsEnumerable().Sum(row => (int)row["x"]);` Thanks – SCramphorn Jul 03 '17 at 10:44
@SCramphorn foreach is faster than linq (Since you're is targeting effeciency): https://stackoverflow.com/questions/3156059/is-a-linq-statement-faster-than-a-foreach-loop – Zein Makki Jul 03 '17 at 10:45
@SCramphorn and it is for sure faster than `.Compute()` try them out and compare on large data. – Zein Makki Jul 03 '17 at 10:47
Many Thanks. I will try – SCramphorn Jul 03 '17 at 10:50
Just tried and makes a lot more sense when I stepped through it. So in the end it was the Convert.ToInt32 I needed, but in terms of performance i understand. – SCramphorn Jul 03 '17 at 10:54

score 1 · Answer 2 · answered Jul 03 '17 at 10:44

object sumOfX = SqlCon.ds.Tables[3].Compute("Sum(x value)", null);

Then cast to appropriate number type.

See DataTable.Compute

Note: this might be efficient in terms of writing code, but there are probably more efficient approaches in terms of performance if you really need to squeeze the last clock cycle... but then again, using DataTable and C# might be the wrong choice.

What is the most efficient method to calculate total numbers in a DataTable Column

2 Answers2