ASP.NET Web API : Return JSON object by implementing multiple stored procedure

WEB API: The ASP.NET Web API is an extensible framework for building HTTP based services that can be accessed in different applications on different platforms such as web, windows, mobile, etc. It works more or less the same way as the ASP.NET MVC web application except that it sends data as a response instead of an HTML view. Read more about Web API

Web API supports different formats of response data. Built-in support for JSON, XML, BSON format. Here we will use the JSON format response.

Microsoft SQL Server: We use SQL Server as a database server, The primary function of SQL Server is storing and retrieving data.

In this article, we will use Web API and SQL Server for Getting the JSON format response using multiple stored procedures. Here I use two stored procedures inside Web API. 

Let's create a Web API with ASP.NET and the latest version of the Entity Framework and also using Stored Procedures. Please follow these below steps:

Step-1: 

Create a New Database "DB_Demo_API" in SQL Server. Then Create tables inside this database. Here I created two tables first one is "[dbo].[Tbl_Country]" and another is "[dbo].[Tbl_State]". Follow the below SQL query for creating these two tables.

1.

CREATE TABLE [dbo].[Tbl_Country](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Country_Name] [varchar](50) NULL,
 CONSTRAINT [PK_Tbl_Country] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

2.

CREATE TABLE [dbo].[Tbl_State](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [State_Name] [varchar](50) NULL,
 [Country_Id] [int] NULL,
 CONSTRAINT [PK_Tbl_State] PRIMARY KEY CLUSTERED
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 

ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Step-2:
Create two Stored Procedures inside that database "DB_Demo_API".

1.

CREATE PROCEDURE [dbo].[Get_All_Country]
 AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
   Select * from [dbo].[Tbl_Country]
END

2.

CREATE PROCEDURE [dbo].[Get_All_State]
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
    -- Insert statements for procedure here
 SELECT a.Id as StateId,a.State_Name,a.Country_Id,b.Country_Name  

 from [dbo].[Tbl_State] as a
 join [dbo].[Tbl_Country] as b on a.[Country_Id]=b.Id
END

Step-3:

Open Visual Studio, and select menu File > New > Project > Visual C# - Web > ASP.NET  Web Application
Set the project name WebBlogApi and click OK.
Select the "WebAPI" template, set "No Authentication", unchecked "Host in the cloud" options, and click OK.
After that, the WebAPI Solution will be created.

Step-4:
Follow the below steps for creating ADO.NET Entity Data Model:

Then the Entity "DB_Demo_APIEntities" added in your solution Model Folder. After adding entity all stored procedures and tables will add in this entity.

The Entity Connectionstring will automatically be added in the "web.config" file.
e.g.:

<connectionStrings>
    <add name="DB_Demo_APIEntities" 

 connectionString="metadata=res://*/Models.Model1.csdl|res://*/Models.Model1.ssdl|res://*/Models.Model1.msl;

provider=System.Data.SqlClient;

provider connection string="data source=ODMPARISHMITAPC\SQLEXPRESS;initial catalog=DB_Demo_API;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework"" 

providerName="System.Data.EntityClient" />
  </connectionStrings>

Add an empty API Controller "DefaultController" inside the controller folder. For calling two stored procedures in one object create class file "CommonCls" inside "DefaultController".

Write this code in the "DefaultController" controller.

using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Web.Http;
using WebBlogApi.Models;

namespace WebBlogApi.Controllers
{

    public class DefaultController : ApiController
    {
        DB_Demo_APIEntities DbContext = new DB_Demo_APIEntities();
        public class CommonCls
        {
            public List<Get_All_Country_Result> country { get; set; }
            public List<Get_All_State_Result> state { get; set; }
        }
        [HttpGet]
        public HttpResponseMessage Get()
        {
            try
            {
                var country = DbContext.Get_All_Country().ToList();
                var states = DbContext.Get_All_State().ToList();
                CommonCls cm = new CommonCls();
                cm.country = country;
                cm.state = states;
                return new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new StringContent(JObject.FromObject(cm).ToString(), Encoding.UTF8, "application/json")
                };
            }
            catch (Exception)
            {
                return new HttpResponseMessage(HttpStatusCode.BadRequest);
            }
        }
    }
}

Output: