How to create web Api execute stored procedure

ahmed salah 3,216 Reputation points
2021-07-06T11:25:57.833+00:00

I work on SQL server 2012 and web API entity framework .NET core 2.2 so I face issue I can't implement web API execute stored Procedure below

Create proc ItemCalculateStock
@OptionId int=NULL,
@ItemId  int = NULL,
@InventoryLocation int=NULL
as
begin

if(@OptionId=1)
begin

SELECT i.itemName,l.InventoryName, SUM(case when QTY > 0  then QTY else 0 end)  as PurchasedItem,SUM(case when QTY < 0  then -QTY else 0 end)  as ConsumItems,SUM(case when QTY > 0 then QTY else 0 end) + SUM(case when QTY < 0 then QTY else 0 end) as remaining  

FROM [dbo].[Invenroty] n with(nolock)
inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
and i.id=case when @ItemId is null then n.itemid else @ItemId end 

GROUP BY i.itemName,l.InventoryName
end
else
begin

SELECT i.itemName,l.InventoryName,PostingDate, case when QTY > 0  then QTY else 0 end  as PurchasedItem,case when QTY < 0  then -QTY else 0 end  as ConsumItems,case when QTY > 0 then QTY else 0 end - case when QTY < 0 then QTY else 0 end as remaining  
FROM [dbo].[Invenroty] n with(nolock)
inner join [dbo].[InventoryLocations] l with(nolock) on l.id=n.InventoryLocID
inner join [dbo].[Items] i with(nolock) on n.itemid=i.id
inner join [dbo].[TransactionTypes] t with(nolock) on n.transactionTypeId=t.ID and InventoryLocID=case when @InventoryLocation is null then n.InventoryLocID else @InventoryLocation end
and i.id=case when @ItemId is null then n.itemid else @ItemId end 


end
end 

so How to get result of stored procedure on web API using Entity Framework .NET core 2.2

[HttpGet("CalculateInventoryData")]

    public IActionResult CalculateInventoryData([FromQuery]int optionId, [FromQuery] int ItemId, [FromQuery] int InventoryLocation)
    {
    // here how to get stored procedure result here
    // so i ask question to know how to get result of stored procedure above
    }

to call API I use the link below :

https://localhost:44374/api/Inventory/getInventoryData?optionId=1&ItemId=2&InventoryLocation=1
ASP.NET Core
ASP.NET Core
A set of technologies in the .NET Framework for building web applications and XML web services.
4,133 questions
{count} votes