In this article, we are going to learn how to call store procedures in .Net core 5 using a code-first approach.
Prerequisites
Let’s first open Visual Studio 2019 and create a .NET 5.0 application by clicking Create a new project.
Then open your project and install two NuGet packages Microsoft.EntityFrameworkCore.SqlServer and Microsoft.EntityFrameworkCore.Tools, which are required for Code First migration.
Now create an Entities folder and inside that folder create an Emplyee.cs and Salary.cs model class of record type and EmployeeDbContext.cs class that will inherit to DbContext class, then add the following code to it.
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace CodeFirstDemo.Entites
{
public record EmployeeMaster
{
[Key]
public int Id { get; set; }
public string EmpName { get; set; }
public string MobileNumber { get; set; }
[ForeignKey("SalaryMaster")]
public int SalaryId { get; set; }
public SalaryMaster SalaryMaster { get; set; }
}
}
using CodeFirstDemo.Extentions;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
namespace CodeFirstDemo.Entites
{
public record SalaryMaster
{
[Key]
public int Id { get; set; }
public decimal Amount { get; set; }
public SalaryType SalaryType { get; set; }
public ICollection<EmployeeMaster> EmployeeMasters { get; set; }
}
}
using CodeFirstDemoNew.Models;
using Microsoft.EntityFrameworkCore;
namespace CodeFirstDemo.Entites
{
public class CodeFirstContext : DbContext
{
public CodeFirstContext(DbContextOptions options) : base(options)
{
}
public virtual DbSet<EmployeeMaster> EmployeeMaster { get; set; }
public virtual DbSet<SalaryMaster> SalaryMaster { get; set; }
}
}
Open the appsettings.json file and add the code to it.
{
"ConnectionStrings": {
"SqlConnection": "Server=DESKTOP-78L71550; database=EmployeeDemoDB; User Id=sa; Password=thecodehubs;"
},
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft": "Warning",
"Microsoft.Hosting.Lifetime": "Information"
}
},
"AllowedHosts": "*"
}
Open the Startup.cs file and add the code to it.
using CodeFirstDemo.Entites;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.OpenApi.Models;
namespace CodeFirstDemoNew
{
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)
{
services.AddDbContext<CodeFirstContext>(item => item.UseSqlServer(Configuration.GetConnectionString("SqlConnection")));
services.AddControllers();
services.AddSwaggerGen(c =>
{
c.SwaggerDoc("v1", new OpenApiInfo { Title = "CodeFirstDemoNew", 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", "CodeFirstDemoNew v1"));
}
app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();
app.UseEndpoints(endpoints =>
{
endpoints.MapControllers();
});
}
}
}
Now, open the package manager console to fire the commands.
- Add-Migration MigrationName
Add-Migration Initial
- Update-Database
Update-Database
Then your table is created you can see in your database.
Now create the GetAllEmployee Store procedure with the help of code first approche.
First, create blank migration using the following command.
Add-Migration Add_GetAllEmployee_SP
Then add your Store procedure to it.
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.Sql(@"
CREATE OR ALTER PROCEDURE GetAllEmployees
As
BEGIN
SELECT
e.Id,
e.EmpName,
e.MobileNumber,
s.Amount
FROM
EmployeeMaster AS e
JOIN SalaryMaster AS s ON e.SalaryId = s.Id
END");
}
Then Update-Database.
Then add View Model.
namespace CodeFirstDemoNew.Models
{
public class EmployeeVM
{
public int Id { get; set; }
public string EmpName { get; set; }
public string MobileNumber { get; set; }
public decimal Amount { get; set; }
}
}
To call SP you have to add this view model in DbContext.
using CodeFirstDemoNew.Models;
using Microsoft.EntityFrameworkCore;
namespace CodeFirstDemo.Entites
{
public class CodeFirstContext : DbContext
{
public CodeFirstContext(DbContextOptions options) : base(options)
{
}
public virtual DbSet<EmployeeMaster> EmployeeMaster { get; set; }
public virtual DbSet<SalaryMaster> SalaryMaster { get; set; }
//Add view models for store procedure
public virtual DbSet<EmployeeVM> EmployeeVM { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<EmployeeVM>().HasNoKey().ToView(null);
}
}
}
To use it you need to first mark your class EmployeeVM with [Keyless] data annotation or through fluent configuration with .HasNoKey() method call like above.
This will help not to create this table in the database.
Now create API to call this Sp in our controller.
using CodeFirstDemo.Entites;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Linq;
namespace CodeFirstDemoNew.Controllers
{
[Route("api/[controller]")]
[ApiController]
public class EmployeeController : ControllerBase
{
private readonly CodeFirstContext _context;
public EmployeeController(CodeFirstContext context)
{
_context = context;
}
[HttpGet]
[Route("GetAllEmployee")]
public IActionResult GetAllEmployee()
{
var employeeDetail = _context.EmployeeVM.FromSqlRaw("GetAllEmployees").ToList();
return Ok(employeeDetail);
}
}
}
That’s it.
Output
Also check,the Minimal API CRUD In .Net Core 6
