Click here to Skip to main content
15,886,518 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
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

SQL
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 ?
to call API I use the link below :

HTML
https://localhost:44374/api/Inventory/getInventoryData?optionId=1&ItemId=2&InventoryLocation=1


What I have tried:

ASP.NET
[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
    }
Posted

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900