Calculate each column total and display using AngularJS and Web API in ASP.Net

Hi skp,

Check the below code.

You need to return JSON string from the API.

Here in the example i have used JSON.Net library. You can download the JSON.Net library from the following URL.

Download JSON.Net

Inside the GetCustomers method i have to get the result in the DataTable.

Then looping through all the columns present in the DataTable you have to check the DataType.

If the DataType is integer then calculate the sum. Then at last add the rows containing the sum for each column to the DataTable with new row.

Check this example. Now please take its reference and correct your code.

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.3.9/angular.min.js"></script>
<script type="text/javascript">
    var app = angular.module('MyApp', [])
    app.controller('MyController', function ($scope, $http, $window) {
        $scope.Customers = [];
        $http.post("Default.aspx/GetCustomers", { headers: { 'Content-Type': 'application/json'} })
        .then(function (response) {
            $scope.Customers = eval(response.data.d);
        }, function error(response) {
            alert(response.responseText);
        });
    });
</script>
<div ng-app="MyApp" ng-controller="MyController">
    <table>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Price1</th>
            <th>Price2</th>
        </tr>
        <tbody ng-repeat="m in Customers">
            <tr>
                <td>{{m.Id}}</td>
                <td>{{m.Name}}</td>
                <td>{{m.Price1}}</td>
                <td>{{m.Price2}}</td>
            </tr>
        </tbody>
    </table>
</div>

Namespaces

C#

using System.Data;
using System.Web.Services;
using Newtonsoft.Json;

VB.Net

Imports System.Data
Imports System.Web.Services
Imports Newtonsoft.Json

Code

C#

[WebMethod]
public static string GetCustomers()
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Price1",typeof(int)),
                        new DataColumn("Price2",typeof(int)) });
    dt.Rows.Add(1, "John Hammond", 10, 20);
    dt.Rows.Add(2, "Mudassar Khan", 15, 25);
    dt.Rows.Add(3, "Suzanne Mathews", 18, 23);
    dt.Rows.Add(4, "Robert Schidner", 12, 17);
    DataRow dr = dt.NewRow();
    for (int i = 0; i < dt.Columns.Count; i++)
    {
        string columnName = dt.Columns[i].ColumnName;
        // Checking DataType.
        if (dt.Columns[i].DataType.Name == "Int32")
        {
            int sum = Convert.ToInt32(dt.Compute("SUM(" + columnName + ")", String.Empty));
            dr[columnName] = sum;
        }
        else
        {
            dr[columnName] = "0";
        }
    }
    // Adding each column sum.
    dt.Rows.Add(dr);

    return JsonConvert.SerializeObject(dt, Formatting.Indented, new JsonSerializerSettings { ReferenceLoopHandling = ReferenceLoopHandling.Ignore });
}

VB.Net

<WebMethod()>
Public Shared Function GetCustomers() As String
    Dim dt As DataTable = New DataTable()
    dt.Columns.AddRange(New DataColumn() {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Price1", GetType(Integer)), New DataColumn("Price2", GetType(Integer))})
    dt.Rows.Add(1, "John Hammond", 10, 20)
    dt.Rows.Add(2, "Mudassar Khan", 15, 25)
    dt.Rows.Add(3, "Suzanne Mathews", 18, 23)
    dt.Rows.Add(4, "Robert Schidner", 12, 17)
    Dim dr As DataRow = dt.NewRow()
    For i As Integer = 0 To dt.Columns.Count - 1
        Dim columnName As String = dt.Columns(i).ColumnName
        If dt.Columns(i).DataType.Name = "Int32" Then
            Dim sum As Integer = Convert.ToInt32(dt.Compute("SUM(" & columnName & ")", String.Empty))
            dr(columnName) = sum
        Else
            dr(columnName) = "0"
        End If
    Next
    dt.Rows.Add(dr)
    Return JsonConvert.SerializeObject(dt, Formatting.Indented, New JsonSerializerSettings With {
        .ReferenceLoopHandling = ReferenceLoopHandling.Ignore
    })
End Function

Screenshot

I have just modified my stored procedure and making some changes in the web api, thus arrived to a solution.

SELECT SUM(NUMBERS) 'TotalQty', SUM(NoofPallets) 'TotalPallets' FROM #TEMPWH 

        [HttpGet]
        [ActionName("GetTotalQuantity")]
        public string GetTotalPallets(string InvoiceNo)
        {
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ERPConnectionString"].ConnectionString;
            SqlCommand sqlCmd = new SqlCommand();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandText = "GET_WH_RECEIPT";
            sqlCmd.Connection = myConnection;
            sqlCmd.Parameters.AddWithValue("@InvoiceNo", InvoiceNo);
            SqlDataAdapter sda = new SqlDataAdapter(sqlCmd);
            DataSet ds = new DataSet();
            string jsonString = string.Empty;
            myConnection.Open();
            sda.Fill(ds);
            myConnection.Close();
            jsonString = JsonConvert.SerializeObject(ds.Tables[1]);
            return jsonString;
        }