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()
Table of Contents
Install Required Software
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
