使用带有输出参数的实体框架调用Oracle存储过程?
问题描述:
我有一个简单的Oracle存储过程获取传入三个参数,并且有一个输出参数:使用带有输出参数的实体框架调用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
后无一例外:
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操作工作,任何人都可以请协助,并指示我哪里出错了?
答
在这种情况下,你不应该叫:
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;
}
为了证明我的理论,我转载你所遇到的无效行为,然后做出一个改变。它挂了一下(可能让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查询使用延迟执行。
我有类似的要求,你能解决它吗? –
我有同样的问题,请让我知道,如果你找到了解决方案。 – pforsthoff