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 classes;
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]
-
- @ClinicID int,
- @AppointmentDate varchar,
- @FirstName varchar,
- @LastName varchar,
- @PatientID int,
- @AppointmentStartTime varchar,
- @AppointmentEndTime varchar
- AS
- BEGIN
-
-
- SET NOCOUNT ON;
-
-
- 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,
-
- 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; }
-
-
- public void ConfigureServices(IServiceCollection 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" });
- });
- }
-
-
- 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,
-
- [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
-
- [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.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;
- }
-
-
- [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.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.