C# 实体框架：如何运行存储过程并返回值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11779155/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Entity Framework: how do I run a stored procedure and return a value?
提问by Funky
Is it possible to execute a stored procedure using the Entity Framework, passing in variables and returning data along with a return value?
是否可以使用实体框架执行存储过程，传入变量并返回数据和返回值？
Please do not leave any code about function mappings with Entity Framework.
请不要留下任何关于实体框架函数映射的代码。
I have tried this and I keep getting the same damn error:
我已经尝试过这个，但我不断收到同样该死的错误：
Procedure or function 'usp_BusinessUser_Search' expects parameter '@TotalRecords', which was not supplied.
过程或函数“usp_BusinessUser_Search”需要未提供的参数“@TotalRecords”。
Here's my code:
这是我的代码：
SqlParameter Business = new SqlParameter("Business", Search.Term);
SqlParameter Location = new SqlParameter("Location", Search.Location);
SqlParameter PageNumber = new SqlParameter("PageNumber", Search.CurrentPage);
SqlParameter RecordsPerPage = new SqlParameter("RecordsPerPage", Search.RecordsPerPage);
SqlParameter TotalRecords = new SqlParameter("TotalRecords", 0);
SqlParameter parm = new SqlParameter()
{
ParameterName = "@TotalRecords",
Value = 0,
SqlDbType = SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output
};
var List = db.ExecuteStoreQuery<ENT_SearchBusinessResult>("exec usp_BusinessUser_Search",Business,Location,PageNumber,RecordsPerPage,parm);
Does anyone have any idea what is causing this?
有谁知道是什么原因造成的？
Thanks
谢谢
EDIT:
编辑：
Stored procedure code:
存储过程代码：
ALTER PROCEDURE [dbo].[usp_BusinessUser_Search] ( @Business nVarChar(255) = NULL
, @Location nVarChar(255) = NULL
, @PageNumber Int = 1
, @RecordsPerPage Int = 10
, @TotalRecords Int OUTPUT)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL NVARCHAR(MAX)
, @CacheTable SYSNAME
, @TotalRows Int
, @Category VarChar(255)
, @BusinessCategory Int
, @TownCounty VarChar(50)
, @PcodeTownCounty VarChar(50)
INSERT Voucher_SearchHistory (Keyword, Location)
SELECT NULLIF(@Business,''), NULLIF(@Location,'')
采纳答案by code4life
回答by HatSoft
You are not passing TotalRecords Sql Parameter in Excecute
您没有在 Excecute 中传递 TotalRecords Sql 参数
var List = db.ExecuteStoreQuery<ENT_SearchBusinessResult>(
"exec usp_BusinessUser_Search",Business,
Location,PageNumber,RecordsPerPage,parm);
回答by david.s
The parameter name should not contain @:
参数名称不应包含@：
SqlParameter parm = new SqlParameter()
{
ParameterName = "TotalRecords",
Value = 0,
SqlDbType = SqlDbType.Int,
Direction = System.Data.ParameterDirection.Output
};
回答by Nicholas Petersen
You can now use the following extension method, ExecuteSqlCommandWithReturn, that takes care of all of the work for you!
您现在可以使用以下扩展方法，ExecuteSqlCommandWithReturn，它会为您处理所有工作！
https://gist.github.com/copernicus365/7037320
https://gist.github.com/copernicus365/7037320
string sql = "EXEC sp_CoolProc @SomeParam, @AnotherParam";
int returnValue;
int val = db.ExecuteSqlCommandWithReturn(sql, out returnValue, someParam, anotherParam);
The key to the solution was by Jieyang Hu here, which is this (though note that all of the following is fully handled by the aforementioned extension method): Just as you can do in a SQL prompt, you just set the result of the executed procedure to a SQL variable (in this case which is sent in as a parameter), like this:
解决方案的关键是在这里的胡杰阳，就是这样（尽管注意以下所有内容都完全由上述扩展方法处理）：就像您可以在 SQL 提示中执行的操作一样，您只需设置执行的结果过程到 SQL 变量（在这种情况下作为参数发送），如下所示：
EXEC @ReturnVal = sp_MyCoolProc;
This code adds the fragment @ReturnVal =after the first EXEC(followed by whitespace) it finds, and adds to the SqlParameters (or creates SqlParamters if there were none) a ReturnVal parameter, though the caller will never see these.
此代码在它找到@ReturnVal =的第一个EXEC（后跟空格）之后添加片段，并向SqlParameters（SqlParamter如果没有，则创建s）添加一个 ReturnVal 参数，尽管调用者永远不会看到这些。
回答by DaniDev
If you just added that parameter ('@TotalRecords) recently I am going to guess that you just need to update your function import definition in your edmx model.
如果您最近刚刚添加了该参数 ('@TotalRecords)，我猜您只需要更新 edmx 模型中的函数导入定义。
