使用带有输出参数的实体框架调用Oracle存储过程

本文关键字：框架 实体 调用 Oracle 存储过程 参数 输出 | 更新日期: 2023-09-27 18:03:02

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

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;

我试图使用实体框架6.1来返回OutAssetRegistered值，然而，在调用SqlQuery后，我得到一个null，没有例外:

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;
}

我一直在努力让这个工作无济于事，已经检查了不同的博客，所有其他的crud操作工作，有人可以帮助和指导我在哪里我错了?

使用带有输出参数的实体框架调用Oracle存储过程

在这种情况下，您不应该调用:

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是不必要的。否则，您的代码看起来应该可以工作。以下是您的原始代码的完整部分，其中包含了我提到的更改:

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;
}

为了证明我的理论，我复制了你正在经历的null行为，然后做了一个改变。它挂了一段时间(可能是为了让EF启动)，但之后每次都执行得很快。在每种情况下，我都在out参数中找到一个等待的值。

如果有人遇到了麻烦，这里有一个手写的变体，可以为您处理脚本细节:

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())，从技术上讲，您可以使用原始解决方案。查询的返回值将始终为空，但out参数至少会被填充。这是因为EF查询使用延迟执行。

如果您需要传入和/或输出日期:

        public DateTime GetDate(string dateIn)
        {
            Regex regex = new Regex(@"('d{4}-'d{2}-'d{2}T'd{2}:'d{2}:'d{2}Z)");
            if (!regex.IsMatch(dateIn))
            {
                throw new ArgumentException($"dateIn must be given in the YYYY-MM-DD'"T'"hh24:mi:ss'"Z'" format. " +
                    $"Value given: {dateIn}");
            }
            OracleParameter dateInParam = new OracleParameter("DATE_IN_", dateIn);
            createdParam.OracleDbType = OracleDbType.TimeStampTZ;
            OracleParameter dateOutParam= new OracleParameter("DATE_OUT_", OracleDbType.Date, System.Data.ParameterDirection.Output);
            var sql = "BEGIN " +
                $"Get_Date(:DATE_IN_, TO_TIMESTAMP_TZ('{dateIn}','YYYY-MM-DD'"T'"hh24:mi:ss'"Z'"'), :DATE_OUT_ ); " +
                "END;";
#pragma warning disable EF1000 // Possible SQL injection vulnerability.
            context.Database.ExecuteSqlCommand(sql, dateInParam, dateOutParam);
#pragma warning restore EF1000 // Possible SQL injection vulnerability.
            return (DateTime)(OracleDate)dateOutParam.Value;
        }
相关文章：