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
beginif(@OptionId=1)
beginSELECT 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
beginSELECT 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
Sachin SinghPosted Jul 6, 2021, 2:41 PM
ahmed elbarbaryPosted Jul 6, 2021, 12:11 PM
Sachin SinghPosted Jul 6, 2021, 11:44 AM
create a class that can map to the result of SP, if you already have an entity that can map to the result of SP then directly use this
ahmed elbarbaryPosted Jul 6, 2021, 11:38 AM
Satya KarkiPosted Jul 6, 2021, 11:34 AM
Rijwan AnsariPosted Jul 6, 2021, 11:32 AM
Sachin SinghPosted Jul 6, 2021, 11:30 AM