ASP.NET Core, Web API - Entity Framework Call Stored Procedure Implementation

Step 2 - Set up test database

USE [DB_Demo_API]  
GO  
/****** Object:  StoredProcedure [dbo].[CreateAppointment]    Script Date: 12/6/2020 12:15:02 PM ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
  
CREATE PROCEDURE [dbo].[CreateAppointment]  
    -- Add the parameters for the stored procedure here  
                @ClinicID int,  
                @AppointmentDate varchar,  
                @FirstName varchar,  
                @LastName varchar,  
                @PatientID int,  
                @AppointmentStartTime varchar,  
                @AppointmentEndTime varchar  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  
  
    -- Insert statements for procedure here  
    INSERT appointments VALUES(1)  
  
    SELECT top 1 AppointmentID, ReturnCode, getdate() SubmittedTime  
    FROM appointments  
    order by appointmentID desc  
END  

The Stored procedure is a virtual stored procedure that inserts a row value into a table and returns back the newly-created appointment ID. Actually, this simulates a stored procedure, that could be with any complex actions in it, such as INSERT, UPDATE, DELETE, OR SELECT. We give the simple form here, you can replace it with any complex one in the real world.

using System;  
using Microsoft.EntityFrameworkCore;  
using Microsoft.EntityFrameworkCore.Metadata;  
using WebAPICallSP.Models.DB;  

#nullable disable  
  
namespace WebAPICallSP.Models.DB  
{  
    public partial class DB_Demo_APIContext : DbContext  
    {  
        public DB_Demo_APIContext()  
        {  
        }  
  
        public DB_Demo_APIContext(DbContextOptions<DB_Demo_APIContext> options)  
            : base(options)  
        {  
        }  
  
    }  
} 

Add database connection string,

"Data Source=localhost;Initial Catalog=DB_Demo_API;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False" 

into file "appsettings.json", like this

// Register SQL database configuration context as services.   
services.AddDbContext<DB_Demo_APIContext>(options =>  
{  
    options.UseSqlServer(Configuration.GetConnectionString("DevConnection"));  
}); 

in "Startup" file, like this

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.OpenApi.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using WebAPICallSP.Models.DB;
namespace WebAPICallSP {
    public class Startup {
        public Startup(IConfiguration configuration) {
            Configuration = configuration;
        }
        public IConfiguration Configuration {
            get;
        }
        // This method gets called by the runtime. Use this method to add services to the container.  
        public void ConfigureServices(IServiceCollection services) {
            // Register SQL database configuration context as services.   
            services.AddDbContext < DB_Demo_APIContext > (options => {
                options.UseSqlServer(Configuration.GetConnectionString("DevConnection"));
            });
            services.AddControllers();
            services.AddSwaggerGen(c => {
                c.SwaggerDoc("v1", new OpenApiInfo {
                    Title = "WebAPICallSP", Version = "v1"
                });
            });
        }
        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.  
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env) {
            if (env.IsDevelopment()) {
                app.UseDeveloperExceptionPage();
                app.UseSwagger();
                app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json", "WebAPICallSP v1"));
            }
            app.UseHttpsRedirection();
            app.UseRouting();
            app.UseAuthorization();
            app.UseEndpoints(endpoints => {
                endpoints.MapControllers();
            });
        }
    }
}

Now, you can build your project, however, you will get errors in both class Startup and class DB_Demo_APIContext. This is because you do not have references to EntityFrameworkCore.

Step 5 - Installing Microsoft.EntityFrameworkCore.SqlServer from NuGet Package Manager

From Visual Studio IDE, click Tools->NuGet Package Manager->Manage NuGet Packages for Solution

Step 6 - Create Controller to access stored procedure from entity framework

• Right-click the Controllers folder.
• Select Add > New Scaffolded Item Or Controller to open the window Add Scaffolded Item
• Select API from the left hand side under Common;
• Select API Controller with actions, using Entity Framework, and then select Add.
• In the Add API Controller with actions, using Entity Framework dialog:
  • Select Output (WebAPICallSP.Models.DB) in the Model class.
  • Select DB_Demo_APIContext (WebAPICallSP.Models.DB) in the Data context class.
  • Select Add.

• Models/DB/input.cs (added)
• Models/DB/output.cs (added)
• Models/DB/DB_Demo_APIContext.cs (added)
• Controller/outputsController.cs (added)
• appsettings.json (modified)
• Startup.cs (modified)

1. Add input parameter into the action;
2. Make a string storedProc to hold the SQL command exceted in the SQL Server;
3. Use the FromSqlRaw command to call the stored procedure;
4. Change the Get verb to Post, this is due to the Get method cannot bring input parameters from body.

// POST: api/outputs  
[HttpPost]  
public async Task<ActionResult<IEnumerable<output>>> Getoutput(Input input)  
{  
    string StoredProc = "exec CreateAppointment " +  
            "@ClinicID = " + input.ClinicId + "," +  
            "@AppointmentDate = '" + input.AppointmentDate + "'," +  
            "@FirstName= '" + input.FirstName + "'," +  
            "@LastName= '" + input.LastName + "'," +  
            "@PatientID= " + input.PatientId + "," +  
            "@AppointmentStartTime= '" + input.AppointmentStartTime + "'," +  
            "@AppointmentEndTime= '" + input.AppointmentEndTime + "'";  
  
    //return await _context.output.ToListAsync();  
    return await _context.output.FromSqlRaw(StoredProc).ToListAsync();  
} 

and the outputsController is like this

using System.Collections.Generic;  
using System.Threading.Tasks;  
using Microsoft.AspNetCore.Mvc;  
using Microsoft.EntityFrameworkCore;  
using WebAPICallSP.Models.DB;  
  
namespace WebAPICallSP.Controllers  
{  
    [Route("api/[controller]")]  
    [ApiController]  
    public class outputsController : ControllerBase  
    {  
        private readonly DB_Demo_APIContext _context;  
  
        public outputsController(DB_Demo_APIContext context)  
        {  
            _context = context;  
        }  
  
        // POST: api/outputs  
        [HttpPost]  
        public async Task<ActionResult<IEnumerable<output>>> Getoutput(Input input)  
        {  
            string StoredProc = "exec CreateAppointment " +  
                    "@ClinicID = " + input.ClinicId + "," +  
                    "@AppointmentDate = '" + input.AppointmentDate + "'," +  
                    "@FirstName= '" + input.FirstName + "'," +  
                    "@LastName= '" + input.LastName + "'," +  
                    "@PatientID= " + input.PatientId + "," +  
                    "@AppointmentStartTime= '" + input.AppointmentStartTime + "'," +  
                    "@AppointmentEndTime= '" + input.AppointmentEndTime + "'";  
  
            //return await _context.output.ToListAsync();  
            return await _context.output.FromSqlRaw(StoredProc).ToListAsync();  
        }  
  
    }  
}  

Now, running the program, you will see the page