Calculate The SUM Of The DataTable Column In C#

Introduction

 
In this article, I am going to explain how to calculate the sum of the DataTable column in ASP.NET using C#. Here, I'll also explain what is DataTable in C# as well as different kinds of ways to calculate the sum of the DataTable column, like using the compute method of DataTable as well as using LINQ.
 
 
This is a common requirement for all developers. When we working with the DataTable, sometimes we need to calculate the sum of the particular column of the datable as per the need. Let's take an example, you are developing an e-commerce website, and working on implementing an add-to-cart functionality. As per the requirement, you want to calculate the subtotal product amount or total product, etc.
 
Requirements
  • What is a DataTable in C#?
  • Explain what the compute method of DataTable is and how to calculate the sum of the DataTable column using the compute method.
  • Explain how to calculate the sum of the DataTable column using LINQ.

Implementation

 
So, let's start with the DataTable and understand what a DataTable in C# is and the use of the DataTable in ASP.NET.
 

What is a DataTable in C#?

 
The DataTable is an object in the ADO.NET that represents an in-memory table and provides a collection of rows and columns to store the data in a tabular manner.
 

Ways to calculate the sum of the DataTable column in ASP.NET

 
We can calculate the sum of the DataTable column using many multiple ways, Here, I'll explain two different ways, using the compute method of DataTable and using the LINQ.
 

DataTable Compute Method

 
What is the compute method?
 
The compute method is a method of DataTable that is used to perform aggregate operations on raw data of the DataTable.
 
Syntax
  1. public object? Compute(string expression, string filter);    
Explanation
 
As you can see in the given syntax, the compute method accepts two different parameters, expression, and filter where the expression indicates the aggregate operation like SUM, MIN, MAX and etc. and filter indicates the row limit same like where clause and will return an Object as a result of the computation.
 
Let's take an example to calculate the sum of the DataTable column using the compute method of DataTable.
 
DataTable
  1. DataTable dt = new DataTable();  
  2. dt.Columns.AddRange(new DataColumn[4] {  
  3.     new DataColumn("ProductId"typeof(int)),  
  4.         new DataColumn("ProductName"typeof(string)),  
  5.         new DataColumn("Qty"typeof(int)),  
  6.         new DataColumn("Price"typeof(int))  
  7. });  
  8. dt.Rows.Add(1, "TV", 1, 45000);  
  9. dt.Rows.Add(2, "Mobile", 1, 32000);  
  10. dt.Rows.Add(3, "Laptop", 1, 90000);  
  11. dt.Rows.Add(4, "Keyboard", 2, 3300);  
  12. dt.Rows.Add(5, "Washing Machine", 1, 18500);   
Here, we have created a sample DataTable with some dummy data for demonstration. As per the given data, let's calculate the subtotal of the product price.
 
DataTable Compute method
  1. Decimal TotalPrice = Convert.ToDecimal(dt.Compute("SUM(Price)", string.Empty));    
  2. Decimal TotalPrice = Convert.ToDecimal(dt.Compute("SUM(Price)""ProductId > 3"));     
LINQ
  1. int TotalPrice = dt.AsEnumerable().Sum(row => row.Field<int>("Price"));    
  2. int TotalPrice = dt.AsEnumerable().Where(row => row.Field<int>("ProductId") > 3).Sum(row => row.Field<int>("Price"));     
Explanation
 
As you can see in the code, in the DataTable compute method we used the SUM(Price) as we want a subtotal of the product price. You can also see that we also used a filter parameter, as we want only subtotal of only for that item that having productid > 3 and we want to skip the first 2 items. The price of the first 2 items will not be included in the final subtotal of the price.
 
Similar to the compute method, we used Enumerable.Sum as an extension method to calculate the subtotal of price and used a where condition with Enumerable.Sum to calculate the subtotal of the price for an item with productid > 3.
 

Summary

 
In this article, we learned how to calculate the sum of the DataTable column in ASP.NET using C#.