Executing Oracle Stored Procedure in EntityFramework

Question

In short: I'm trying to run the Oracle stored procedure in the Entity Framework (I know this sounds weird, but in the general case, the application uses the Entity Framework, but this particular command cannot be processed by EF due to restrictions on changing key values).

The procedure has some parameters (only IN) and updates the values ​​in the table. I tested it by doing:

execute PROCEDURE_NAME('parameter1', parameter2 etc.);

It works great.

My options are defined as follows:

OracleParameter param1 = new OracleParameter("PARAM1", OracleDbType.Varchar2, changed.PARAM1, ParameterDirection.Input);

object[] parameters = new object[] { 
                param1,...};

My request:

string query = "execute PROCEDURE_NAME(:PARAM1,...);";

I am trying to execute it from C # code. Namely, by running:

_context.Database.ExecuteSqlCommand(query, parameters);

ORA-00900: : SQL. , SQL, (, CREATE PROCEDURE). , , SQL * Plus. PL/SQL , .

, , .

, , , Entity Framework. ? , , - ?

Answer 1

:

OracleConnection connection = (Oracle.DataAccess.Client.OracleConnection)_context.Database.Connection;
connection.Open();
OracleCommand cmd = _context.Database.Connection.CreateCommand() as OracleCommand;
cmd.CommandText = "STORED_PROCEDURE_NAME";
cmd.CommandType = CommandType.StoredProcedure;

#region Parameters

//original and changed are just some POCOs
OracleParameter oNameOfParameter = new OracleParameter("oNameOfParameter", OracleDbType.Decimal, original.NameOfParameter, ParameterDirection.Input);
OracleParameter oNameOfParameter2 = new OracleParameter("oNameOfParameter2", OracleDbType.Varchar2, original.NameOfParameter2, ParameterDirection.Input);

OracleParameter NameOfParameter3 = new OracleParameter("nameOfParameter3", OracleDbType.Varchar2, changed.NameOfParameter3, ParameterDirection.Input);
OracleParameter NameOfParameter4 = new OracleParameter("nameOfParameter4", OracleDbType.Decimal, changed.NameOfParameter4, ParameterDirection.Input);

cmd.Parameters.Add(nameOfParameter3);
cmd.Parameters.Add(nameOfParameter4);
cmd.Parameters.Add(oNameOfParameter);
cmd.Parameters.Add(oNameOfParameter2);

#endregion Parameters

var i = cmd.ExecuteNonQuery();
connection.Close();

, , .

Answer 2

:

string query = "begin PROCEDURE_NAME(:PARAM1,...); end; ";

Answer 3

EntityERP context = new EntityERP (); // is a context map from entity

context.Database.ExecuteSqlCommand("BEGIN  STORED_PROCEDURE_NAME; END; "); 

This is the solution.