How to create web Api execute stored procedure

Question

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
@ItemId  int = NULL,
@InventoryLocation int=NULL
as
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

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

updated Post

I try below

context.Database.ExecuteSqlCommand("ItemCalculateStock @OptionId ,@ItemId,@InventoryLocation ", parameters: new[] {optionId,ItemId,InventoryLocation});
 
i get error cannot implicitly convert type int into to Microsoft .aspnetcore.mvc.action result

so how to solve issue

score 2 · Accepted Answer · answered Jul 07 '21 at 07:58

The code to execute a stored procedure without parameters is as follows:

SqlConnection conn=new SqlConnection(“connectionString”);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = new SqlCommand();
da.SelectCommand.Connection = conn;
da.SelectCommand.CommandText = "NameOfProcedure";
da.SelectCommand.CommandType = CommandType.StoredProcedure;

The code to execute a stored procedure with parameters is as follows (we can declare the function that calls the stored procedure as ExeProcedure(string inputdate)):

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.Input;
param.Value = Convert.ToDateTime(inputdate);
da.SelectCommand.Parameters.Add(param);

This adds an input parameter. If you need to add output parameters:

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.Output;
param.Value = Convert.ToDateTime(inputdate);
da.SelectCommand.Parameters.Add(param);

To get the return value of the stored procedure:

param = new SqlParameter("@ParameterName", SqlDbType.DateTime);
param.Direction = ParameterDirection.ReturnValue;
param.Value = Convert.ToDateTime(inputdate);
da.SelectCommand.Parameters.Add(param);

For more information, please refer to this post:How to run stored procedures in Entity Framework Core?

How to create web Api execute stored procedure

1 Answers1