Geek Answers Handbook

Calling an Oracle stored procedure using Entity Framework with an output parameter?

I have a simple Oracle stored procedure that includes three parameters 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;

I am trying to use Entity Framework 6.1 to return an OutAssetRegistered value, however I get null after calling SqlQuery without 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 fought to make this work to no avail, checked different blogs, all other work with crud operations, can anyone help and direct me to where I am wrong?

+5
source share
1 answer

In this case you should not call:

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

Instead, it calls:

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

Note that the only effective difference is that SqlQuery<CmdRegisterAssetDto> been replaced with ExecuteSqlCommand . It also means that DTO is not required. Otherwise, your code looks as if it should work. Here is your source code fully 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; }

To prove my theory, I reproduced the null behavior you experience and then made this one change. It hung a bit (it is possible that EF came into play), but then it was executed quickly every time after that. In each case, I found a value waiting in the out parameter.

If anyone has problems, there is a long way variation that takes care of the details of scripting 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; }

As an afterthought, you can technically go with your original solution if you immediately call a query (i.e. query.FirstOrDefault() ). The return value of the request will always be zero, but your out parameter will at least be populated. This is because EF requests use deferred execution.

0
source

More articles:


All Articles