使用实体框架的输出参数执行SQL存储过程 [英] Executing SQL Stored Procedure with Output Parameter from Entity Framework
本文介绍了使用实体框架的输出参数执行SQL存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
使用EF,我试图执行返回单个字符串值的存储过程,即SQL Agent作业的状态。
Using EF, I'm trying to execute a stored procedure that returns a single string value, i.e. the status of an SQL Agent Job.
存储过程是声明为
CREATE PROCEDURE [dbo].[up_GetJobStatus](@JobStatus NVARCHAR(30) OUTPUT)
AS
-- some code omitted for brevity
SELECT @JobStatus = (
SELECT
CASE job_state
WHEN 1 THEN 'Executing'
WHEN 2 THEN 'Waiting for thread'
WHEN 3 THEN 'Between retries'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Suspended'
WHEN 6 THEN '<unknown>'
WHEN 7 THEN 'Performing completion actions'
END
FROM @xp_results results
INNER JOIN msdb.dbo.sysjobs sj
ON results.job_id = sj.job_id
WHERE sj.job_id = @job_id)
RETURN
我已经验证存储过程是正确的,因为我可以在查询窗口中执行它,并返回rns
I have verified the stored procedure is working correct as I can execute it in query window and it returns
@JobStatus
------------
1|Idle
但是,当使用EF执行时,参数值为NULL
However when executing with EF, the param value is NULL
var param = new SqlParameter
{
ParameterName = "@JobStatus",
DbType = DbType.String,
Size = 30,
Direction = System.Data.ParameterDirection.Output
};
var result = this.etlContext.Database.SqlQuery<string>("EXEC dbo.up_GetJobStatus @JobStatus OUTPUT", param);
我还尝试了 ExecuteSqlCommand
方法但是这还没有起作用。
I've also tried the ExecuteSqlCommand
method but that didn't work either.
任何想法?
推荐答案
- 在数据库中创建存储过程
。
CREATE PROCEDURE [dbo].myStoredProcName
@inputParam1 varchar(150),
@inputParam2 varchar(150),
@myOutputParamBool bit output,
@myOutputParamString varchar(100) output,
@myOutputParamInt int output
AS
BEGIN
-- sql here
END
-
从数据库更新实体模型以包含存储过程
< a href =https://i.stack.imgur.com/61Bf9.png =nofollow noreferrer>如下所示
从C#代码调用存储过程
Call stored proc from C# code
System.Data.Entity.Core.Objects.ObjectParameter myOutputParamBool=new System.Data.Entity.Core.Objects.ObjectParameter("myOutputParamBool",typeof(bool));
System.Data.Entity.Core.Objects.ObjectParameter myOutputParamString=new System.Data.Entity.Core.Objects.ObjectParameter("myOutputParamString",typeof(string));
System.Data.Entity.Core.Objects.ObjectParameter myOutputParamInt=new System.Data.Entity.Core.Objects.ObjectParameter("myOutputParamInt",typeof(Int32));
using (var context = new SandCryptEntities())
{
context.myStoredProcName(inputParam1, inputParam2, myOutputParamBool, myOutputParamString, myOutputParamInt);
}
bool myBool = Convert.ToBoolean(myOutputParamBool.Value);
string myString = Convert.ToString(myOutputParamString.Value);
int myInt = Convert.ToInt32(myOutputParamInt.Value);
这篇关于使用实体框架的输出参数执行SQL存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文