使用带有输出参数的Entity Framework调用Oracle存储过程?

问题描述

我有一个简单的Oracle存储过程，该存储过程获取传入的三个参数，并具有一个输出参数:

I have a simple Oracle stored procedure that gets three parameters passed in, and has one output parameter:

CREATE OR REPLACE PROCEDURE RA.RA_REGISTERASSET
(
    INPROJECTNAME IN VARCHAR2
    ,INCOUNTRYCODE IN VARCHAR2
    ,INLOCATION IN VARCHAR2
    ,OUTASSETREGISTERED OUT VARCHAR2
)
AS
BEGIN
  SELECT
      INPROJECTNAME || ', ' || INLOCATION || ', ' || INCOUNTRYCODE
  INTO
      OUTASSETREGISTERED
  FROM
      DUAL;
END RA_REGISTERASSET;

我试图使用Entity Framework 6.1来获取 OutAssetRegistered 值，但是，在无例外地调用 SqlQuery 后，我得到了一个空值:

I am trying to use Entity Framework 6.1 to get back the OutAssetRegistered value, however, I get a null after calling SqlQuery with no exception:

public class CmdRegisterAssetDto
{
        public string inProjectName { get; set; }
        public string inCountryCode { get; set; }
        public string inLocation { get; set; }
        public string OutAssetRegistered { get; set; }
}

//------------------------------------------------------------

//------------------------------------------------------------

string projectName = "EXCO";
string location = "ANYWHERE";
string countryCode = "XX";

using (var ctx = new RAContext())
{
    var projectNameParam = new OracleParameter("inProjectName", OracleDbType.Varchar2, projectName, ParameterDirection.Input);
    var countryCodeParam = new OracleParameter("inCountryCode", OracleDbType.Varchar2, countryCode, ParameterDirection.Input);
    var locationParam = new OracleParameter("inLocation", OracleDbType.Varchar2, location, ParameterDirection.Input);
    var assetRegisteredParam = new OracleParameter("OutAssetRegistered", OracleDbType.Varchar2, ParameterDirection.Output);

    var sql = "BEGIN RA.RA_RegisterAsset(:inProjectName, :inCountryCode, :inLocation, :OutAssetRegistered); END;";
    var query = ctx.Database.SqlQuery<CmdRegisterAssetDto>(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam
    );

    assetRegistered = (string)assetRegisteredParam.Value;
}

我一直在努力使这项工作无济于事，已经检查了不同的博客，所有其他杂项操作都起作用，有人可以协助并指示我哪里出了问题吗?

I have been battling to get this to work to no avail, have checked different blogs, all the other crud operations work, can anyone please assist and direct me where I am going wrong?

推荐答案

在这种情况下，您不应拨打电话:

In this case, you shouldn't be calling:

var query = ctx.Database.SqlQuery<CmdRegisterAssetDto>(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam);

但是要打电话给

var result = ctx.Database.ExecuteSqlCommand(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam);

请注意，唯一有效的区别是将 SqlQuery< CmdRegisterAssetDto> 替换为 ExecuteSqlCommand .这也意味着DTO是不必要的.否则，您的代码看起来应该可以工作.这是您完整的原始代码，其中包括我提到的更改:

Notice that the only effective difference is that SqlQuery<CmdRegisterAssetDto> was replaced with ExecuteSqlCommand. This also means that the DTO is unnecessary. Otherwise, your code looks like it should work. Here's your original code in its entirety with the changes I mentioned:

string projectName = "EXCO";
string location = "ANYWHERE";
string countryCode = "XX";

using (var ctx = new RAContext())
{
    var projectNameParam = new OracleParameter("inProjectName", OracleDbType.Varchar2, projectName, ParameterDirection.Input);
    var countryCodeParam = new OracleParameter("inCountryCode", OracleDbType.Varchar2, countryCode, ParameterDirection.Input);
    var locationParam = new OracleParameter("inLocation", OracleDbType.Varchar2, location, ParameterDirection.Input);
    var assetRegisteredParam = new OracleParameter("OutAssetRegistered", OracleDbType.Varchar2, ParameterDirection.Output);

    var sql = "BEGIN RA.RA_RegisterAsset(:inProjectName, :inCountryCode, :inLocation, :OutAssetRegistered); END;";
    var result = ctx.Database.ExecuteSqlCommand(sql, projectNameParam, countryCodeParam, locationParam, assetRegisteredParam);

    assetRegistered = (string)assetRegisteredParam.Value;
}

为证明我的理论，我复制了您遇到的无效行为，然后进行了更改.它挂了一下(可能让EF开始运转)，但是此后每次都快速执行.在每种情况下，我都在out参数中找到一个值.

To prove my theory, I reproduced the null behavior that you're experiencing and then made that one change. It hung for a bit (probably to let EF kick into gear), but then executed quickly every time thereafter. In each case, I found a value waiting in the out parameter.

如果外面有人遇到麻烦，则可以通过长期修改来为您处理脚本详细信息:

If anyone out there is running into trouble, there's a longhand variation that takes care of the scripting details for you:

string projectName = "EXCO";
string location = "ANYWHERE";
string countryCode = "XX";

using (var ctx = new RAContext())
using (var cmd = ctx.Database.Connection.CreateCommand())
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "RA.RA_REGISTERASSET";

    var projectNameParam = new OracleParameter("inProjectName", OracleDbType.Varchar2, projectName, ParameterDirection.Input);
    var countryCodeParam = new OracleParameter("inCountryCode", OracleDbType.Varchar2, countryCode, ParameterDirection.Input);
    var locationParam = new OracleParameter("inLocation", OracleDbType.Varchar2, location, ParameterDirection.Input);
    var assetRegisteredParam = new OracleParameter("OutAssetRegistered", OracleDbType.Varchar2, ParameterDirection.Output);
    cmd.Parameters.AddRange(new[] { projectNameParam, countryCodeParam, locationParam, assetRegisteredParam });

    cmd.Connection.Open();
    var result = cmd.ExecuteNonQuery();
    cmd.Connection.Close();

    assetRegistered = (string)assetRegisteredParam.Value;
}

事后考虑，如果您在之后立即调用查询(即 query.FirstOrDefault())，从技术上讲，您可以采用原始解决方案.查询的返回值将始终为null，但是您的out参数至少将被填充.这是因为EF查询使用延迟执行.

As an afterthought, you technically could go with your original solution if you invoked the query immediately after (i.e. query.FirstOrDefault()). The return value of the query would always be null, but your out parameter would at least get populated. This is because EF queries use deferred execution.

这篇关于使用带有输出参数的Entity Framework调用Oracle存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！