如何创建web Api执行存储过程

Question

我在SQL server 2012和web API实体框架.NET核心2.2上工作所以我面临的问题是我不能实现web API执行下面的存储过程

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 

因此如何使用EntityFramework.NETcore2.2在Webneneneba API上获取存储过程的结果

[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
}

要调用API，我使用以下链接：

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

更新后

我尝试低于

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 

Answer 1

执行无参数存储过程的代码如下：

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;

执行带参数的存储过程的代码如下(我们可以将调用存储过程的函数声明为ExeProcedure(字符串输入日期((：

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

这将添加一个输入参数。如果需要添加输出参数：

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

获取存储过程的返回值：

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

有关更多信息，请参阅这篇文章：如何在实体框架核心中运行存储过程？