.NET Core Web API SQL Server stored procedures

Question

I'm trying to create a .NET Core Web Rest API to connect Infopath forms to it. The idea that I have is to to capture data from the form and store it to SQL Server, since I want to do some complex things besides just insert to table, I would like to use stored procedures. I would also like to query some information to the forms or/and some reports.

The questions is that I don't know if this is the right approach.

This is my test controller:

using System.Collections.Generic;
using Microsoft.AspNetCore.Mvc;
using ct3PR.Models;

namespace ct3PR.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class BaseController : ControllerBase
    {
        private readonly baseContext _context;

        public BaseController(baseContext context) => _context = context;

        [HttpGet("{id}")]
        public ActionResult<Base> GetBasesItem(int id)
        {
            var baseItem = _context.BaseItems.Find(id);

            if(baseItem == null)
            {
                return NotFound();
            }

            return baseItem;
        }
    }
}

I already have a test database from which I can query:

var baseItem = _context.BaseItems.Find(id);

The problem is that I want to send parameters to the stored procedure, but Find only searches by primary key. I want to send a range of dates, a name, an id and a range of dates etc.

Could someone guide me in the right direction?

Answer 1

You could use FromSqlRaw in EF core to call stored procedure like this:

CREATE PROCEDURE [dbo].[GetBaseItems]
@id int = 0,
@name varchar(50)

Action:

var baseItem = _context.BaseItems.FromSqlRaw("Execute dbo.GetBaseItems @id = {0} ,@name = {1}", id, "itemName");

Answer 2

var itemsList = _context.BaseItems.FromSqlRaw("EXEC SPGetItemsListById @ItemId={0}",Id).ToList();