使用来自实体框架的输出参数执行

使用来自实体框架的输出参数执行

本文介绍了使用来自实体框架的输出参数执行 SQL 存储过程的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

使用 EF,我试图执行一个返回单个字符串值的存储过程,即 SQL 代理作业的状态.

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

我已验证存储过程工作正常,因为我可以在查询窗口中执行它并返回

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.

有什么想法吗?

推荐答案

  1. 在数据库中创建存储过程

  1. Create stored procedure in database

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

  • 从数据库更新实体模型以包含存储过程如图所示

    1. 从 C# 代码调用存储过程

    1. Call the stored procedure from C# code

    // Type is System.Data.Entity.Core.Objects.ObjectParameter
    ObjectParameter myOutputParamBool = new ObjectParameter("myOutputParamBool", typeof(bool));
    ObjectParameter myOutputParamString = new ObjectParameter("myOutputParamString", typeof(string));
    ObjectParameter myOutputParamInt = new 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 存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

    08-30 03:28