Database.SqlQuery调用具有多个输出参数的存储过程 [英] Database.SqlQuery calling stored procedure that has multiple output parameters
问题描述
我有一个类似于以下的存储过程,该过程需要1个输入参数(Name)并返回2个输出参数(EmployeeId和Salary)。我们的存储过程会将Name插入Employee表中,并返回EmployeeId和Salary。
I have a stored procedure like below which takes 1 input parameter ( Name ) and returns 2 output parameters( EmployeeId and Salary). Our stored procedure will insert Name into Employee table and give us back EmployeeId and Salary.
CREATE PROCEDURE dbo.insertemployee
@iName varchar(500),
@OEmployeeId int OUTPUT,
@OSalary Money OUTPUT
我们正在使用EF Code First方法。我能够将记录插入到employee表中,但是找不到如何访问我的两个输出参数。我知道我需要像下面这样使用。谁能告诉我结果必须是什么。根据MSDN,它可以是一个以列名作为属性的类。但是我的情况是我们不返回表的列,而是使用两个输出参数,并且我需要知道如何访问这两个输出参数@OEmployeeId和@OSalary。
We are using EF Code First approach. I am able to insert records into employee table and cannot find how I can access my two output parameters. I know that I need to use like below. Can anybody tell me what must be Result. According to MSDN, it can be a class that has column names as properties. But my case is that we are not returning columns of my table but we are using two output parameters and I need know how to access those two output parameters @OEmployeeId and @OSalary.
context.Database.SqlQuery<Result>(" exec dbo.insertemployee....);
public class Result
{
// what properties I must give here
}
推荐答案
您尝试使用的方法仅适用于查询结果,它无法自动将输出参数的值放入新对象中。
The method you are trying to use only works for results of a query. It can't put the values of the output parameters into a new object for you automatically.
必须在运行存储过程后显式创建参数并读取它们的值。
You have to create the parameters explicitly and read their values after the stored procedure has been run.
因此,如果您有这样的存储过程:
So if you have a stored procedure like this:
CREATE PROCEDURE dbo.insertemployee
(
@iName varchar(500),
@OEmployeeId int OUTPUT,
@OSalary Money OUTPUT
)
AS
BEGIN
SELECT @OEmployeeId = 1337;
SELECT @OSalary = 1000;
END
...您可以执行它并获取像这样的参数结果:
... you can execute it and get the results of the parameters like this:
using (var ctx = new Context())
{
var nameParam = new SqlParameter("iName", "TestName");
var employeeIdParam = new SqlParameter("OEmployeeId", SqlDbType.Int)
{
Direction = System.Data.ParameterDirection.Output
};
var salaryParam = new SqlParameter("OSalary", SqlDbType.Money)
{
Direction = System.Data.ParameterDirection.Output
};
ctx.Database.ExecuteSqlCommand(
"insertemployee @iName, @OEmployeeId out, @OSalary out",
nameParam, employeeIdParam, salaryParam);
var employeeId = (int)employeeIdParam.Value;
var salary = (decimal)salaryParam.Value;
}
这篇关于Database.SqlQuery调用具有多个输出参数的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!