How to call the stored procedure in blazor wasm?

Question

Hi,

In another post I created a stored procedure (thanks to @AggaveJoe) to increment a value / to the current year

Strored procedure

CREATE PROCEDURE [dbo].[CountECR]  
              
   AS  
   BEGIN  
    DECLARE @Id INT;  
    DECLARE @year INT;  
    SET @year =  YEAR(GETDATE());  
                  
    IF EXISTS (SELECT (1) FROM Developers WHERE [Year] = @year)  
     SELECT @id = MAX(Id) + 1 FROM Developers;  
    ELSE  
     SET @id = 1;  
                  
    INSERT INTO Developers (Id, [Year])  
    VALUES (@id, @year)  
              
    SELECT FORMAT(Id, '000') + '/' + SUBSTRING(CAST([Year] AS VARCHAR), 3, 2)   
    FROM Developers  
    WHERE DeveloperId = @id  
   END  

I would like to know if you can help me to call this stored procedure in blazor wasm when I click on the button create

DevelopperController

 [Route("api/[controller]")]  
    [ApiController]  
    public class DeveloperController : ControllerBase  
    {  
        private readonly ApplicationDBContext _context;  
  
        public DeveloperController(ApplicationDBContext context)  
        {  
            this._context = context;  
        }  
  
        [HttpGet]  
        public async Task<IActionResult> Get()  
        {             
            var devs = await _context.Developers.ToListAsync();  
            return Ok(devs);  
        }  
  
        [HttpGet("{id}")]  
        public async Task<IActionResult> Get(int id)  
        {  
            var dev = await _context.Developers.FirstOrDefaultAsync(a => a.Id == id);  
            return Ok(dev);  
        }  
  
        
        [HttpPost]  
        public async Task<IActionResult> Post(Developer developer)  
        {  
            _context.Add(developer);  
            await _context.SaveChangesAsync();  
            return Ok(developer.Id);  
        }  
        [HttpPut]  
        public async Task<IActionResult> Put(Developer developer)  
        {  
            _context.Entry(developer).State = EntityState.Modified;  
            await _context.SaveChangesAsync();  
            return NoContent();  
        }  
  
        [HttpDelete("{id}")]  
        public async Task<IActionResult> Delete(int id)  
        {  
            var dev = new Developer { Id = id };  
            _context.Remove(dev);  
            await _context.SaveChangesAsync();  
            return NoContent();  
        }  
    }  

Form Create

<FormCreate ButtonText="Create ECR" dev="@dev"  
            OnValidSubmit="@CreateDeveloper"/>  
  
@code {  
    Developer dev = new Developer();  
  
    async Task CreateDeveloper()  
    {  
        await http.PostAsJsonAsync("api/developer", dev);  
        uriHelper.NavigateTo("developer");  
    }  
  
}  

EditCreate.razor

<EditForm Model="@dev" OnValidSubmit="@OnValidSubmit">  
    <DataAnnotationsValidator />  
  
   <RadzenTextBox style="width: 100%;" Name="Numéro ECR" @bind-Value=@dev.ECR>  
                             
                        </RadzenTextBox>     
                      <ValidationMessage For="@(() => dev.ECR)" />  
  
@code {  

Developer[] developers { get; set; }  


//I think it's here I've to call the procedure to fill the textBox  
  
}  

FYI, I used the several source to create my application, I list below the different links
blazor-crud-with-entity-framework-core
blazor-api-handling
dashboard

Answer 1

Hi @sblb ,

EF Core provides the following methods to execute a stored procedure:

1. Using DbSet<TEntity>.FromSql() Suppose there have a GetStudents stored procedure. You can execute SP using FromSql
   method in EF Core in the same way as above, as shown below.

   var context = new SchoolContext();   
   
   var students = context.Students.FromSql("GetStudents 'Bill'").ToList();  
   

2. Using DbContext.Database.ExecuteSqlCommand() Supposer this have a CreateStudents stored procedure to insert students. Then, you could use the following code to call the SP. var context = new SchoolContext(); context.Database.ExecuteSqlCommand("CreateStudents @p0, @p1", parameters: new[] { "Bill", "Gates" });
   More detail information, please check the article:Working with Stored Procedure in Entity Framework Core. Besides, you could also use the Raw SQL Queries to execute the stored procedure.

So, you can according to the Stored Procedure output data to create the entity.

Then, in the API controller, create the action method and use the above method to execute the stored procedure and get the query result. Refer to my reply in this link: ASP.NET Core 3.1 : using stored procedures with view model

Finally, in the Blazor component, use HttpClient to call the API method, and populate the textbox based on the API response data (refer to the code in the Form Create or this article: Call a web API from ASP.NET Core Blazor).

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,
Dillion