Calculate The SUM of the DataTable Column in ASP.NET

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.

In my previous article, I explained read CSV file in asp.net with example c# and vb.net and export dataset/Datatable to CSV file using c# and vb.net and export JSON to CSV using jquery/javascript and bootstrap in asp.net and export all the excel sheets to a dataset in c# and vb.net as well as export JSON data to excel/CSV file using angular js with bootstrap many other articles on ASP.NET, C#, and VB.NET as well as How to Export DataTable to CSV using C#.

This is a common requirement for all the 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 developing an e-commerce website, and working on implement add-to-cart functionality and as per the requirement you want to calculate the subtotal product amount or total product and etc.

Requirement

1) What is DataTable in c#?

2) Explain What is compute method of DataTable and how to calculate the sum of the DataTable column using compute method.

3) Explain how to calculate the sum of the DataTable column using LINQ.

Implementation

So, let's start with the DataTable and understand What is DataTable in c# and the use of the DataTable in ASP.NET.

What is 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 compute method?

Compute method is a method of DataTable that is used to perform aggregate operations on a raw data of 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 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 compute method of DataTable.

DataTable

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, "Washing Machine", 1, 18500);

Here, we have created a sample DataTable with few dummy data for demonstration, as per the given data let's calculate the subtotal of the product price.

DataTable Compute method

Decimal TotalPrice = Convert.ToDecimal(dt.Compute("SUM(Price)", string.Empty));

Decimal TotalPrice = Convert.ToDecimal(dt.Compute("SUM(Price)", "ProductId > 3"));

LINQ

int TotalPrice = dt.AsEnumerable().Sum(row => row.Field<int>("Price"));

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 SUM(Price) as we want a subtotal of the product price. And 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 want to skip the first 2 items, So the price of the first 2 items will not include in the final subtotal of price.

Same as compute method, we used Enumerable.Sum is an extension method to calculate the subtotal of price and used where condition with Enumerable.Sum for calculates the subtotal of the price for an item having productid > 3.

Summary

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