How to call stored procedure in c# web api

In this section, we will discuss how to create a stored procedure using SQL Server and add that stored procedure in your Web API application using Entity Framework.

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

Step-1: Create Tables

Create first Table "dbo.Tbl_Student_Api" :

CREATE TABLE [dbo].[Tbl_Student_Api] (
    [Student_Id]       INT          IDENTITY (1, 1) NOT NULL,
    [Student_Name]     VARCHAR (50) NULL,
    [Student_Age]      INT          NULL,
    [Student_Gender]   VARCHAR (50) NULL,
    [Student_Religion] VARCHAR (50) NULL,
    PRIMARY KEY CLUSTERED ([Student_Id] ASC)
);

and Second Table "dbo.Tbl_Address_Api":

CREATE TABLE [dbo].[Tbl_Address_Api] (
    [Address_Id] INT          IDENTITY (1, 1) NOT NULL,
    [Address]    VARCHAR (50) NULL,
    [City]       VARCHAR (50) NULL,
    [District]   VARCHAR (50) NULL,
    [Country]    VARCHAR (50) NULL,
    [Student_Id] INT          NULL,
    PRIMARY KEY CLUSTERED ([Address_Id] ASC),
    FOREIGN KEY ([Student_Id]) REFERENCES [dbo].[Tbl_Student_Api] ([Student_Id])
);

Step-2: Create a Stored Procedure

Create a Stored Procedure "dbo.API_CALL_STDDTL".

CREATE PROCEDURE [dbo].[API_CALL_STDDTL]
 @MODE VARCHAR(50)=NULL,
 @Address_Id INT=NULL, 
    @Address VARCHAR(50)=NULL, 
    @City VARCHAR(50)= NULL, 
    @District VARCHAR(50)= NULL, 
    @Country VARCHAR(50)= NULL,
 @Student_Name VARCHAR(50)= NULL,
 @Student_Age INT= NULL,
 @Student_Gender VARCHAR(50)= NULL,
 @Student_Religion VARCHAR(50)= NULL,
 @Student_Id INT=NULL
 AS
 BEGIN
 IF(@MODE='RETRIEVE')
 BEGIN
 IF(@Student_Id IS NULL)
 BEGIN
 SELECT * FROM Tbl_Student_Api A INNER JOIN Tbl_Address_Api B ON A.Student_Id=B.Student_Id
 END
 ELSE
 BEGIN
 SELECT * FROM Tbl_Student_Api A INNER JOIN Tbl_Address_Api B ON A.Student_Id=B.Student_Id WHERE A.Student_Id=@Student_Id
 END
 END
 ELSE IF(@MODE='INSERT')
 
 BEGIN
 INSERT INTO Tbl_Student_Api(Student_Name,Student_Age,Student_Gender,Student_Religion) VALUES(@Student_Name,@Student_Age,@Student_Gender,@Student_Religion)
 INSERT INTO Tbl_Address_Api(Address,City,District,Country,Student_Id) VALUES(@Address,@City,@District,@Country,(SELECT MAX(Student_Id) FROM Tbl_Student_Api))
 END
 
 ELSE IF(@MODE='UPDATE')
 BEGIN
 UPDATE Tbl_Student_Api SET Student_Name=@Student_Name,Student_Age=@Student_Age,Student_Gender=@Student_Gender,Student_Religion=@Student_Religion WHERE Student_Id=@Student_Id 
 UPDATE Tbl_Address_Api SET Address=@Address,City=@City,District=@District,Country=@Country WHERE Student_Id=@Student_Id;
 END
 
 ELSE IF(@MODE='DELETE')
 BEGIN
 DELETE FROM Tbl_Address_Api WHERE Student_Id=@Student_Id
 DELETE FROM Tbl_Student_Api  WHERE Student_Id=@Student_Id
 
 END
 END

Step-3:

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

Step-4:

Then add Entity "InductionNewEntities1" in Model Folder.

convert Stored Procedure to Function in entity Model.

And add a class "std_dtl.cs" in model. And write this below code in "std_dtl.cs" class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace WebAPI.Models
{
    public class std_dtl
    {
        public string MODE { get; set; }
        public int Address_Id { get; set; }
        public string Address { get; set; }
        public string City { get; set; }
        public string District { get; set; }
        public int Student_Id { get; set; }
        public string Student_Name { get; set; }
        public Nullable<int> Student_Age { get; set; }
        public string Student_Gender { get; set; }
        public string Student_Religion { get; set; }
        public string Country { get; set; }
    }
}

Step-5:

Add a Controller "StoredController" in the controller folder. And write this code in "StoredController" controller.

using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Text;
using System.Web.Http;
using WebAPI.Models;
namespace WebAPI.Controllers
{
    public class StoredController : ApiController
    {
        InductionNewEntities1 ob = new InductionNewEntities1();
        // GET: api/Stored
        public HttpResponseMessage Get()
        {
            try
            {
                var MODE = new SqlParameter("@MODE", "RETRIEVE");
                var courseList = ob.Database.SqlQuery<std_dtl>("exec API_CALL_STDDTL @MODE= '" + MODE.Value + "'").ToList<std_dtl>();
                return new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new StringContent(JArray.FromObject(courseList).ToString(), Encoding.UTF8, "application/json")
                };
            }
            catch (Exception)
            {
                return new HttpResponseMessage(HttpStatusCode.BadRequest);
            }
        }
        // GET: api/Stored/5
        public HttpResponseMessage Get(int?id)
        {
            try
            {
                var MODE = new SqlParameter("@MODE", "RETRIEVE");
                var Student_Id = new SqlParameter("@Student_Id", id);
                var courseList = ob.Database.SqlQuery<std_dtl>("exec API_CALL_STDDTL @MODE='"+MODE.Value+"', @Student_Id="+Student_Id.Value+"").ToList<std_dtl>();
                return new HttpResponseMessage(HttpStatusCode.OK)
                {
                    Content = new StringContent(JArray.FromObject(courseList).ToString(), Encoding.UTF8, "application/json")
                };
            }
            catch (Exception)
            {
                return new HttpResponseMessage(HttpStatusCode.BadRequest);
            }
        }
        // POST: api/Stored
        public HttpResponseMessage Post(std_dtl value)
        {
            try
            {
                ob.API_CALL_STDDTL1(value.MODE, null, value.Address, value.City, value.District, value.Country, value.Student_Name, value.Student_Age, value.Student_Gender, value.Student_Religion, null);
                return new HttpResponseMessage(HttpStatusCode.OK);
            }
            catch (Exception)
            {
                return new HttpResponseMessage(HttpStatusCode.BadRequest);
            }
        }
        // PUT: api/Stored/5
        public HttpResponseMessage Put(int?id,std_dtl value)
        {
            try
            {
                ob.API_CALL_STDDTL1(value.MODE, null, value.Address, value.City, value.District, value.Country, value.Student_Name, value.Student_Age, value.Student_Gender, value.Student_Religion,Convert.ToInt32(id));
                return new HttpResponseMessage(HttpStatusCode.OK);
            }
            catch (Exception)
            {
                return new HttpResponseMessage(HttpStatusCode.BadRequest);
            }
        }
        // DELETE: api/Stored/5
        public HttpResponseMessage DELETE(int?id)
        {
            try
            {
                var MODE = new SqlParameter("@MODE", "DELETE");
                var Student_Id = new SqlParameter("@Student_Id", id);
                var courseList = ob.Database.SqlQuery<std_dtl>("exec API_CALL_STDDTL @MODE='DELETE', @Student_Id=" + Convert.ToInt32(Student_Id.Value) + "").ToList<std_dtl>();
                return new HttpResponseMessage(HttpStatusCode.OK);
            }
            catch (Exception)
            {
                return new HttpResponseMessage(HttpStatusCode.BadRequest);
            }
        }
    }
}

Output: