Implementation of ASP.NET Core, Web API with Entity Framework call Stored Procedure
Step 1, Create a ASP.NET Core Web API application (see
Part I-D);
Step 2, Set up database;
Step 3, Create Entity classses;
Step 4, Set up DbContext and data connection;
Step 5, Installing Microsoft.EntityFrameworkCore.SqlServer from NuGet Package Manager;
Step 6, Create Controller to access stored procedure from entity framework;
Step 2 - Set up test database
Create a testing SQL server database named "DB_Demo_APIl" which will be utilized in ASP.NET Core Web API application by executing the following SQL scripts to create a table and a Stored procedure,
Table: Appointments
USE [DB_Demo_API]
GO
/****** Object: Table [dbo].[appointments] Script Date: 12/6/2020 12:12:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[appointments](
[AppointmentID] [int] IDENTITY(1,1) NOT NULL,
[ReturnCode] [int] NOT NULL,
CONSTRAINT [PK_appointments] PRIMARY KEY CLUSTERED
(
[AppointmentID] 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
Stored Procedure: CreateAppointment
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.
The basic point we simulate is the stored procedure with some input parameters and output parameters.
Step 3 - Create Entity Classes: for the input and output parameters of the stored procedure
Add a new folder, Models, under visual studio project, and add another folder, DB, under Models, and add two classes input.cs and output.cs such as,
Input Class for input parameters of stored procedure,
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace WebAPICallSP.Models.DB
{
public class Input
{
public int ClinicId { get; set; }
public string AppointmentDate { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public int PatientId { get; set; }
public string AppointmentStartTime { get; set; }
public string AppointmentEndTime { get; set; }
}
}
Output class for output parameters of stored procedure,
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
#nullable disable
namespace WebAPICallSP.Models.DB
{
public partial class output
{
[Key]
public int AppointmentId { get; set; }
public int ReturnCode { get; set; }
public DateTime SubmittedTime { get; set;}
}
}
Step 4 - Set up DbContext and data connection
Add class DB_Demo_APIContext under Models/DB,
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
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"ConnectionStrings": {
"DevConnection": "Data Source=localhost;Initial Catalog=DB_Demo_API;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
},
"AllowedHosts": "*"
}
Note, here, we use local database, i.e., server: localhost, and Windows authetication. If you use it the same way, you can just copy the code into your "appsettings.json" file.
However, if you use different database or SQL Server Authentication, you must use the database connection like this,
"Server=SQL SERVER;Database=DATABASE;Trusted_Connection=True;user id=SQL USERNAME;password=SQL PASSWORD;"
Finally, you need to register SQL database configuration Context as service,
// 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
Click Browse, then search Microsoft.EntityFrameworkCore.SqlServer, then install,
After installation, rebuild the project again, the errors will be gone.
Step 6 - Create Controller to access stored procedure from entity framework
From Visual Studio IDE,
-
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.
So far, we have all files:
- 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)
The last thing we need to do is modify the controller to access the stored procedure. Note, we create the controller based on the output entity object. So, the controller will create CRUD actions for us, i.e., GET/POST/PUT/DELETE method to access database. However, the output entity object is created based on the output of the stored procedure CreateAppointment --- there is no table or entity object in the database called output. That means the controller will not work.
Now, we modify the outputsController, in order to make it working on call database stored procedure, CreateAppointment.
0, Delete all actions excep the first one: GET,
// GET: api/outputs
[HttpGet]
public async Task<ActionResult<IEnumerable<output>>> Getoutput()
{
return await _context.output.ToListAsync();
}
We realize the output is just what we want. However, the result is from a native table of output that does not exist. We need to call stored procedure CreateAppointment to get the output.
- Add input parameter into the action;
- Make a string storedProc to hold the SQL command exceted in the SQL Server;
- Use the FromSqlRaw command to call the stored procedure;
- Change the Get verb to Post, this is due to the Get method cannot bring input parameters from body.
The new code is like this
// 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
![]()
Click the button Post, then Try it, you will get this page,
Execute, and you will get a result like this,
[
{
"appointmentId": 2,
"returnCode": 1,
"submittedTime": "2020-12-06T15:19:10.87"
}
]
Conclusion
In this article, you learned to call stored procedures by using ASP.NET Core and entity framework. This method is simple and universal, you can use that to call any stored procedure, with a simple entity framework like method, and take the advantage of ASP.NET Core. You will avoid all the trouble work of ADO.NET and the need to format the input / output like the previous version of Web API.
And in
Part I, we reviewed the microservice architecture and its evolution, also the importance of Web API in the microservice architecture.