立即注册 登录
汉山 返回首页

翰山的个人空间 http://www.hanshan.info/?2 [收藏] [复制] [分享] [RSS]

日志

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

已有 5368 次阅读2020-12-9 12:40 |个人分类:Tech|系统分类:原创博文 | 宽屏 请点击显示宽屏,再点击恢复窄屏 | 动漫全图 如只见部分动漫,请点击显示全图,再点击恢复窄图

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


This article will continue from part I (ASP.NET Core, Web API: Entity Framewor call Stored Procedure) and give developers a practical way to build Web API with ASP.NET Core and Entity Framework to call Stored procedure. Part II will finish the implementation. 
 

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
  1. USE [DB_Demo_API]  
  2. GO  
  3.   
  4. /****** Object:  Table [dbo].[appointments]    Script Date: 12/6/2020 12:12:28 PM ******/  
  5. SET ANSI_NULLS ON  
  6. GO  
  7.   
  8. SET QUOTED_IDENTIFIER ON  
  9. GO  
  10.   
  11. CREATE TABLE [dbo].[appointments](  
  12.     [AppointmentID] [int] IDENTITY(1,1) NOT NULL,  
  13.     [ReturnCode] [intNOT NULL,  
  14.  CONSTRAINT [PK_appointments] PRIMARY KEY CLUSTERED   
  15. (  
  16.     [AppointmentID] ASC  
  17. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  18. ON [PRIMARY]  
  19. GO 
Stored Procedure: CreateAppointment
  1. USE [DB_Demo_API]  
  2. GO  
  3. /****** Object:  StoredProcedure [dbo].[CreateAppointment]    Script Date: 12/6/2020 12:15:02 PM ******/  
  4. SET ANSI_NULLS ON  
  5. GO  
  6. SET QUOTED_IDENTIFIER ON  
  7. GO  
  8. -- =============================================  
  9.   
  10. CREATE PROCEDURE [dbo].[CreateAppointment]  
  11.     -- Add the parameters for the stored procedure here  
  12.                 @ClinicID int,  
  13.                 @AppointmentDate varchar,  
  14.                 @FirstName varchar,  
  15.                 @LastName varchar,  
  16.                 @PatientID int,  
  17.                 @AppointmentStartTime varchar,  
  18.                 @AppointmentEndTime varchar  
  19. AS  
  20. BEGIN  
  21.     -- SET NOCOUNT ON added to prevent extra result sets from  
  22.     -- interfering with SELECT statements.  
  23.     SET NOCOUNT ON;  
  24.   
  25.     -- Insert statements for procedure here  
  26.     INSERT appointments VALUES(1)  
  27.   
  28.     SELECT top 1 AppointmentID, ReturnCode, getdate() SubmittedTime  
  29.     FROM appointments  
  30.     order by appointmentID desc  
  31. 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,
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5.   
  6. namespace WebAPICallSP.Models.DB  
  7. {  
  8.     public class Input  
  9.     {  
  10.         public int ClinicId { getset; }  
  11.         public string AppointmentDate { getset; }  
  12.         public string FirstName { getset; }  
  13.         public string LastName { getset; }  
  14.         public int PatientId { getset; }  
  15.         public string AppointmentStartTime { getset; }  
  16.         public string AppointmentEndTime { getset; }  
  17.     }  
  18. }  
Output class for output parameters of stored procedure,
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.ComponentModel.DataAnnotations;  
  4.  
  5. #nullable disable  
  6.   
  7. namespace WebAPICallSP.Models.DB  
  8. {  
  9.     public partial class output  
  10.     {  
  11.         [Key]  
  12.         public int AppointmentId { getset; }  
  13.         public int ReturnCode { getset; }  
  14.         public DateTime SubmittedTime { getset;}  
  15.     }  

Step 4 - Set up DbContext and data connection

 
Add class DB_Demo_APIContext under Models/DB,
  1. using System;  
  2. using Microsoft.EntityFrameworkCore;  
  3. using Microsoft.EntityFrameworkCore.Metadata;  
  4. using WebAPICallSP.Models.DB;  
  5.  
  6. #nullable disable  
  7.   
  8. namespace WebAPICallSP.Models.DB  
  9. {  
  10.     public partial class DB_Demo_APIContext : DbContext  
  11.     {  
  12.         public DB_Demo_APIContext()  
  13.         {  
  14.         }  
  15.   
  16.         public DB_Demo_APIContext(DbContextOptions<DB_Demo_APIContext> options)  
  17.             : base(options)  
  18.         {  
  19.         }  
  20.   
  21.     }  

Add database connection string,
  1. "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
  1. {  
  2.   "Logging": {  
  3.     "LogLevel": {  
  4.       "Default""Information",  
  5.       "Microsoft""Warning",  
  6.       "Microsoft.Hosting.Lifetime""Information"  
  7.     }  
  8.   },  
  9.   
  10.   "ConnectionStrings": {  
  11.     "DevConnection""Data Source=localhost;Initial Catalog=DB_Demo_API;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"  
  12.   },  
  13.   "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,
  1. "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,
  1. // Register SQL database configuration context as services.   
  2. services.AddDbContext<DB_Demo_APIContext>(options =>  
  3. {  
  4.     options.UseSqlServer(Configuration.GetConnectionString("DevConnection"));  
  5. }); 
 in "Startup" file, like this
  1. using Microsoft.AspNetCore.Builder;  
  2. using Microsoft.AspNetCore.Hosting;  
  3. using Microsoft.AspNetCore.HttpsPolicy;  
  4. using Microsoft.AspNetCore.Mvc;  
  5. using Microsoft.EntityFrameworkCore;  
  6. using Microsoft.Extensions.Configuration;  
  7. using Microsoft.Extensions.DependencyInjection;  
  8. using Microsoft.Extensions.Hosting;  
  9. using Microsoft.Extensions.Logging;  
  10. using Microsoft.OpenApi.Models;  
  11. using System;  
  12. using System.Collections.Generic;  
  13. using System.Linq;  
  14. using System.Threading.Tasks;  
  15. using WebAPICallSP.Models.DB;  
  16.   
  17. namespace WebAPICallSP  
  18. {  
  19.     public class Startup  
  20.     {  
  21.         public Startup(IConfiguration configuration)  
  22.         {  
  23.             Configuration = configuration;  
  24.         }  
  25.   
  26.         public IConfiguration Configuration { get; }  
  27.   
  28.         // This method gets called by the runtime. Use this method to add services to the container.  
  29.         public void ConfigureServices(IServiceCollection services)  
  30.         {  
  31.             // Register SQL database configuration context as services.   
  32.             services.AddDbContext<DB_Demo_APIContext>(options =>  
  33.             {  
  34.                 options.UseSqlServer(Configuration.GetConnectionString("DevConnection"));  
  35.             });  
  36.   
  37.             services.AddControllers();  
  38.             services.AddSwaggerGen(c =>  
  39.             {  
  40.                 c.SwaggerDoc("v1"new OpenApiInfo { Title = "WebAPICallSP", Version = "v1" });  
  41.             });  
  42.         }  
  43.   
  44.         // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.  
  45.         public void Configure(IApplicationBuilder app, IWebHostEnvironment env)  
  46.         {  
  47.             if (env.IsDevelopment())  
  48.             {  
  49.                 app.UseDeveloperExceptionPage();  
  50.                 app.UseSwagger();  
  51.                 app.UseSwaggerUI(c => c.SwaggerEndpoint("/swagger/v1/swagger.json""WebAPICallSP v1"));  
  52.             }  
  53.   
  54.             app.UseHttpsRedirection();  
  55.   
  56.             app.UseRouting();  
  57.   
  58.             app.UseAuthorization();  
  59.   
  60.             app.UseEndpoints(endpoints =>  
  61.             {  
  62.                 endpoints.MapControllers();  
  63.             });  
  64.         }  
  65.     }  

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,
  1. // GET: api/outputs  
  2. [HttpGet]  
  3. public async Task<ActionResult<IEnumerable<output>>> Getoutput()  
  4. {  
  5.     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. 
  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.
The new code is like this
  1. // POST: api/outputs  
  2. [HttpPost]  
  3. public async Task<ActionResult<IEnumerable<output>>> Getoutput(Input input)  
  4. {  
  5.     string StoredProc = "exec CreateAppointment " +  
  6.             "@ClinicID = " + input.ClinicId + "," +  
  7.             "@AppointmentDate = '" + input.AppointmentDate + "'," +  
  8.             "@FirstName= '" + input.FirstName + "'," +  
  9.             "@LastName= '" + input.LastName + "'," +  
  10.             "@PatientID= " + input.PatientId + "," +  
  11.             "@AppointmentStartTime= '" + input.AppointmentStartTime + "'," +  
  12.             "@AppointmentEndTime= '" + input.AppointmentEndTime + "'";  
  13.   
  14.     //return await _context.output.ToListAsync();  
  15.     return await _context.output.FromSqlRaw(StoredProc).ToListAsync();  

 and the outputsController is like this
  1. using System.Collections.Generic;  
  2. using System.Threading.Tasks;  
  3. using Microsoft.AspNetCore.Mvc;  
  4. using Microsoft.EntityFrameworkCore;  
  5. using WebAPICallSP.Models.DB;  
  6.   
  7. namespace WebAPICallSP.Controllers  
  8. {  
  9.     [Route("api/[controller]")]  
  10.     [ApiController]  
  11.     public class outputsController : ControllerBase  
  12.     {  
  13.         private readonly DB_Demo_APIContext _context;  
  14.   
  15.         public outputsController(DB_Demo_APIContext context)  
  16.         {  
  17.             _context = context;  
  18.         }  
  19.   
  20.         // POST: api/outputs  
  21.         [HttpPost]  
  22.         public async Task<ActionResult<IEnumerable<output>>> Getoutput(Input input)  
  23.         {  
  24.             string StoredProc = "exec CreateAppointment " +  
  25.                     "@ClinicID = " + input.ClinicId + "," +  
  26.                     "@AppointmentDate = '" + input.AppointmentDate + "'," +  
  27.                     "@FirstName= '" + input.FirstName + "'," +  
  28.                     "@LastName= '" + input.LastName + "'," +  
  29.                     "@PatientID= " + input.PatientId + "," +  
  30.                     "@AppointmentStartTime= '" + input.AppointmentStartTime + "'," +  
  31.                     "@AppointmentEndTime= '" + input.AppointmentEndTime + "'";  
  32.   
  33.             //return await _context.output.ToListAsync();  
  34.             return await _context.output.FromSqlRaw(StoredProc).ToListAsync();  
  35.         }  
  36.   
  37.     }  
  38. }  
 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,
  1. [  
  2.   {  
  3.     "appointmentId": 2,  
  4.     "returnCode": 1,  
  5.     "submittedTime""2020-12-06T15:19:10.87"  
  6.   }  

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.

鲜花

真棒

玩闹

同情

看看

困惑

震惊

bad

评论 (0 个评论)

facelist doodle 涂鸦板

您需要登录后才可以评论 登录 | 立即注册

Archiver|手机版|小黑屋|汉山网    

GMT-5, 2024-3-28 12:18 , Processed in 0.072179 second(s), 20 queries .

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

返回顶部