Tutorial :how to calculate(sum up) an a row value of an datatable



Question:

i have an datatable like this. i am getting this data from an excel sheet and converted to datatable

id  workedhours  tfshours  1     3          2  2     5          5  3     .7         3  4      2         3.2  5     4.3       6.8   

now i need the sum of the column workedhours and tfshours

how can i achive that is .there any builtin function to get the sum of teh column

i need the result like this in a new datatable

 workedhours   tfshours  15                20  

any help would be greatly appreicated. thank you


Solution:1

Create a new Datatable and use the datatable.compute methods.

        DataTable sumDataTable = new DataTable();          sumDataTable.Columns.Add("total", typeof(string));          sumDataTable.Columns.Add("workedhours", typeof(int));          sumDataTable.Columns.Add("tfshours", typeof(int));            DataRow row = sumDataTable.NewRow();          row["total"] = "Total";          row["workedhours"] = oldDataTable.Compute("Sum(workedhours)", "workedhours > 0");          row["tfshours"] = oldDataTable.Compute("Sum(tfshours)", "tfshours > 0");          sumDataTable.Rows.Add(row);  

The second parameter, filter, determines which rows are used in the expression, but you could just put "" to select all rows.


Solution:2

There is buit-in method in DataTable object: DataTable.Compute

MSDN description


Solution:3

Try LinQ (sorry, just VB but could easily transfered to C#):

Dim sum_workedhours = (From x in myDB Select x.workedhours).Sum()  

Depending on your class you are using to save the database you may need to iterate the whole database... Maybe you would like to give us some more details


Solution:4

Using DataTable Methods:

decimal workedHours = (decimal)dataTable.Compute( "Sum(workedhours)", "" );     decimal tfsHours = (decimal)dataTable.Compute( "Sum(tfshours)", "" );  

Using Linq extensions:

decimal workedHours = dataTable.AsEnumerable().Sum(       row => decimal.Parse( row["workedhours"].ToString() ) );    decimal tfsHours = dataTable.AsEnumerable().Sum(       row => decimal.Parse( row["tfshours"].ToString() ) );  


Solution:5

    DataTable sumDataTable = new DataTable();      sumDataTable.Columns.Add("total", typeof(string));      sumDataTable.Columns.Add("workedhours", typeof(int));      sumDataTable.Columns.Add("tfshours", typeof(int));          DataRow row = sumDataTable .NewRow();                  for (int j = 0; j < sumDataTable .Columns.Count; j++)                  {                          if(sumDataTable .Columns[j].Caption!="total")                          row[j] = sumDataTable .Compute("Sum([" +                                                          sumDataTable .Columns[j].Caption + "])", "");                                         }                  sumDataTable .Rows.Add(row);  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »