question

ahmedsalah-1628 avatar image
0 Votes"
ahmedsalah-1628 asked ZhiLv-MSFT commented

How to create web Api execute stored procedure

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


dotnet-aspnet-core-mvcdotnet-aspnet-core-webapi
· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Executing raw queries is covered in the official Entity Framework docs. Keep in mind, 2.2 is out of support and you should consider upgrading rather than using deprecated methods. If I recall the 2.2 command was .FromSql() and the new command is .FromSqlRaw().

0 Votes 0 ·

i try like that

  public IActionResult CalculateInventoryData([FromQuery]int optionId, [FromQuery] int ItemId, [FromQuery] int InventoryLocation, [FromQuery] DateTime StartDate, [FromQuery] DateTime EndDate)
         {
             var result = _connection.Database.ExecuteSqlCommand("ItemCalculateStock @OptionId ,@ItemId,@InventoryLocation,@StartDate,@EndDate ", parameters: new[] {optionId, ItemId, InventoryLocation, StartDate, EndDate});
             return Ok(result);
         }

i get error not best type found
so how to solve this issue please


0 Votes 0 ·

It seems you did not read the linked documentation. The ExecuteSqlCommand is not designed to return a result set. Use the ExecuteReader and populate the type manually.

Otherwise, register the type in the DbContext and use the .FromSql() method.


0 Votes 0 ·
Show more comments

Hi @ahmedsalah-1628,

i get error not best type found

Can you post the detailed error message?

Here is an article about ExecuteSqlCommand method, it looks that your code is correct. but please check the limitations to use the ExecuteSqlCommand to execute the Stored procedure: Result must be an entity type. This means that a stored procedure must return all the columns of the corresponding table of an entity. Result cannot contain related data. This means that a stored procedure cannot perform JOINs to formulate the result. Insert, Update and Delete procedures cannot be mapped with the entity, so the SaveChanges method cannot call stored procedures for CUD operations.

0 Votes 0 ·

0 Answers