ASP.Net Web API to get record count using Stored Procedure in C# and VB.Net

Hi skp,

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

Here i am using WebService get data from Stored Procedure.

You need to call the Web API url that return Json string using Newtonsoft Json Library.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

SQL

-- EXEC [dbo].[Get_Country_Count]
CREATE PROCEDURE [dbo].[Get_Country_Count]
AS
BEGIN
	SELECT (SELECT COUNT(*) FROM Employees WHERE Country = 'USA') AS 'USA'
	,(SELECT COUNT(*) FROM Employees WHERE Country = 'UK') AS 'UK' 
END

HTML

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/angularjs/1.6.9/angular.min.js"></script>
<script type="text/javascript">
    var app = angular.module('MyApp', []);
    app.controller('MyController', function ($scope, $http) {
        $http.post("WebService.asmx/GetDataCount", { headers: { 'Content-Type': 'application/json'} })
        .then(function (response) {
            var countryCount = eval(response.data.d);
            $scope.USA = countryCount[0].USA;
            $scope.UK = countryCount[0].UK;
        });
    });
</script>
<div ng-app="MyApp" ng-controller="MyController">
    <span>USA count is : <b>{{USA}}</b></span><br />
    <span>UK count is : <b>{{UK}}</b></span>
</div>

WebService

C#

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

[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService
{
    [WebMethod]
    public string GetDataCount()
    {
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection())
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
            using (SqlCommand cmd = new SqlCommand("Get_Country_Count"))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Connection = con;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    sda.Fill(dt);
                }
            }
        }

        return JsonConvert.SerializeObject(dt, Formatting.Indented);
    }
}

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports Newtonsoft.Json

' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Public Class WebService
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function GetDataCount() As String
        Dim dt As DataTable = New DataTable()
        Using con As SqlConnection = New SqlConnection()
            con.ConnectionString = ConfigurationManager.ConnectionStrings("constr").ConnectionString
            Using cmd As SqlCommand = New SqlCommand("Get_Country_Count")
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Connection = con
                Using sda As SqlDataAdapter = New SqlDataAdapter(cmd)
                    sda.Fill(dt)
                End Using
            End Using
        End Using

        Return JsonConvert.SerializeObject(dt, Formatting.Indented)
    End Function
End Class

Screenshot