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

1. What is a DataTable in C#?
2. Explain what the compute method of DataTable is and how to calculate the sum of the DataTable column using the compute method.
3. Explain how to calculate the sum of the DataTable column using LINQ.

Implementation of Data Table

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.

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

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.

Example

DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[4]
{
    new DataColumn("ProductId", typeof(int)),
    new DataColumn("ProductName", typeof(string)),
    new DataColumn("Qty", typeof(int)),
    new DataColumn("Price", typeof(int))
});
dt.Rows.Add(1, "TV", 1, 45000);
dt.Rows.Add(2, "Mobile", 1, 32000);
dt.Rows.Add(3, "Laptop", 1, 90000);
dt.Rows.Add(4, "Keyboard", 2, 3300);
dt.Rows.Add(5, "WashingMachine", 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.

decimal DecimalTotalPrice = Convert.ToDecimal(dt.Compute("SUM(Price)", string.Empty));
decimal DecimalTotalPriceFiltered = Convert.ToDecimal(dt.Compute("SUM(Price)", "ProductId > 3"));

Example

int totalPrice = dt.AsEnumerable().Sum(row => row.Field<int>("Price"));
int totalPriceFiltered = 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#.