How to execute stored procedure in ASP.NET Core Web API | Entity Framework core

In this Tutorial, We will learn How to execute the stored procedure in ASP.NET Core Web API with Entity Framework core and SQL Server. It is web service that allows clients to access and manipulate data stored in a database. The API provides a set of endpoints that clients can use to perform the operation on the data.

Entity framework Core provides the following methods to execute a stored procedure.

• DbSet<TEntity>.FromSql()
• DbContext.Database.ExecuteSqlCommand()

Install Required Software

• Download NET Core SDK from here
• Download Visual Studio or Visual Studio Code from here

Create ASP.NET Core Web API Project

• Open Visual Studio and Create a new project.
• Select ASP.NET Core Web Application and give it a name.
• Choose the API template ad select Create

Install NuGet Packages

• Microsoft.EntityFrameworkCore.SqlServer
• Microsoft.EntityFrameworkCore.Tools

Create the Model

          Now you need to create a model that represents the data you want to store in the SQL Server database.

Create Folder Models of your project and Create Class student with properties like sFirstname, sLastname, address etc. As per your you can change.

using System.ComponentModel.DataAnnotations;

namespace Execute_storedProcedure_DotnetCore.Models
{
    public class student
    {
        [Key]
        public int enrollId { get; set; }
        public string sFirstname { get; set; }
        public string sLastname { get; set;}
        public string address { get; set; }
    }
}

Also check previous article How to Create Web API (CRUD) in Asp.net Core With PostgreSQL

Create the Database Context

The Database context is responsible for communicating with the SQL Server database.

• Create a new  Class studentDbConext that inherits from DbContext.
• Add a DbSet property for each table you want to access.

using Microsoft.EntityFrameworkCore;

namespace Execute_storedProcedure_DotnetCore.Models
{
    public class studentDbConext : DbContext
    {
        public studentDbConext(DbContextOptions<studentDbConext> options):base(options)
        {

        }            
        public virtual DbSet<student> student { get; set; }
    }
}

Configure SQL Server

          SQL Server with your ASP.NET Core Web API, you need to configure the connection string in the appsettings.json file.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "sqlconnect": "Server=(local)\\MSSQL;Database=schoolmgmt;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "AllowedHosts": "*"
}

Configure Program.cs

using Execute_storedProcedure_DotnetCore.Models;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllers();

builder.Services.AddDbContext<studentDbConext>(cnn => cnn.UseSqlServer(builder.Configuration.GetConnectionString("sqlconnect")));

var app = builder.Build();

// Configure the HTTP request pipeline.

app.UseHttpsRedirection();

app.UseAuthorization();

app.MapControllers();

app.Run();

Migrations

 Run migrations

Add-Migration
Update-Database

Stored Procedure

USE [schoolmgmt]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[Student] 
@Id INT= NULL
	
AS
BEGIN
	
	SET NOCOUNT ON;

	IF @Id > 0 
	BEGIN
   	SELECT * FROM Students WHERE enrollId=@Id;
	END
	ELSE
	BEGIN
	SELECT * FROM Students;
	END	   
END

Create the Controller

Create the controller that will handle HTTP request to the API. You can do this by create a new class in the controllers folder of your project.

using Execute_storedProcedure_DotnetCore.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace Execute_storedProcedure_DotnetCore.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class StudentController : ControllerBase
    {
        private readonly studentDbConext _dbcontext;
        public StudentController(studentDbConext dbConext)
        {
            _dbcontext = dbConext;
        }

        [HttpGet]
        public async Task<IActionResult> Get()
        {
            var studentList = await _dbcontext.student.FromSqlRaw("Exec Student").ToListAsync();
            return Ok(studentList);
        }
        [HttpGet("{Id}")]
        public async Task<IActionResult> GetById(int Id)
        {
            var Sqlstr = "EXEC Student @Id=" + Id;
            var studentList = await _dbcontext.student.FromSqlRaw(Sqlstr).ToListAsync();
            return Ok(studentList);

        }
    }
}

Test the API With Postman

Run the application and test each API endpoint using a tool like Postman.

Get All

Get By Id